Web lists-archives.com

Re: [PHP] Array to string conversion error help needed

On Tue, Mar 8, 2016 at 10:56 AM, dealtek@xxxxxxxxx <dealtek@xxxxxxxxx>

> $sth = $pdo->prepare("SELECT * FROM table1 where id IN (1,2,3)"

Nice, safe, simple string query.

but with an array like...
> $mylist = array(1,2,3);
> $sth = $pdo->prepare("SELECT * FROM table1 where id IN ($mylist)"
> I get error : Array to string conversion...

The error is exactly what it says: you're telling PHP to convert an array
to a string by embedding it inside a string which it won't do
automatically. For this you can use implode [1] to turn the array into the
string "1,2,3".

$sth = $pdo->prepare("SELECT * FROM table1 where id IN (" . implode(",",
$mylist) . ")";

That will solve the problem but may create other problems.

1. What if instead of numbers you used string keys?

$ids = array('foo', 'bar');
$sth = ...

> SELECT * FROM table1 where id IN (foo, bar)

This query will fail because the strings aren't quoted.

2. SQL injection

$ids = array("); DROP TABLE users;");

> SELECT * FROM table1 where id IN (); DROP TABLE users;

No bueno!

If you're working on a project that will accept input from unknown users
(a.k.a. "the public"), 2 becomes a concern. For 1 you can start by quoting
the strings yourself in a loop for a few queries, but as the project
becomes more complicated you may find yourself wanting a DB library such as
PDO [2] or a full-blown ORM [3] such as Doctrine. [4]


[1] http://php.net/manual/en/function.implode.php
[2] http://php.net/manual/en/book.pdo.php
[3] https://en.wikipedia.org/wiki/Object-relational_mapping
[4] http://www.doctrine-project.org/