...

View Full Version : bind variable for sql injection protection



htcilt
01-20-2010, 04:30 PM
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. The slight complication is I'm using Oracle databases so mysql functions like mysql_real_escape_string wont work.
I can however use Oracle bind variables. (more info: http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/ullman_bindings.html)

and 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?

angst
01-20-2010, 04:34 PM
you might have better luck here: http://codingforums.com/forumdisplay.php?f=38

MattF
01-20-2010, 06:04 PM
If you have a look on the PHP site, I seem to recall someone posted a short function for binding array entries to the respective placeholder. Not sure if it was on the pg_query_params page. Might not be what you need, but worth a look just incase.

Dormilich
01-20-2010, 08:07 PM
PDO (http://php.net/pdo) provides Prepared Statements (thatís where you bind parameters) for a couple of databases, including Oracle.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum