在 PDO 语句中转义列名 [英] Escaping column names in PDO statements

问题描述

我目前正在构建一个查询，其中字段/列和值部分可能由用户输入的数据组成.

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 语句中转义列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！