mysql_paramquery()

A.E.Veltstra
2008-05-12

PHP 4 and 5 have a built-in function to execute SQL statements on MySQL databases, called 'mysql_query()'.

This function comes in quite handy, but it allows for SQL injection where the parameters are filled with values. In the description of the function this behaviour is acknowledged, and a best-practice is formulated whereby developers should use sprintf() with the auxilary function 'mysql_real_escape_string()'. I found this method quite verbose. Based on the comments I read on the php.net site, and looking at the way various content management systems implemented it, I came up with the following method to allow unlimited numbers of parameters in various places in the SQL statement.

I tested this in a recent project where it proved to work way faster than string replacement methods... but I obviously haven't tested it in every possible circumstance and it's likely I forgot a case or two. Any comments to improve this little piece of code will be appreciated.

Use at your own peril.

Code sample

function mysql_paramquery($sql,$sqlparams=array(),$dblink){
  //A.E.Veltstra 2008
  //http://www.omegajunior.net/code/mysql_paramquery.html
  //
  //Function parameters:
  //
  //$sql (string): sql statement using ? for parameter placeholders.
  //For instance: $sql="select email from users where name='?' and userid=?";
  //We expect the developer to add quotes around the string parameters 
  //inside the sql statement as needed.
  
  //$sqlparams (array) holding the values to be used instead of the placeholders.
  //The values must be provided in the same order as the placeholders, seen from the 
  //left to the right.
  //For instance: $sqlparams=array($strUsername,$lngUserID);
  
  //$dblink (resource): Link resource to MySQL database created with 'mysql_connect()'.

  //if there are any parameters, replace them with values. Otherwise,skip.
  if(is_array($sqlparams) && count($sqlparams)>0 && strpos($sql,'?')>0){

      //We're only looking to replace occurrences of ? with parameters. 
      //So we split our sql into parts exactly where the parameters need to be put.
      $arrSQL=explode('?',$sql);
      //count the number of parts for the upcoming loops
      $c=count($arrSQL);
      //count the number of parameters for the upcoming loops
      $p=count($sqlparams);
      //set up the receiver
      $arrRes=array();
      
      //push array parts into the receiver until either source array runs out
      for($i=0;$i<$c && $i<$p; $i++){
         $arrRes[]=array_shift($arrSQL);
         $arrRes[]=mysql_real_escape_string(array_shift($sqlparams));
      }
      
      //If all is well, there should be no more parts in the params array... 
      //but if there are, we don't care. The developer should care instead.
      //However, there may be more parts in the sql array. 
      //We need to push those into the receiver as well.
      //Since we already counted the number of parts, we can simply use a counting loop.
      
      while($i<$c){
          $arrRes[]=array_shift($arrSQL);
          $i++;
      }
      
      //Then we implode the receiver into a string
      $sql=implode('',$arrRes);
  }
  //execute the sql string
  return mysql_query($sql,$dblink);
}

Afterthoughts

This method allows for placeholders in various places of the sql statement... the WHERE clause is an obvious place, but it will also allow for placeholders in the SELECT clause, the FROM clause, and the ORDER-BY clause... and anywhere else. The parameter values array will take comma separated strings, as well. For instance:

$sql = "select ? from ? where ? = ? order by ?";
$sqlparams = array('UserID, Name','tblUsers','enabled',1,'name ASC');

Copyright policy

You are free to copy and adapt this function to suit your own needs.

Did I mention this? Use at your own peril.