Passing an array to a query using a WHERE clause

Question

Given an array of ids $galleries = array(1,2,5) I want to have a SQL query that uses the values of the array in its WHERE clause like:

SELECT *
FROM galleries
WHERE id = /* values of array $galleries... eg. (1 || 2 || 5) */

How can I generate this query string to use with MySQL?

Answer 1

Below is the method I have used, using PDO with named placeholders for other data. To overcome SQL injection I am filtering the array to accept only the values that are integers and rejecting all others.

$owner_id = 123;
$galleries = array(1,2,5,'abc');

$good_galleries = array_filter($chapter_arr, 'is_numeric');

$sql = "SELECT * FROM galleries WHERE owner=:OWNER_ID AND id IN ($good_galleries)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    "OWNER_ID" => $owner_id,
));

$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

Answer 2

Safer.

$galleries = array(1,2,5);
array_walk($galleries , 'intval');
$ids = implode(',', $galleries);
$sql = "SELECT * FROM galleries WHERE id IN ($ids)";

Answer 3

You may have table texts (T_ID (int), T_TEXT (text)) and table test (id (int), var (varchar(255)))

In insert into test values (1, '1,2,3') ; the following will output rows from table texts where T_ID IN (1,2,3):

SELECT * FROM `texts` WHERE (SELECT FIND_IN_SET( T_ID, ( SELECT var FROM test WHERE id =1 ) ) AS tm) >0

This way you can manage a simple n2m database relation without an extra table and using only SQL without the need to use PHP or some other programming language.

Answer 4

Basic methods to prevent SQL injection are:

• Use prepared statements and parameterized queries
• Escaping the special characters in your unsafe variable

Using prepared statements and parameterized queries query is considered the better practice, but if you choose the escaping characters method then you can try my example below.

You can generate the queries by using array_map to add a single quote to each of elements in the $galleries:

$galleries = array(1,2,5);

$galleries_str = implode(', ',
                     array_map(function(&$item){
                                   return "'" .mysql_real_escape_string($item) . "'";
                               }, $galleries));

$sql = "SELECT * FROM gallery WHERE id IN (" . $galleries_str . ");";

The generated $sql var will be:

SELECT * FROM gallery WHERE id IN ('1', '2', '5');

Note: mysql_real_escape_string, as described in its documentation here, was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used. See also MySQL: choosing an API guide and related FAQ for more information. Alternatives to this function include:

• mysqli_real_escape_string()

• PDO::quote()

Answer 5

For MySQLi with an escape function:

$ids = array_map(function($a) use($mysqli) { 
    return is_string($a) ? "'".$mysqli->real_escape_string($a)."'" : $a;
  }, $ids);
$ids = join(',', $ids);  
$result = $mysqli->query("SELECT * FROM galleries WHERE id IN ($ids)");

For PDO with prepared statement:

$qmarks = implode(',', array_fill(0, count($ids), '?'));
$sth = $dbh->prepare("SELECT * FROM galleries WHERE id IN ($qmarks)");
$sth->execute($ids);

Answer 6

Safe way without PDO:

$ids = array_filter(array_unique(array_map('intval', (array)$ids)));

if ($ids) {
    $query = 'SELECT * FROM `galleries` WHERE `id` IN ('.implode(',', $ids).');';
}

• (array)$ids Cast $ids variable to array
• array_map Transform all array values into integers
• array_unique Remove repeated values
• array_filter Remove zero values
• implode Join all values to IN selection