PHP Implode But Wrap Each Element In Quotes [duplicate]

Question

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?

Fellah

Be very careful... you are likely open to some serious SQL injection. – Nickelic

What's the best way to prevent against SQL injection with the solution provided by nickb then? Thanks. – Fellah

Answer 1

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);

Hem

Perfect, so what's the best way to prevent against SQL injection as Brad suggested with this solution? – Fellah

@Jusin I've edited my answer with a suggestion on preventing SQL injection. – Hem

@Fellah The best way is to use prepared statements php.net/manual/en/pdo.prepared-statements.php php.net/manual/en/mysqli.prepare.php – Fbi

Don't use mysql_ functions. – Inspiration

Answer 2

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'

Surgery

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. – Mincey

The link is the "clarification" – Surgery

Answer 3

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));

Gayden

Answer 4

How about json_encode?

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

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

Linsk

This is the best answer. – Astronaut

This is the best answer by far! Short and fast! Cheers! – Wonderwork

If you use this you'll end up with an unwanted [], which the query will not accept. trim(json_encode($arr), '[]') will encode the array and then remove the brackets so the string is then usable the query. – Nonbelligerent

Answer 5

You can do like this as well

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

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

echo $data; // "foo", "bar", "tar", "dar" 

Vernation

Answer 6

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);

Demijohn

Answer 7

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.

Conlon