Sanitise query in SQL and PHP using in_array()

Question

Using MySQL and PHP, a typical (PDO) query looks like this:

// prepare the query
$q = $DB->prepare("SELECT * FROM table_name WHERE property = :value");
// run the query
$q->execute(array(':value'=>$value));

This is safe from SQL injection, as the property value is treated separately to the query.

However if I want to use the same code to write a query that might retrieve a different field or different grouping, you cannot use the prepare/execute method alone, as you cannot use PDO parameters for fields (see here).

Can you simply use in_array() to check a field name, like this:

// return false if the field is not recognised
if(! in_array($field_name, array('field1','field2','field3')) return false
// run the query
$q = $DB->query("SELECT * FROM table_name ORDER BY " . $field_name);

Is there a safer / quicker way?

its safe enough. There are better solution, i.e. when you have entity/model with defined fields, but in_array its ok ;) — Daimos
– Daimos, Commented Oct 21, 2015 at 11:24

Andreas Linden · Accepted Answer · 2015-10-21 11:32:56Z

2

Already seems quite fast and secure. Maybe add backticks around the field name in the query.

To speed it up slightly you can use an assoc array and just check if the index exists instead of searching the contents of an array.

$fields = array('field1' => null, 'field2' => null, 'field3' => null);
if (!array_key_exists($field_name, $fields)) return false;

furthermore isset is faster than array_key_exists

if (!isset($fields[$field_name])) return false;

function benchmarks

answered Oct 21, 2015 at 11:32

Andreas Linden

12.8k7 gold badges54 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sanitise query in SQL and PHP using in_array()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related