Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Bind variable on multiple search keywords

732941Jan 20 2010 — edited Jan 30 2010
Hi all,
I'm new to PHP so apologies if my coding is a bit long-winded.

I have a search form that queries the titles of books. The user can enter any number of keywords and the sql is built up dynamically for each keyword.

I'm trying to secure the variables so that its better protected from sql injections. here's my PHP:
$the_keyword = $_POST['livesearch']; // LIVESEARCH IS THE NAME OF THE FORM
$allterms = explode(" ", $the_keyword); // SEPARATE EACH KEYWORD INTO ARRAY
$x = 1; // COUNTER FOR DYNAMIC NUMBER OF ORACLE CONTAINS
$counter = count($allterms); // COUNTER DYNAMIC ORACLE SCORE
reset($allterms);

$sql = "";
$sql .= "SELECT ";

// DYNAMIC ADDITION OF SCORE TO START OF SQL
for ($i = 1; $i <= $counter; ++$i) {
    $sql .= "score(".$i."), ";
}

$sql .= "  FROM MYTABLE WHERE ";

// CYCLE THROUGH EACH KEYWORD FOR MULTIPLE KEYWORD SEARCH.
while (list($key, $val) = each($allterms)) {
    $sql .= "CONTAINS(TITLE, '%'||nvl(lower('$val'),'ZZZZZZZZZZZ')||'%', $x) > 0 AND ";
	$x++;
	}
}
$sql=substr($sql,0,(strLen($sql)-4)); // REMOVE TRAILING AND
$sql .= "ORDER BY ";

// ORDER BY SCORE THEN TITLE
for ($i = 1; $i <= $counter; ++$i) {
    $sql .= "score(".$i.") DESC, ";
}

$sql .= "CL_TITLE ASC";

echo $sql;

$query = $sql;
$result = oci_parse($conn, $query);
If the search keyword was a single variable (not an array) I could have:
SELECT score(1), TITLE FROM MYTABLE WHERE (CONTAINS(TITLE, '%'||nvl(lower(:keyword),'ZZZZZZZZZZZ')||'%', 1) > 0 ORDER BY score(1) DESC, score(2) DESC, CL_TITLE ASC;
then bind keyword
oci_bind_by_name($result, ":keyword", $the_keyword);
But as I'm dealing with an array, I cant use a single bind variable.
I'm guessing I also need to dynamically add each bind variable too so I end up with an oci_bind_by_name for each item in the array.

Could anyone help with how to go about this?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 27 2010
Added on Jan 20 2010
10 comments
4,127 views