PDO issues with binding IN operator

Question

Note: I see this question as a probable serious issue in PDO Drivers. I can pretty much understand the difference between an array and string. So, please consider testing this on your sandbox before Deleting or making duplicate.

$pdo = db()->getInstance();

$sql = "SELECT * FROM clients WHERE client_id IN :clients";
$params = [ ":clients" =>  "(223,224,225)" ];    

$stmt = $pdo->prepare($sql);
try{
    $stmt->execute($params);
} catch( \Exception $e){
    die("Query Execution Failed" . $e->getMessage());
}

Query Execution FailedSQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''(223,224,225)'' at line 1

The issue here is instead of translating to:

SELECT * FROM clients WHERE client_id IN (223,224,225)

it is translating to:

 SELECT * FROM clients WHERE client_id IN '(223,224,225)'

There are no arrays here. I am just providing a parameter clients to replace :clients. Why does it add the quotes around?

http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — Chetan Ameta, Feb 11 '16 at 06:36
just create a dynamic placeholder which corresponds to the number of inputs, them load em all up in the `->execute()`, just check the link above given in the comments section — Kevin, Feb 11 '16 at 06:37
. Either check above link or do like :- `$params = [ ":clients" => "("."223".","."224".","."225".")" ];` — Alive to die - Anant, Feb 11 '16 at 06:39
@ChetanAmeta. I know that. But, I am not passing array. I am passing string that needs to be replaced. — tika, Feb 11 '16 at 06:40
It is **your issue**. Explode your string and work with array of values. — u_mulder, Feb 11 '16 at 06:43
@u_mulder, I already have exploded it before passing, sweetheart. — tika, Feb 11 '16 at 06:44
@u_mulder The output of explode is string. I already have a ready string **with quotes**. — tika, Feb 11 '16 at 06:47
And you should explode this string to get array of values - `[223, 224,225]`. And then check a linked answer. That's all. — u_mulder, Feb 11 '16 at 06:48
@u_mulder, If you think that works, why don't you post an answer that works? — tika, Feb 11 '16 at 06:50
The answer linked is precisely the solution you need. Why don't you give it a try instead of hanging on to a self-entitled attitude and insist on being spoon-fed an answer? — Terry, Feb 11 '16 at 06:54
its not serious issue in PDO Drivers. you are doing some thing wrong. the above comments are enough to solve your problem. — Vigneswaran S, Feb 11 '16 at 06:58
All you need to understand is that [placeholder have to represent a complete data literal only](https://phpdelusions.net/pdo#in) - a string or a number namely. And thus it is impossible to substitute just an arbitrary query part with a placeholder. Hope it will clear your confusion. — Your Common Sense, Feb 11 '16 at 10:59

score 0 · Accepted Answer · answered Feb 11 '16 at 08:00

First of all, I agree with you (somewhat). It's a issue, not very serious one. Maybe they kept it knowingly. Or, maybe your stupid question has some debate potentials.

Every PDO parameter gets wrapped with quotes. (Personally I think should not be). When you pass the IN string at once, it puts quote around it which fails the query. But, you can do that (put quotes) with individual item. So, pass parameter to each item instead of preparing string before.

Now, to the solution:

$pdo = db()->getInstance();

$sql = "SELECT * FROM clients WHERE client_id IN :clients";

$clients = [223,224,225];

/* You could use bind with ? here. I am just making this with named parameters so that you could echo SQL */

$params = [];
$replacement = [];

foreach ($clients as $key => $value){
    $replacement[] = ":client$key";
    $params[":client" . $key] = $value;
}
$sql = str_replace(":clients", "("  . implode(",",$replacement) . ")", $sql);

echo $sql;
/* SELECT * FROM clients WHERE client_id IN (:client0,:client1,:client2) */

$stmt = $pdo->prepare($sql);
try{
    $stmt->execute($params);
    print_pre($stmt->fetchAll(PDO::FETCH_ASSOC));
} catch( \Exception $e){
    die("Query Execution Failed" . $e->getMessage());
}

This works like a charm. I tried by creating a dummy clients table. But the thing is your actual SQL now becomes:

SELECT * FROM clients WHERE client_id IN ('223','224','225')

Most people might not give a shit about it but you might lose some performance because the query is converting the id to string with quotes, especially you have a large database, complex queries and prefer integers.

Please, don't call an SQL syntax error on the user side a "PDO issue". Thank you. — Your Common Sense, Feb 11 '16 at 10:52
Regarding your fears about casting, mysql is smart enough to cast query parameters only once. — Your Common Sense, Feb 11 '16 at 11:02
By the way, you you should never catch errors to report them. — Your Common Sense, Feb 11 '16 at 11:15

PDO issues with binding IN operator

1 Answers1