78

Assume I have an array:

 $elements = array('foo', 'bar', 'tar', 'dar');

Then I want to build up a DELETE IN SQL query:

 $SQL = "DELETE FROM elements
               WHERE id IN ('" . implode(',', $elements) . "')";

The problem is that the ids in the elements array aren't quoted each individually. I.E the query looks like:

 $SQL = "DELETE FROM elements
               WHERE id IN ('foo,bar,tar,dar');

What's the best, most elegants way to fix this?

DiverseAndRemote.com
  • 19,314
  • 10
  • 61
  • 70
Justin
  • 42,716
  • 77
  • 201
  • 296
  • 1
    Be very careful... you are likely open to some serious SQL injection. – Brad May 08 '12 at 00:16
  • What's the best way to prevent against SQL injection with the solution provided by `nickb` then? Thanks. – Justin May 08 '12 at 00:25

7 Answers7

137

Add the quotes into the implode call: (I'm assuming you meant implode)

$SQL = 'DELETE FROM elements
           WHERE id IN ("' . implode('", "', $elements) . '")';

This produces:

DELETE FROM elements WHERE id IN ("foo", "bar", "tar", "dar")

The best way to prevent against SQL injection is to make sure your elements are properly escaped.

An easy thing to do that should work (but I haven't tested it) is to use either array_map or array_walk, and escape every parameter, like so:

$elements = array();
$elements = array_map( 'mysql_real_escape_string', $elements);
nickb
  • 59,313
  • 13
  • 108
  • 143
19

You can use array_walk to iterate all the elements in side the array passing the reference to the element and add the quotes in the following way.

php 7.4 or newer

<?php

$arr = ['a','b','c'];

array_walk($arr, fn(&$x) => $x = "'$x'");

echo implode(',', $arr); // 'a','b','c'

php 7.3 or older version

<?php

$arr = ['a','b','c'];

array_walk($arr, function(&$x) {$x = "'$x'";});

echo implode(',', $arr); // 'a','b','c'
insign
  • 5,353
  • 1
  • 38
  • 35
  • Please add clarification as to why this is an answer. It may seem trivial to you, but since you do not add any explanation your suggestion is open to interpretation about its use. – Glubus Feb 01 '18 at 13:35
  • 1
    The link is the "clarification" – insign May 02 '20 at 03:16
4

You can run a simple array_map() function to wrap the strings in quotes and then wrap that around the implode() to add the commas:

$array = ["one", "two", "three", "four"];

implode(",", array_map(function($string) {
    return '"' . $string . '"';
}, $array));
ssharp_wp
  • 41
  • 1
3

How about json_encode?

$arr=array("foo","bar","tar","dar");
$str=json_encode($arr);
echo $str;

Result: ["foo","bar","tar","dar"]

911
  • 41
  • 4
1

You can do like this as well

$elements = array('foo', 'bar', 'tar', 'dar');

$data = '"' . implode('", "', $elements) . '"';

echo $data; // "foo", "bar", "tar", "dar"
Azam Alvi
  • 6,918
  • 8
  • 62
  • 89
0

Just to add to the top answer a bit here, even if you are using MySQLi it is possible to call real_escape_string using array_map by using the object method callable form. Here is an example, assuming $conn is your MySQLi connection:

$elements = array('foo', 'bar', 'tar', 'dar');
$cleanedElements = array_map([$conn, 'real_escape_string'], $ids);
$SQL = 'DELETE FROM elements WHERE id IN ("' . implode('", "', $elements) . '")';

Note that the first parameter of array_map is an array with the object followed by the method name. This is the same as executing the following for each item in the array:

$newItem = $conn->real_escape_string($item);
techdude
  • 1,334
  • 20
  • 29
0

I just want to note that the top answer won't fully work. You will have a missing element at the end of your implode.

So instead of:

DELETE FROM elements WHERE id IN ("foo", "bar", "tar", "dar")

You will get:

DELETE FROM elements WHERE id IN ("foo", "bar", "tar", "dar)

Notice that the end element "dar is now missing a quote.

n3wbsauce
  • 51
  • 5