在运行时构建多个 LIKE 操作员准备好的语句 [英] Building Multiple LIKE Operator Prepared Statement at run-time

问题描述

表格tbl列
col1 |col |col3

SQL 我想与准备好的语句一起使用，并在运行时绑定参数

SQL I want to use with prepared statement with binding the parameter at runtime

select col1, col2, col3 
from tbl 
where col1=10 
and col2 between 0 and 10 and col3 like '%QUERY%';

问题是对 col3 的过滤取决于用户输入.如果只使用 2 个术语，则应该是

The problem is that filtering on col3 depends on the user inputs. If only 2 terms are used then it should be

select col1, col2, col3 
from tbl 
where col1=10 
and col2 between 0 and 10 and (col3 like '%QUERY%' AND col3 like '%QUERY2%');

如果找到 3 个输入，则应该是

If 3 inputs are found then it should be

select col1, col2, col3 
from tbl 
where col1=10 
and col2 between 0 and 10 and (col3 like '%QUERY%' AND col3 like '%QUERY2%' AND col3 like '%QUERY3%');

我只想使用准备好的语句而不是直接查询.我知道同样可以通过清理输入并运行直接查询来实现，但我仍然只喜欢准备好的语句.

I want to use only Prepared Statement and not a direct query. I know the same can be achieved by sanitizing the input and running a direct query still I only prefer Prepared Statements.

如何实现这一目标?

在我的代码下面:( 使用开放式 SQL 注入

Below the code I have :( with open SQL injection

$terms=explode(",",$_POST['txtD']);
$sql='';                     
for($i=0;$i<count($terms);$i++) {
    $terms[$i] = trim($terms[$i]);
    if ($i!=$count-1)
        $sql = $sql."`Places` LIKE '%$terms[$i]%' AND ";
    else
        $sql = $sql."`Places` LIKE '%$terms[$i]%'";
}

$stmt = mysqli_prepare($con,"select col1, col2, col3 from tbl where col1=? and col2 between ? and ? and ".$sql);
mysqli_stmt_bind_param($stmt,"iii", $param1, $param2, $param3);
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

推荐答案

考虑使用 implode 构建准备好的 SQL 语句的 LIKE 表达式部分.然后构建一个要使用 call_user_func_array().

Consider building your LIKE expression portion of prepared SQL statement using implode. Then build a param arguments to be run with call_user_func_array().

$terms = explode(",", str_replace(",", " ,", $_POST['txtD']));

// PREPARED STATEMENT BUILD
$likes = [];
foreach($terms as $t) {
    $likes[] = "col3 LIKE CONCAT('%', ?, '%')";
}

$expr = implode(" or ", $likes);
$sql = "select col1, col2, col3 from tbl ".
       "where col1=? and col2 between ? and ? and (". $expr .")";

// PARAM ARG BUILD
$type = 'iii' . str_repeat("s", count($terms));
$sql_params = array_merge(array($stmt, $type, $param1, $param2, $param3), $terms);

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con, $sql);
call_user_func_array('mysqli_stmt_bind_param', sql_params);    
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

SQL 和参数构建演示

或者，考虑 MySQL 的 REGEXP 用于使用管道表示 OR 逻辑的正则表达式:

Alternatively, consider MySQL's REGEXP for a regex expression using pipes to denote OR logic:

// REPACE COMMAS BY PIPE
$terms = str_replace(",", "|", str_replace(",", " ,", $_POST['txtD']));

$sql = "select col1, col2, col3 from tbl " .
       "where col1=? and col2 between ? and ? and col3 regexp ?";

// PREPARE AND EXECUTE QUERY
$stmt = mysqli_prepare($con);    
mysqli_stmt_bind_param($stmt, "iii", $param1, $param2, $param3, $terms);
mysqli_stmt_execute($stmt); 
mysqli_stmt_close($stmt);

请注意此处 REGEXP 的执行速度比等效的 慢LIKE 表达式.

Do note as mentioned here REGEXP is slower in execution than the equivalent LIKE expression.

这篇关于在运行时构建多个 LIKE 操作员准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！