PHP、Python 中的 Mysql IN 子句参数化 [英] Mysql IN clause parameterization in PHP, Python

问题描述

我习惯于对我的查询执行以下操作:

I'm used to doing something like the following for my queries:

$array = array(123,456,10,57,1024,768); //random data
$sql = "select * from table where field in(".implode(',',$array).")";
mysql_query($sql);

这工作得很好，即使数组可能有 1,000 或更多要搜索的项目(实际上是对不存在的表的连接)也很快.对我来说，这非常有用，因为我将来自多个来源的数据连接在一起.

This works just fine, and is fast even when the array may have 1,000 or more items to search for(effectively a join against a nonexistent table). For me, this is quite useful because I am joining data from multiple sources together.

但是，我知道这有点不安全——如果我没有正确转义传入的数据，则可能会注入 SQL.

However, I know it is somewhat insecure -- SQL injection is possible if I don't escape my incoming data correctly.

我想尝试找出一个参数化的等价物；使用更强化"的代码来做那种数组输入"的方法.

I'd like to try to figure out a parametrized equivalent of this; some way to do that sort of "array IN" using more "hardened" code.

可能无法直接执行此操作，它可能需要一个辅助函数来进行替换.如果是这样，我正在寻找一个可以放入我的代码中的好方法，希望能够让我将数组作为参数传递.

It may not be possible to do this directly, it may require a helper function that does the replacements. If so, I'm looking for a good one I can drop into my code, hopefully something that will allow me to just pass an array as a parameter.

虽然我的示例是针对 PHP 的，但我也需要为 Python 解决这个问题，因为我在那里做同样的事情.

While my example is for PHP, I also need to figure this out for Python, as I'm doing the same thing there.

我在这里阅读了一些类似的问题，但没有看到任何(好的)答案.

I've read some similar questions here, but haven't seen any (good) answers.

推荐答案

使用 PDO 准备好的语句:

Using PDO prepared statements:

$placeholders = str_repeat('?, ', count($array)-1) . '?';
$stmt = $pdo->prepare("SELECT * FROM table WHERE field IN ($placeholders)");
$stmt->execute($array);

$placeholders 将包含一系列 ?, ?, ? 占位符，? 的数量与数组的大小相同.然后当你执行语句时，数组值绑定到占位符.

$placeholders will contain a sequence of ?, ?, ? placeholders, with the same number of ? as the size of the array. Then when you execute the statement, the array values are bound to the placeholders.

这篇关于PHP、Python 中的 Mysql IN 子句参数化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！