MYSQL & C#: How to escape the elements in a SELECT WHERE IN (...) clause?

I have procedure with a single string parameter to retrieve records from my table test which has two fields id(int) and Name(varchar). the query in the procedure is shown below

Select * from test where id in (strParam);

and value in the parameter will be

strParam="1,2";

but the result will be wrong because query will be as shown below

Select开发者_如何转开发 * from test where id in ('1,2');

but i need the query to be like shown below

Select * from test where id in (1,2);

please help me with a solution the programming language is C# thanks, suraj

Usually you construct the SQL correctly in your programming language:

Select * from test where id in ('1,2');

should come from your application code, where it's easier to change strParam="1,2"; to strParam="'1','2'":

• Split (explode) the string into an array
• escape each element in the array (using the correct MySQL-ESCAPE function)
• Join (implode) the array back into a string,

If you really can't change the application code, maybe some SQL tricks could work. Try:

SELECT * FROM test where FIND_IN_SET(ID,strParam) > 0

Not sure if this is the most efficient way:

Explode the value strParam to an array and then build up the string you need in the query:

<?php 

$arrayParam = explode(',', $strParam);
$strParamQuery = '(';

foreach ($arrayParam as $Param) {
    if ($strParamQuery != '(') { $strParamQuery = $strParamQuery.','; //Add a comma to all but the first occurence
    $strParamQuery = $strParamQuery.$param;
}

$strParamQuery = $strParamQuery.')';

$query = 'Select * from test where id in '.$strParamQuery.';';
?>