在SQL语句中通过安全使用用户指定顺序的推荐方法 [英] recommended way to safely use a user specificed order by in a SQL statement

问题描述

是否可以按sql语句的一部分顺序绑定bindParam. 例如，以下可能吗?

Is it posible to bindParam in the order by portion of a sql statement. For example, is the following possible?

select whatever from table where age > :age order by :user_specified_order_by_field_name_here  

，如果不是，建议使用什么方法来确保user_specified_order_by_field_name_here不包含SQL注入代码?

and if not, what's the recommended way to make sure that the user_specified_order_by_field_name_here does not contain SQL injection code?

推荐答案

否，

No, PDO doesn't support dynamic table or column names as prepared values. Any column names you insert into the query will not be escaped, and will lead to a SQL injection vulnerability.

PDO :: Quote()也不起作用-它只能转义字符串，但是在mySQL中，列名不是字符串.

PDO::Quote() won't help either - it can escape strings only, but in mySQL, column names aren't strings.

防止出现问题的唯一100％安全的方法是将user_specified_order_by_field_name_here与表中的有效列列表进行比较.您甚至可以使用数字(内部解析为列名)来增加一层晦涩的含义.

The only 100% safe way to prevent problems is to compare user_specified_order_by_field_name_here against a list of valid columns in the table. You could even use numbers (that you resolve into column names internally) to add an additional layer of obscurity.

这篇关于在SQL语句中通过安全使用用户指定顺序的推荐方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！