在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在字段名周围添加引号，这也使它们在查询中毫无用处
• 加斜线有效，但并不十分安全

任何人都知道在将字段名传递给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和加号都不能转义反引号字符.如果您可以确定列名中绝不会出现非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.使用反引号并在名称中禁止使用反引号，反斜杠和nul字符，因为转义它们是不可靠的

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屋！