Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Sep 2007
    Posts
    238
    Thanks
    9
    Thanked 0 Times in 0 Posts

    bind variable for sql injection protection

    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..._bindings.html)

    and here's my PHP

    PHP Code:
    $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:

    PHP Code:
    SELECT score(1), TITLE FROM MYTABLE WHERE (CONTAINS(TITLE'%'||nvl(lower(:keyword),'ZZZZZZZZZZZ')||'%'1) > 0 ORDER BY score(1DESCscore(2DESCCL_TITLE ASC
    then bind keyword

    PHP Code:
    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?

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    you might have better luck here: http://codingforums.com/other-databases/

  • #3
    Senior Coder
    Join Date
    Jul 2009
    Location
    South Yorkshire, England
    Posts
    2,318
    Thanks
    6
    Thanked 304 Times in 303 Posts
    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.

  • #4
    Senior Coder Dormilich's Avatar
    Join Date
    Jan 2010
    Location
    Behind the Wall
    Posts
    3,409
    Thanks
    13
    Thanked 357 Times in 353 Posts
    PDO provides Prepared Statements (that’s where you bind parameters) for a couple of databases, including Oracle.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •