CODEWITHSUNDEEP
phpmysqlpdosql-injection
Ben
Ben

Reputation: 4867

Sanitise query in SQL and PHP using in_array()

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?

Upvotes: 3

Views: 649

Answers (1)

Andreas Linden
Andreas Linden

Reputation: 12721

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

Upvotes: 2

Related Questions