mysql - 准备好的语句执行，要绑定(bind)的参数数量可变

Question

我使用的一些存储过程需要根据是否提供了过程输入参数来插入 WHERE 条件。为了避免潜在的注入(inject)点，我想对要成为内插标准一部分的值使用参数绑定(bind)。

由于添加到准备语句的条件以及要绑定(bind)的参数数量可能会因用户输入而异，因此我设计了以下方法来确定将哪些变量传递给 EXECUTE 语句。这行得通，但看起来不够优雅。

CREATE PROCEDURE foo (IN mandatory INT, IN optional INT, IN optional2 VARCHAR(20))
  BEGIN

    SELECT
      0, '', '', mandatory, optional, optional2
    INTO 
      @params, @sql, @where, @m, @o1, @o2;

    IF (@o1 > '' AND @o1 IS NOT NULL) THEN
      SET @where = CONCAT(@where, ' AND field = ?');
      SET @params = @params + 1;
    END IF;
    IF (@o2 > '' AND @o2 IS NOT NULL) THEN
      SET @where = CONCAT(@where, ' AND field2 = ?');
      SET @params = @params + 3;
    END IF;

    SET @sql = CONCAT('
      SELECT id, bar FROM table
      WHERE
        baz = ?
        ', @where
    );
    PREPARE STMT FROM @sql;
    CASE @params
      WHEN 0 THEN EXECUTE STMT USING @m;
      WHEN 1 THEN EXECUTE STMT USING @m, @o1;
      WHEN 3 THEN EXECUTE STMT USING @m, @o2;
      WHEN 4 THEN EXECUTE STMT USING @m, @o1, @o2;
    END CASE;
    DEALLOCATE PREPARE STMT;

  END$$

我知道其他选择:

• 将调用这些存储过程的二进制文件具有一个函数，该函数通过正则表达式传递用户提供的字符串来尝试识别潜在的 SQL 注入(inject)。
• 用户定义的函数可用于在给定动态输入数量的情况下动态构造 EXECUTE 语句。

但是，我想知道是否还有其他人遇到过这种纯粹使用 SQL 处理 EXECUTE 语句的动态构造的愿望。

Answer 1

However, I was wondering if anyone else has ran into this desire to handle dynamic construction of an EXECUTE statement purely with SQL.

是的，我也是。

这是一个 PHP 解决方案，用于根据未知长度的数组为准备好的语句生成问号列表:

/* My target query is this:
   SELECT fun FROM fun_stuff WHERE fun_key IN ( ...unknown number of values... )
*/

/* For this example let's set our array to this: */
$val_arr = array(1,2,3,4,5,6,7,8,9);
$val_arr_cnt = count($val_arr); /* and count it */

/* Now make prepared statement q-mark string from values array */
$sql_prep = str_pad('?', ($val_arr_cnt * 2) - 1, ',?', STR_PAD_RIGHT);

/* add it to query */
$sql = "SELECT fun FROM fun_stuff WHERE fun_key IN ($sql_prep)";

/* And the result:
   SELECT fun FROM fun_stuff WHERE fun_key IN (?,?,?,?,?,?,?,?,?)
 */

我不知道这有多有效。但我也时不时地想实现 MySQL 准备语句的安全性和效率，但具有可变长度的输入数组