On Tue, Mar 8, 2016 at 10:56 AM, dealtek@xxxxxxxxx <dealtek@xxxxxxxxx>
wrote:
> $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]
Cheers!
David
[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/
