How to securely use delimited values from a string in an SQL query's WHERE IN condition? [duplicate]

Question

I have a form which is a select multiple input which POSTs values like this: option1,option2,option3 etc..

How is the best way to convert this to 'option1','option2','option3' etc...

Currenty I'm doing this, but it feels wrong??

$variable = explode(",", $variable);
$variable = implode("','", $variable);

The reason why I'm doing this is because I want to use the form select multiple inputs in a SQL Query using IN.

SELECT * FROM TABLE WHERE some_column IN ('$variable')

Highoctane

How does that even work out to put the first and the last '? Or do you put it in some code you don't show? – Bybidder

I edited the SQL statement where I added the quotation marks to the variable. – Highoctane

Don't build up query strings like this. Use PDO. – Reubenreuchlin

meagar, why not? why is PDO better? – Highoctane

You might be better of with FIND_IN_SET() then, escaping-wise. – Rescissory

Mario, how could I use this? I'm not interested in the position of the value? – Highoctane

If your csv file has a column with data "Hey, Chuck Norris" and it really meant to be a single cell, your script makes it a mess. – Idellaidelle

How are you getting the PHP code to comma-delimit the incoming selected values? What's your HTML? – Scotia

Answer 1

You can wrap whatever code in a function to make the "feels wrong" feeling disapear. E.g.:

function buildSqlInClauseFromCsv($csv)
{
        return "in ('" . str_replace(",", "','", $csv) . "') ";
}

Gutturalize

What I mean by "feels wrong" is that there might be a bitter way :) Do you think this function will do it faster? – Highoctane

Answer 2

If $variable = "option1,option2,option3"

you can use:

"SELECT * FROM TABLE WHERE FIND_IN_SET(some_column, '$variable')"

Scarabaeus

How to use NOT IN SET? – D

Kiran Reddy: SELECT * FROM TABLE WHERE NOT FIND_IN_SET(some_column, '$variable') – Scarabaeus

Answer 3

we know that implode converts array to string,we need to provide the separator and then array as shown below, here we have (coma ,) as a separator. Implode breaks each element of an array with the given separator,I have conceited '(single quotes) with the separator.

 $arr = array();
    $arr[] = "raam"; 
    $arr[] = "laxman"; 
    $arr[] = "Bharat"; 
    $arr[] = "Arjun"; 
    $arr[] = "Dhavel"; 
    var_dump($arr);


    $str = "'".implode("','", $arr)."'";
    echo $str;

output: 'raam','laxman','Bharat','Arjun','Dhavel'

Worley

While this may answer the question it’s always a good idea to put some text in your answer to explain what you're doing. Read how to write a good answer. – Yoruba

Answer 4

Here is what I used:

WHERE  column IN ('".str_replace(",", "','", $_GET[stringlist])."')

Eratosthenes

Great. why implode explode, if you can do it in one line. – Heall

love it. very simple solution. – Shortstop

Answer 5

There is only one correct way to escape strings for SQL - use the function provided by the database api to escape strings for SQL. While mysyl_* provides mysql_real_escape_string():

$choices = explode(",", $variable);

foreach($choices as &$choice)
    $choice = "'".mysql_real_escape_string($choice)."'";

$choices = implode(",", $choices);

PDO provides a method that will add quotes at the same time:

$choices = explode(",", $variable);

foreach($choices as &$choice)
    $choice = $pdoDb->quote($choice);

$choices = implode(",", $choices);

^{Note that PDO::prepare doesn't really work here}

Smutty

that is good practice, but unfortunately is not an answer to the question that was asked. – Pernik

@Hammerite: Not recommending - the question implies the asker is already using it. – Smutty

@Asad: I was under the impression it added quotes for you. Edited to use the PDO version, which does. – Smutty