PHP Implode But Wrap Each Element In Quotes
PhpImplodePhp Problem Overview
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?
Php Solutions
Solution 1 - Php
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);
Solution 2 - Php
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'
Solution 3 - Php
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));
Solution 4 - Php
You can do like this as well
$elements = array('foo', 'bar', 'tar', 'dar');
$data = '"' . implode('", "', $elements) . '"';
echo $data; // "foo", "bar", "tar", "dar"
Solution 5 - Php
How about json_encode?
$arr=array("foo","bar","tar","dar");
$str=json_encode($arr);
echo $str;
Result: ["foo","bar","tar","dar"]
Solution 6 - Php
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);
Solution 7 - Php
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.