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?
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.