动态准备声明坏吗？ （用php + mysqli） [英] Are Dynamic Prepared Statements Bad? (with php + mysqli)

问题描述

我喜欢Dynamic SQL的灵活性，我喜欢Prepared语句的安全性+改进的性能。所以我真正想要的是动态准备语句，这是很麻烦的，因为bind_param和bind_result接受固定数量的参数。所以我利用一个eval（）语句来解决这个问题。但是我觉得这是一个坏主意。这是我的意思的示例代码

  // WHERE条件的数组
 $ param = array（'customer_id'= > 1，'qty'=>'2'）; 
 $ stmt = $ mysqli-> stmt_init（）; 
 
 $ types =''; $ bindParam = array（）; $ where =''; $ count = 0; 
 
 //构建动态sql和param绑定条件
 foreach（$ param as $ key => $ val）
 {
 $ types。='i ; 
 $ bindParam [] ='$ p'。$ count。'= $ param ['。$ key。']'; 
 $ where。=$ key =？AND; 
 $ count ++; 
} 
 
 //准备查询 -  SELECT * FROM t1 WHERE customer_id =？和qty =？ 
 $ sql =SELECT * FROM t1 WHERE.substr（$ where，0，strlen（$ where）-4）; 
 $ stmt-> prepare（$ sql）; 
 
 //组合bind_param命令
 $ command ='$ stmt-> bind_param（$ types，'.implode（'，'，$ bindParam）。'）;'; 
 
 //评估命令 -  $ stmt-> bind_param（$ types，$ p0 = $ param [customer_id]，$ p1 = $ param [qty]）; 
 eval（$ command）; 
  

最后一个eval（）语句是一个坏主意吗？我试图通过将值封装在变量名称$ param后面来避免代码注入。

有没有人有意见或其他建议？是否有需要注意的问题？

解决方案

我认为使用 eval ）

尝试这样：

• 迭代params数组以生成带有问号的SQL字符串SELECT * FROM t1 WHERE p1 =？AND p2 =？


• 调用 prepare（）


• 使用 call_user_func_array（）调用 bind_param（），传入动态参数数组。

代码：

  call_user_func_array（array（$ stmt，'bind_param'），array（$ types）+ $ param）; 
  

I like the flexibility of Dynamic SQL and I like the security + improved performance of Prepared Statements. So what I really want is Dynamic Prepared Statements, which is troublesome to make because bind_param and bind_result accept "fixed" number of arguments. So I made use of an eval() statement to get around this problem. But I get the feeling this is a bad idea. Here's example code of what I mean

// array of WHERE conditions
$param = array('customer_id'=>1, 'qty'=>'2');
$stmt = $mysqli->stmt_init();

$types = ''; $bindParam = array(); $where = ''; $count = 0;

// build the dynamic sql and param bind conditions
foreach($param as $key=>$val)
{
    $types .= 'i';
    $bindParam[] = '$p'.$count.'=$param["'.$key.'"]'; 
    $where .= "$key = ? AND ";
    $count++;
}

// prepare the query -- SELECT * FROM t1 WHERE customer_id = ? AND qty = ?
$sql = "SELECT * FROM t1 WHERE ".substr($where, 0, strlen($where)-4);
$stmt->prepare($sql);

// assemble the bind_param command
$command = '$stmt->bind_param($types, '.implode(', ', $bindParam).');';

// evaluate the command -- $stmt->bind_param($types,$p0=$param["customer_id"],$p1=$param["qty"]);
eval($command);

Is that last eval() statement a bad idea? I tried to avoid code injection by encapsulating values behind the variable name $param.

Does anyone have an opinion or other suggestions? Are there issues I need to be aware of?

解决方案

I think it is dangerous to use eval() here.

Try this:

• iterate the params array to build the SQL string with question marks "SELECT * FROM t1 WHERE p1 = ? AND p2 = ?"
• call prepare() on that
• use call_user_func_array() to make the call to bind_param(), passing in the dynamic params array.

The code:

call_user_func_array(array($stmt, 'bind_param'), array($types)+$param);

这篇关于动态准备声明坏吗？ （用php + mysqli）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！