在PDO语句中转义列名称。

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 requires a mysql connection resource in order to us so that is ruled out
mysql_real_escape_string需要mysql连接资源才能排除这种情况
PDO::quote adds quotes around the fieldnames which renders them useless in a query too
引号在字段名周围添加了引号，使得它们在查询中也无用
addslashes works but isn't really safe
addslash确实有效，但并不真正安全

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

任何人都知道，在将字段名传递给PDO: prepare之前，最好将字段名正确地插入到查询中。

5 个解决方案

#1

The ANSI standard way of doing a delimited identifier is:

ANSI做定界标识符的标准方法是:

SELECT "field1" ...

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

如果名字里有个"，翻倍:

SELECT "some""thing" ...

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中，这是行不通的，因为MySQL更倾向于认为双引号可以替代单引号。在这种情况下，您必须使用反斜杠(如Bjorn所描述的)和反斜杠转义。

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.

要正确执行反斜杠转义，需要mysql_real_escape_string，因为它与字符集有关。但关键是，因为mysql_real_escape_string和addslashes都不能够逃脱后面引用的字符。如果您可以确保列名中永远不会有非ascii字符，那么您只需手动回转义“和”\字符即可。

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.

无论哪种方式，这都与其他数据库不兼容。可以通过设置配置选项ANSI_QUOTES告诉MySQL允许使用ANSI语法。同样，SQL Server在默认情况下也会选择双引号;它还使用另一种语法，即方括号。同样，您可以配置它以支持ANSI语法和“quoted_identifier”选项。

Summary: if you only need MySQL compatibility:

如果你只需要MySQL兼容性:

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

使用反引号，不允许在名称中使用反引号、反斜杠和nul字符，因为转义它们是不可靠的

If you need cross-DBMS compatibility, either:

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

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,

使用双引号并要求MySQL/SQL-Server用户适当地更改配置。不允许在名称中使用双引号字符(因为Oracle甚至无法处理它们)。或者,

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.

c.为MySQL vs SQL Server和其他服务器设置一个设置，并根据这些设置生成引用、方括号或双引号语法。不允许双引号和反斜杠/反引号/nul。

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

这是你希望数据访问层有函数的东西，但是PDO没有。

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

总结:任意的列名是一个问题，如果可以的话最好避免。

Summary of the summary of the summary: gnnnnnnnnnnnh.

总结总结:

#2

The correct answer, is

正确答案是

str_replace("`", "``", $fieldname)

Wrong:

错误的:

mysql> SELECT `col\"umn` FROM user;
ERROR 1054 (42S22): Unknown column 'col\"umn' in 'field list'

Right:

正确的:

mysql> SELECT `kid``s` FROM user;
ERROR 1054 (42S22): Unknown column 'kid`s' in 'field list'
mysql> SELECT ```column``name``` FROM user;
ERROR 1054 (42S22): Unknown column '`column`name`' in 'field list'

(Note that in last example, the column name has 3 (three) extra back-ticks in it, just to show an extreme case)

(请注意，在最后一个例子中，列名有3(3)个额外的反引号，只是为了显示一个极端的例子)

#3

This may affect performance, but it should be secure.

这可能会影响性能，但应该是安全的。

First run a DESCRIBE table query to get a list of allowed field names, then match these agaisnt the user submitted data.

首先运行一个description表查询以获取允许字段名的列表，然后匹配用户提交的数据。

If there's a match then you can use the user-submitted data without the need for any escaping.

如果匹配，那么您可以使用用户提交的数据，而不需要任何转义。

If there's not match then it's a typo or a hack - either way it's an 'error' in the inputted data and the query should not be run.

如果没有匹配，那么它就是一个错误或者一个黑客——无论哪种方式，它都是输入数据中的一个“错误”，查询不应该运行。

The same could be done for 'dynamic' table names by running a SHOW TABLES query and matching from that result set.

通过运行SHOW TABLES查询并从结果集中进行匹配，也可以对“动态”表名执行相同的操作。

In one of my applications I have an 'install' script; part of this queries the database and table field names and then writes a php file that is always referred back to so I'm not constantly running DESCRIBE queries agains the database, eg

在我的一个应用程序中，我有一个“安装”脚本;其中一部分查询数据库和表字段名，然后编写一个php文件，该文件总是被引用，这样我就不会不停地运行描述对数据库的查询。

$db_allowed_names['tableName1']['id'] = 1;
$db_allowed_names['tableName1']['field1'] = 1;
$db_allowed_names['tableName1']['field2'] = 1;
$db_allowed_names['tableName2']['id'] = 1;
$db_allowed_names['tableName2']['field1'] = 1;
$db_allowed_names['tableName2']['field2'] = 1;
$db_allowed_names['tableName2']['field3'] = 1;

if($db_allowed_names['tableName1'][$_POST['field']]) {
     //ok
}

I use array keys like this as the if statement is a little faster than an in_array lookup

我使用这样的数组键，因为if语句比in_array查找要快一些

#4

How about something like this?

像这样的东西怎么样?

function filter_identifier($str, $extra='') {
    return preg_replace('/[^a-zA-Z0-9_'.$extra.']/', '', $str);
}

try {
    $res = $db->query('SELECT '.filter_identifier($_GET['column'], '\*').' FROM '.filter_identifier($_GET['table']).' WHERE id = ?', $id);
} catch (PDOException $e) {
    die('error querying database');
}

This is a simple white-list based character list. Any characters not in the list will get removed. Fortunately for me, I was able to make the database and tables, so I know there will never be any characters outside "a-zA-Z0-9_" (note: no space). You can add extra characters to the list via the $extra arg. If someone were to try and put "(SELECT * FROM users);--" in 'column', it would filter down to "SELECT*FROMusers", which would thrown an exception :)

这是一个简单的基于白名单的字符列表。任何不在列表中的字符将被删除。幸运的是，我能够创建数据库和表，所以我知道在“a-zA-Z0-9_”之外不会有任何字符(注意:没有空格)。您可以通过$extra arg向列表添加额外的字符。如果有人尝试输入“(SELECT *FROMusers)”;——“in 'column”，它会过滤为“SELECT*FROMusers”，这会抛出一个异常:

I try to avoid doing extra queries if at all possible (I'm very performance sensitive). So things like doing a DESCRIBE beforehand or hard-coding an array of tables/columns to check against is something I'd rather not do.

如果可能的话，我尽量避免做额外的查询(我对性能非常敏感)。因此，像事先做一个描述或者硬编码一个表/列数组来检查之类的事情我宁愿不去做。

#5

Wierd design of a project, but for your problem: Surround your field names with ` and use addslashes for the name as well.

一个项目的Wierd设计，但对于您的问题:围绕您的字段名称，并使用addslashes作为名称。

select `field1`, `field2` from table where `field3`=:value

#1