替换：IN语句中的参数，其周围的函数重复

Question

I want to replace the parameter :firstname of the query by multiplying the expression as much as there are elements in the $firstnames array.

This is what I've got so far:

$query = "SELECT * FROM test WHERE col1 IN(UNHEX(:firstname)) OR IN (UNHEX('foo'))";

$firstnames = array("Jack", "John", "Michael");
$replacement = "";
foreach ($firstnames as $key => $value) {
    $replacement .= "\${1}".$key.",";
}
$replacement = rtrim($replacement,",");

$pattern = "/(:[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*)/i";
$query = (preg_replace($pattern, $replacement, $query)).",";

echo $query;

So far I only replaced the parameter, but not the function around it. There might be several other functions or string concatenations that should also be part of the repeating replacement. There might be some nested parenthesis also. So this would be the pattern to get all the nested parenthesis for the IN-statement:

$pattern= "/(\s*IN\s*)(\(((?>[^()]+)|(?2))*\))/i";

I just can't find a way to combine the two statements, to properly replace the parameter and the stuff that has been wrapped around it. Also, if there's no parameter in the IN-statement, there should be no replacement. This should be the result:

$query = "SELECT * FROM test WHERE col1 IN(UNHEX(:firstname1), UNHEX(:firstname2), UNHEX(:firstname3)) OR IN (UNHEX('foo'))";

UPDATE: Solution! In the meantime I stumbled upon several Problems, but finally solved the problem.

It seems not to be possible to get all the stuff by just using a regex. Therefore I decided to go with the preg_replace_callback function. In the first step I get the whole IN function, determined by all it's nested parentheses. In the second step I get the parameter an replace it with the desired amount of repetitions. The solution below fyi:

$params = array(
    "firstname" => array("Jack", "John", "Michael")
);

$query = "SELECT * FROM test WHERE col1 IN  (UNHEX(:firstname)) OR col2 IN(UNHEX('foo'))";

function createIN($query, $arrParam) {
    return preg_replace_callback(
        "/(\\s*IN\s*(\\((?:(?>[^()]+)|(?2))*\)))/is", 
        function ($matches) use($arrParam) {
            $pattern = "/(\s*IN\s*\()((.*?((['\"`]).*?\5)?)*)(:[a-zA-Z_][a-zA-Z0-9_]*)(.*)(\))/is";
            preg_match($pattern, $matches[1], $matches2);
            $replacement = $matches[0];
            if(isset($matches2[6])) {
                $replacement = $matches2[1];
                foreach ($arrParam[substr($matches2[6],1)] as $key => $value) {
                    $replacement .= $matches2[2].$matches2[6].$key.$matches2[7].",";
                }
                $replacement = rtrim($replacement,",").$matches2[8];
            }
            return $replacement;
        }, 
        $query
    );
}

echo createIN ($query, $params);

Answer 1

Create the query dynamically, and use prepared statements. A simple way to do this is to add each parameter name to an array and then use implode() to convert the array into a comma-separated list of the names. This example should work:

$query = "SELECT * FROM test WHERE col1 IN(";

$firstnames = array("Jack", "John", "Michael");
$params = array();
$query_in = array();
foreach ($firstnames as $key => $value) {
    //$params[] = $value;
    //$query_in[] = "?";
    // Use this for named parameters:
    $params[":param".$key] = $value;
    $query_in[] = ":param".$key;
}

$query .= implode(",",$query_in);
$query .= ") OR IN (UNHEX('foo'))";
/** @var PDO $pdo */
$stm = $pdo->prepare($query);
$stm->execute($params);

echo $stm->queryString;