Safely using prepared statements to query database

Question

I'm trying to write a function that is versatile in the queries it is allowed to make, but also safe from injection. The code below throws an error as is, but if I run it with 'name' instead of ':field' it works fine.

$field = "name";
$value = "joe";

function selectquery($field, $value)
  {
  global $dbcon;

  $select = $dbcon->prepare('SELECT * FROM tester1 WHERE :field = :value');
  if($select->execute(array(':field' => $field, ':value' => $value)));
    {
    $row = $select->fetch();
    for ($i=0; $i<3; $i++)
      {
      echo $row[$i]."\n";
      }
    }  
  }

How would I allow the table/fields/values to be changed, without allowing injection attacks? mysql_real_escape_string() seems kind of like a step backwards. Any ideas?

Answer 1

Database identifiers (column names, table names and database names) can not and should not be escaped, therefore you can't use them in SQL prepared queries.

Sometimes you might need to backtick those identifiers though (use ` for MySQL and " for SQLite).

Answer 2

Binding a variable binds it as data, specifically to prevent it from changing the syntax of the query. Furthermore, having a fixed syntax allows engines to analyze prepared queries once and then run them quickly for each set of values. I would encourage you not to build a hand-holding layer on top of SQL, but if you must, consider a preg_replace('/\W/', '', $field).

Answer 3

PHP Data Objects unfortunately doesn't expose a method to quote a field identifier.

As an alternative, PEAR::MDB2 (spiritual predecessor to PHP Data Objects) has a ->quoteIdentifier() option which allows you to achieve what you want in a safe manner.

function selectquery($field, $value)
  {
  global $dbcon;

  $select = $dbcon->prepare('SELECT * FROM tester1 WHERE ' . $dbcon->quoteIdentifier($field) . ' = :value');
  if($select->execute(array('field' => $field, 'value' => $value)));
    {
    $row = $select->fetchRow();
    for ($i=0; $i<3; $i++)
      {
      echo $row[$i]."\n";
      }
    }  
  }

I understand that this solution is less than optimal (changing abstraction layer in the middle of developing a project is cumbersome) but unfortunately, PDO provides no safe way of doing what you want to do.

Answer 4

I may be mistaken, but I don't believe you can supply fields as parameters in PDO.

Why not just specify it as argument to the function? Unlike the data being supplied by the user, the fields are finite, well defined and don't change often. As in

selectquery('name',$value);

and have your query be

$field = "name";
$value = "joe";

function selectquery($field, $value)
  {
  global $dbcon;

  $select=$dbcon->prepare("SELECT * FROM tester1 WHERE $field = :value");
  if($select->execute(array(':value' => $value)));
 //etcetera  

Since you're supplying the field name for the function call yourself, this is safe unless you're worried you are going to attack yourself with SQL injection.

If for some odd reason the name of the field is coming from user input, you could make an array of allowed fields. That way, you're safe from injection because the values can only come from your array. I don't know why the field name would be coming from user input and thus be untrusted, unless perhaps you're making an API? Other wise there's probably a better way to achieve the goal.

Anyhow, this would be a potential solution, to use a whitelist for table names:

$field = "name";
$value = "joe";

$allowed_fields=array('name','other_name','sandwich');

function selectquery($field_name, $value)
  {
  global $dbcon,$allowed_fields;

  if(!in_array($field_name,$allowed_fields)){ return false; }
  else{ $field=$field_name; }

  $select=$dbcon->prepare("SELECT * FROM tester1 WHERE $field = :value");
  if($select->execute(array(':value' => $value)));
  //etcetera

Answer 5

Seconding Andrew Moore's response: the only way to go is identifier quoting, and PDO doesn't provide the necessary method. Rather than use MDB2, you might just want to borrow its implementation of identifier quoting. The function is simple enough that you should be able to write your own and vet it for bugs fairly easily.

1. Split the input string on . into a list of parts (there might be only one)

2. For each part:

   1. Replace all ` with ``.
   2. Add a ` to the beginning and to the end unless the part is empty.*

3. Join the parts with ..

As an example, quote_identifier("one two.three") should be `one two`.`three` -- pretty straightforward.

For extra safety you could also verify that the string doesn't contain any characters that are illegal even in quoted identifiers (particularly nulls, see the MySQL docs) but in truth MySQL should catch those. MDB2 doesn't bother.

*: This check is necessary because .columnname is legal, and should quote to .`columnname` and not ``.`columnname`.

Answer 6

Use MDB2 autoExecute
http://pear.php.net/manual/en/package.database.mdb2.intro-auto.php

<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';

$fields_values = array(
    'id'      => 1,
    'name'    => 'Fabien',
    'country' => 'France'
);
$types = array('integer', 'text', 'text');

$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, $fields_values,
                        MDB2_AUTOQUERY_INSERT, null, $types);

if (PEAR::isError($affectedRows)) {
    die($affectedRows->getMessage());
}
?>