MySQL SELECT WHERE IN数组[重复]

Question

This question already has an answer here:

• How can I prevent SQL injection in PHP? 28 answers

I have a situation where I'd like to add a list of names in an array and use it in an SQL query.

How I do it:

$names = implode(',', $names);
$sql = "DELETE FROM product WHERE name NOT IN ($names)";

This works ok if I use id (but I can't do that here). Problem is - name can have a comma in it (i.e. - benchpress rack, blue) and that breaks this query. Is there a way to bypass this issue?

</div>

Answer 1

Try this:

$names = implode("','", $names);
$sql = "DELETE FROM product WHERE name NOT IN ('$names')";

If this is your actual code I would suggest switching to PDO, use a library, or escape your values with mysqli_real_escape_string.

Here is a link: http://php.net/manual/en/mysqli.real-escape-string.php

Here is a w3schools link for prepared statements, a decent high level view of how they work: http://www.w3schools.com/php/php_mysql_prepared_statements.asp

Here is how your statement query will run if your using PDO:

$query= $conn->prepare("DELETE FROM product WHERE name NOT IN (:names)");
$query->bindParam(':names', $names);
$query->execute();

And for mysqli it will be similar, refer to the w3schools link above for the differences.

I highly recommend you move away from the old mysql driver and at the very least switch to mysqli.