問題描述
我使用 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
這篇關于為什么 PDO 不允許多個同名占位符?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！