問題描述

我目前正在構建一個查詢，其中字段/列和值部分可能由用戶輸入的數據組成.

I am currently building a query where both the field/column and value parts possibly consist of user inputted data.

問題是轉義字段名.我正在使用準備好的語句來正確轉義和引用值，但是在轉義字段名時我遇到了麻煩.

The problem is escaping the fieldnames. I'm using prepared statements in order to properly escape and quote the values but when escaping the fieldnames i run into trouble.

• mysql_real_escape_string 需要一個 mysql 連接資源以便我們排除
• PDO::quote 在字段名周圍添加引號，這使得它們在查詢中也無用
• addslashes 有效，但并不安全

有人知道在將字段名傳遞給 PDO::prepare 之前將字段名正確插入到查詢中的最佳方法是什么嗎?

Anyone has an idea on what the best way is to properly insert the fieldnames into the query before passing it to PDO::prepare?

推薦答案

ANSI 標準的分隔標識符方式是:

The ANSI standard way of doing a delimited identifier is:

SELECT "field1" ...

如果名稱中有 "，請將其加倍:

and if there's a " in the name, double it:

SELECT "some""thing" ...

不幸的是，這在具有默認設置的 MySQL 中不起作用，因為 MySQL 更喜歡認為雙引號是字符串文字的單引號的替代方案.在這種情況下，您必須使用反引號(如 Bj?rn 所述)和反斜杠轉義.

Unfortunately this doesn't work in MySQL with the default settings, because MySQL prefers to think double quotes are an alternative to single quotes for string literals. In this case you have to use backticks (as outlined by Bj?rn) and backslash-escaping.

要正確進行反斜杠轉義，您將需要 mysql_real_escape_string，因為它依賴于字符集.但這一點沒有實際意義，因為mysql_real_escape_string 和addslashes 都不會轉義反引號.如果您可以確定列名中永遠不會有非 ASCII 字符，您只需手動反斜杠轉義 ` 和 字符即可.

To do backslash escaping correctly, you would need mysql_real_escape_string, because it's character-set-dependent. But the point is moot, because neither mysql_real_escape_string nor addslashes escape the backquote character. If you can be sure there will never be non-ASCII characters in the column names you can get away with just manually backslash-escaping the ` and characters.

無論如何，這與其他數據庫不兼容.您可以通過設置配置選項 ANSI_QUOTES 來告訴 MySQL 允許 ANSI 語法.類似地，SQL Server 也默認在雙引號上阻塞；它使用另一種語法，即方括號.同樣，您可以使用quoted_identifier"選項將其配置為支持 ANSI 語法.

Either way, this isn't compatible with other databases. You can tell MySQL to allow the ANSI syntax by setting the config option ANSI_QUOTES. Similarly, SQL Server also chokes on double quotes by default; it uses yet another syntax, namely square brackets. Again, you can configure it to support the ANSI syntax with the ‘quoted_identifier’ option.

總結:如果你只需要 MySQL 兼容性:

Summary: if you only need MySQL compatibility:

一個.使用反引號并禁止在名稱中使用反引號、反斜杠和空字符，因為轉義它們是不可靠的

a. use backquotes and disallow the backquote, backslash and nul character in names because escaping them is unreliable

如果您需要跨 DBMS 兼容性，可以:

If you need cross-DBMS compatibility, either:

B.使用雙引號并要求 MySQL/SQL-Server 用戶適當地更改配置.禁止在名稱中使用雙引號字符(因為 Oracle 無法處理它們甚至轉義).或者，

b. use double quotes and require MySQL/SQL-Server users to change the configuration appropriately. Disallow double-quote characters in the name (as Oracle can't handle them even escaped). Or,

c.有一個 MySQL vs SQL Server vs Others 的設置，并根據它生成反引號、方括號或雙引號語法.禁止雙引號和反斜杠/反引號/nul.

c. have a setting for MySQL vs SQL Server vs Others, and produce either the backquote, square bracket, or double-quote syntax depending on that. Disallow both double-quotes and backslash/backquote/nul.

這是您希望數據訪問層具有的功能，但 PDO 沒有.

This is something you'd hope the data access layer would have a function for, but PDO doesn't.

摘要總結:任意列名都是一個問題，如果你能幫忙，最好避免.

Summary of the summary: arbitrary column names are a problem, best avoided if you can help it.

總結總結:gnnnnnnnnnnnh.

Summary of the summary of the summary: gnnnnnnnnnnnh.

這篇關于在 PDO 語句中轉義列名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！