为什么 PDO 不允许多个同名占位符?

2021-12-26 00:00:00 php mysql pdo placeholder

我使用 PHP 和带有 PDO 的 MySQL.有时我需要准备一个语句,其中一个变量(占位符)在这个查询中使用了多次.

I'm using PHP, and MySQL with PDO. Sometimes I need to prepare a statement with one variable (placeholder) used more than once in this query.

示例:

SELECT * FROM messages WHERE from_id = :user OR to_id = :user

但是,如果我尝试准备此语句,则会出现错误,因此我需要以如下方式执行此操作:

However if I will try to prepare this statement I will have an error so I need to do this in a way like this:

SELECT * FROM messages WHERE from_id = :user1 OR to_id = :user2

要调用这个语句,我需要有一个这样的数组:

To call this statement I will need to have an array like this:

array('user1'=>$user_id, 'user2'=>$user_id);

这对我来说看起来很愚蠢!为什么 MySQL (PDO?) 不允许我多次使用一个占位符并强迫我使用需要更多控制的额外变量?!

It looks so stupid for me! Why MySQL (PDO?) don't allowing me to use one place holder more than once and forcing me to use extra variables which requires more control?!

如果查询相对简单(就像我在上面发布的那样),这可以很容易地处理,但现在我构建了一个使用 5 (!!!) 单个变量的查询.每次添加占位符时,我需要检查很多地方的代码以使其正常.

This can be handled easy if the query is relatively simple (like I posted above), but now I built a query with 5 (!!!) uses of single variable. Each time I add the placeholder I need to check the code in many places to make it OK.

有什么设置或调整可以绕过这个吗?

Is there any setting or a tweak to bypass this?

推荐答案

有什么设置或调整可以绕过这个吗?

Is there any setting or a tweak to bypass this?

是的,有.您可以开启仿真模式,并且可以多次使用同一个占位符.

Yes, there is. You can turn emulation mode ON and be able to use the same placeholder multiple times.

因此,仅当仿真关闭时才会观察到所描述的行为.我真的不明白为什么会这样,但这里是 Wez Furlong(PDO 作者)的解释:

So the described behavior is observed only when the emulation is turned OFF. I don't really understand why it is so but here is an explanation from Wez Furlong (the PDO author):

进行更改有两个原因;首先,如果您在绑定中重复使用相同的变量,则在使用某些驱动程序时可能会导致崩溃.无法保证做正确的事情,并且有时可以将触发崩溃的方法用作安全漏洞的攻击媒介.

The change was made for two reasons; first and foremost, if you re-use the same variable in a bind, it is possible to induce a crash when using some drivers. It’s not possible to guarantee to do the right thing, and having a way to trigger a crash can sometimes be used as an attack vector for a security exploit.

第二个原因是便携性.一些驱动程序会在内部执行此检查并出错.如果您针对不强制执行此操作的驱动程序编写代码,那么您的代码将无法在不强制执行的驱动程序上运行.

The second reason is that of portability. Some drivers would internally perform this check and error out. If you code against the drivers that don’t enforce this, then your code won’t work on those that don’t.

http://paul-m-jones.com/archives/243#评论-740

相关文章