How to use mysql IN() function in php?

Question

Column state belongs to table2 and consists of the following varchars: "AL","AK",.. In my php code I have the following String: $states="AL,AK,AZ,IL"; I tried to use it in mySQL query in the following way:

$query = SELECT * FROM 'table2' WHERE  'state' IN('$states');

It does not show any results... What is the correct syntax for those apostrophes?

Be careful when putting data directly into a query. Make sure that those state abbreviations are coming straight from your code, and that you don't use this technique with data that must be escaped. — Brad, May 12 '13 at 02:27
As easier alternative (in combination with bound params) you can use `FIND_IN_SET()` instead of the `IN` clause. — mario, May 12 '13 at 02:30

score 7 · Accepted Answer · edited May 23 '17 at 12:21

7

The reason why it is not working is because:

values in the IN statement is not wrap with single quote
column name and table names are wrap with single quotes when it shouldn't be because they are identifiers not string literals

Try this,

$individualStates = explode(",", $states);
$newState = "'" . implode("','", $individualStates) . "'";
$query = "SELECT * FROM table2 WHERE state IN($newState)";

when parsed, the output of the statement is,

SELECT * FROM table2 WHERE state IN('AL','AK','AZ','IL')

How can I create a prepared statement for IN () operator?

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:21

Community

1
1

answered May 12 '13 at 02:30

John Woo

258,903
69
498
492

@JW웃, with empty array you will get empty string. – sectus May 12 '13 at 02:40
@sectus then with empty string, you'll get no result. (*expected because the array is empty*) – John Woo May 12 '13 at 02:42
1

@JW웃, if you have no empty string in your DB. : ) – sectus May 12 '13 at 02:43
@JW웃: And I think something should be said here regarding SQL escaping. – Alix Axel May 12 '13 at 02:44

Alix Axel · Answer 2 · 2013-05-12T02:54:52.750

5

This:

$states_in = "'" . implode("','", $individualStates) . "'";

Is bad. You're opening yourself to SQL injection. Instead, do this:

$states = explode(',', $states);
$states_in = array_map(array($instancePDO, 'quote'), $states);
$states_in = implode(',', $states_in);

If you prefer to use prepared statements instead, this will give you the placeholder string:

$states_placeholder = implode(',', array_fill(0, count($individualStates), '?'));

edited May 12 '13 at 02:54

answered May 12 '13 at 02:47

Alix Axel

151,645
95
393
500

Thank You fro answer. Do you mean XSS? I use only `` Or is there still possibility for injection?‎ – CHEBURASHKA May 12 '13 at 03:15

score 3 · Answer 3 · edited May 23 '17 at 12:21

3

$states = explode(',', $states);
$states = array_map(function($value){return "'$value'";}, $states);
$query = "SELECT * FROM `table2` WHERE  `state` IN(".implode(',', $states).")";

But better to use prepared statement. Read relative question.

edited May 23 '17 at 12:21

Community

1
1

answered May 12 '13 at 02:27

sectus

15,605
5
55
97

How to use mysql IN() function in php?

3 Answers3