SQL-injection with binding of an array to =ANY() condition

Question

It assumed a more complex query with multiple bindings so please don't guide me to use the things like implode(',',$ids), (?,?,?) or PDO possibilities for this example.

The question is to clarify a possibility of the SQL-injection of this specific method.

There is parameter 1,2,3 in the url http://localhost/executeSql/1,2,3. The parameter is passed by binding into = ANY operator as the string representation of the array '{1,2,3}' of PostgreSQL 9.3.

The php-code on Laravel 5.1:

public function executeSql($ids)
{
    $ids='{'.$ids.'}';
    $condition = 'WHERE id = ANY(:ids)';
    $sql="SELECT id FROM (VALUES (1),(2),(3)) AS t(id) $condition";
    DB::select($sql,[':ids'=>$ids]);
}

The result is the query: SELECT id FROM (VALUES (1),(2),(3)) AS t(id) WHERE id = ANY('{1,2,3}')

That's works well untill the parameter contains integers only. If the parameter is 1,2,3+ the QueryException occurs:

Invalid text representation: 7 ERROR: invalid input syntax for integer: "3+"

Can it be considered a proper protection to avoid SQL-injection?

I would consider this a very _creative_ piece of code... But it appears save, since it binds and thus properly escapes the content of `$ids`. Why do you think otherwise? — arkascha, Aug 23 '15 at 12:59
Another meaning for my question - can the QueryException be a correct work of binding? — MapUser, Aug 23 '15 at 13:21
What you really want is your database driver to support binding PHP arrays as PostgreSQL arrays, so you bind as a single parameter. It looks like PHP/PDO doesn't support that. — Craig Ringer, Aug 23 '15 at 13:30
If you want to pass arrays as query parameters, you might have a look at Pomm http://stackoverflow.com/questions/31643297/pg-query-result-contains-strings-instead-of-integer-numeric/31740990#31740990 — greg, Aug 23 '15 at 15:58

score 0 · Accepted Answer · answered Aug 23 '15 at 13:21

As far as I understand from the documentation here and here , ANY convert the string you pass into an array and then use the operator (=) to compare each value in the array for one that would match.

In this case, I think pgsql do a little more: it has seen the lvalue (id) is of type integer, so it expect an array of integers. Since 3+ is not an integer, you have this one.

You should probably inspect the content of ids array (using filter_var and like) to ensure you have only integer values.

Since you definitively want the query to run with unintended result, this fails as a proper SQL injection because ANY checks its input and the query fails before running.

If however pgsql comes with a facility to build an array of integer from range, like {1:999999999999}, then you probably have a problem because the query will match a lot whole more rows.

SQL-injection with binding of an array to =ANY() condition

1 Answers1