为什么参数化查询允许将用户数据移出要解释的字符串？ [英] Why do Parameterized queries allow for moving user data out of string to be interpreted?

问题描述

来自 https://en.wikipedia.org/wiki/Code_injection#Preventing_problems

为防止代码注入问题，请使用安全的输入和输出处理，例如：

To prevent code injection problems, utilize secure input and output handling, such as:

• 使用的API，如果使用得当，可以防止所有输入字符。 参数化查询（也称为编译查询，准备好的语句，绑定变量）允许将用户数据移出要解释的字符串。此外，标准API [7]和类似的API脱离了要创建和解释的命令字符串的概念。

• Using APIs that, if used properly, are secure against all input characters. Parameterized queries (also known as "Compiled queries", "prepared statements", "bound variables") allows for moving user data out of string to be interpreted. Additionally Criteria API[7] and similar APIs move away from the concept of command strings to be created and interpreted.

我想知道如何以及为什么参数化查询（也称为编译查询，准备好的语句，绑定变量）允许将用户数据移出要解释的字符串。并防止或减轻代码注入问题？

I was wondering how and why "parameterized queries (also known as "Compiled queries", "prepared statements", "bound variables") allows for moving user data out of string to be interpreted" and prevent or mitigate code injection problems?

您还可以提供一些示例说明吗？

Can you also provide some examples in explanation?

谢谢。

推荐答案

编译查询使用数据库可以理解的特殊语法。他们通常为参数添加占位符，例如：

Compiled queries use special syntax that the database understands. They usually add placeholders for parameters such as in:

select * from applicant where name = ?

select * from applicant where name = :name

确切语法取决于

现在，一旦这些查询发送到数据库（没有特定的参数值），数据库就会保存它们。稍后（通常在同一数据库会话中），您可以通过每次仅提供参数值来多次运行它们。

Now, once those queries are sent to the database (without the specific parameter values), the database "saves" them. Later on (usually in the same database session), you can run them many times, by just providing the parameter values each time.

SQL注入安全性

它们对于SQL注入也是安全的。例如，如果在上一个查询中而不是简单的值（例如 Mary ），则使用值 x'；从申请人中删除； -数据库将安全运行。它将运行如下内容：

They are also safe against SQL injection. For example, if in the previous query instead of a simple value such as Mary you used the value x'; delete from applicant; -- the database will work safely. It would run something like:

select * from applicant where name = 'x; delete from applicant; --'

此查询可能不会找到任何东西，并且很安全。

This query won't probably find anything and will be safe.

相反，如果您不使用编译查询，而只是决定将SQL连接为字符串，则可以执行以下操作：

If instead you didn't use compiled query, but just decided to concatenate the SQL as a string you would do something like:

String sql = "select * from applicant where name = '" + param1 + "'";

最终会出现UNSAFE查询：

And would end up with the UNSAFE query:

select * from applicant where name = 'x'; delete from applicant; --

这将运行两个查询。第二个将从您的表中删除所有信息。可能不是您想要的。

This one would run two queries. The second one will delete all the information from your table. Probably not what you want.

这篇关于为什么参数化查询允许将用户数据移出要解释的字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！