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
– arkascha, Commented Aug 23, 2015 at 12:59
Another meaning for my question - can the QueryException be a correct work of binding? — MapUser
– MapUser, Commented Aug 23, 2015 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
– Craig Ringer, Commented Aug 23, 2015 at 13:30
If you want to pass arrays as query parameters, you might have a look at Pomm stackoverflow.com/questions/31643297/… — greg
– greg, Commented Aug 23, 2015 at 15:58

Community · Accepted Answer · 2023-11-17 19:42:58Z

0

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.

edited Nov 17, 2023 at 19:42

CommunityBot

11 silver badge

answered Aug 23, 2015 at 13:21

NoDataFound

12k39 silver badges59 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related