PHP PDO bindValue using MySQL IN()?

Question

This query of mine returns nothing. It doesn't throw any errors. Can't find solution on this site. What is wrong here?

$query = "SELECT * FROM `votes` WHERE `tags` IN (:tags)";
$stmt = $dbh->prepare($query);
$stmt->bindValue(':tags', 'one, two, three');
$stmt->execute();
// 0 results, should be more

score 3 · Answer 1 · answered Nov 21 '12 at 08:14

3

That's not going to do what you want, regardless of whether you use bindParam() or bindValue().

A SQL query parameter takes the place of one value only. You can't bind a string containing commas and have it be interpreted as several values. You need one parameter placeholder for each distinct value in your list.

Also, you don't need to bind parameter value at all with PDO. You can pass an array to execute(). In this case, I'd use positional parameters instead of named parameters. PDO supports both, but don't mix them in one query.

$query = "SELECT * FROM `votes` WHERE `tags` IN (?, ?, ?)";
$stmt = $dbh->prepare($query);
$params = explode(', ', 'one, two, three');
$stmt->execute($params);

answered Nov 21 '12 at 08:14

Bill Karwin

538,548
86
673
828

` You can't bind a string containing commas and have it be interpreted as several values` So @Bill, this is not true, the query STRING will accept the seperated values, since a query as the name says it is a stirng, it is not familiar with "values","params" or whatever, an IN value, is a concatted string of possible values, but again a string – Tobias Hagenbeek Aug 22 '14 at 15:37
@TobiasHagenbeek, I think you're confusing parameter binding with string interpolation. We're talking about the former, not the latter. – Bill Karwin Aug 22 '14 at 16:01
i guess you make a good point then :) – Tobias Hagenbeek Aug 22 '14 at 16:25

score 0 · Answer 2 · answered Nov 21 '12 at 07:45

0

Your bind value has an error.

$stmt->bindValue(':tags', 'one, two, three');

should be

$stmt->bindValue(':tags', "'one', 'two', 'three'");

answered Nov 21 '12 at 07:45

Joshua Kaiser

1,461
9
17

This is not valid – Jam Feb 27 '19 at 06:14

score -3 · Accepted Answer · edited Mar 11 '18 at 07:52

-3

Try to Use PDO::bindParam instead.

$query = "SELECT * FROM `votes` WHERE `tags` IN (:tags)";
$stmt = $dbh->prepare($query);
$stmt->bindParam(':tags', implode(",", array('one, two, three')));
$stmt->execute();

Although using implode kinda defeats the original purpose of bind, and the stringified array is binded instead of the original array.

edited Mar 11 '18 at 07:52

Your Common Sense

156,878
40
214
345

answered Nov 21 '12 at 07:49

Elzo Valugi

27,240
15
95
114

Throws error `Cannot pass parameter x by reference` – Norse Nov 21 '12 at 07:50
1

one of the parameters I pass is `four'); DROP TABLE votes;--` – John Woo Nov 21 '12 at 08:32
2

Of course, do not include the user-input directly in the query – Elzo Valugi Nov 21 '12 at 09:04
1

This answer should be deleted. – Your Common Sense Nov 02 '13 at 19:40
2

why is this an answer???? has someone had success with this??????? – agent provocateur Dec 11 '14 at 00:46

PHP PDO bindValue using MySQL IN()?

3 Answers3