Geek asks and answers

Using REGEXP inside prepared mysqli statement in PHP

I am trying to make a simple search process with codes (noob) as follows:

$prep->prepare("SELECT * FROM details WHERE id REGEXP '?'");
$prep->bind_param("s", $search_query);

He gives me this warning:

Warning: mysqli_stmt::bind_param(): Number of variables doesn't match number of parameters in prepared statement

I guess this could be because the question mark is also used for RegExp (optional previous character).

Any idea on how to use REGEXP inside prepared statements (without conflicting question marks)?

Thank.

+3
source share
2 answers

Print single quotes around ?. Your code should read:

$prep->prepare("SELECT * FROM details WHERE id REGEXP ?");
$prep->bind_param("s", $search_query);

As of now, you are passing a single parameter, but ?in single quotes it is treated as a string, not a parameter marker.

+5
source

.

, , CONCAT .

// Target SQL
//    SELECT * FROM `table` WHERE `field` REGEXP "value1|value2|value3";
// Target Prepared Statement SQL
//    SELECT * FROM `table` WHERE `field` REGEXP ?|?|?;
$sql = 'SELECT * FROM `table` '
     . 'WHERE `field` REGEXP CONCAT(?, "|", ?, "|", ?)';
$bindings = [$value1, $value2, $value3];

$prepStmt = $db->prepare($sql);
$prepStmt->execute($bindings);
+1

More articles:


All Articles