准备好的语句如何防止SQL注入攻击? [英] How can prepared statements protect from SQL injection attacks?
问题描述
准备好的声明如何帮助我们防止
How do prepared statements help us prevent SQL injection attacks?
维基百科说:
准备好的语句可以抵御SQL注入，因为 参数值，稍后使用其他参数传输 协议，无需正确转义.如果原始声明 模板不是从外部输入派生的，SQL注入不能 发生.
Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.
我不太清楚原因.用简单的英语和一些示例来做一个简单的解释是什么?
I cannot see the reason very well. What would be a simple explanation in easy English and some examples?
推荐答案
想法很简单-查询和数据分别发送到数据库服务器 .
就是这样.
The idea is very simple - the query and the data are sent to the database server separately.
That's all.
SQL注入问题的根源是代码和数据的混合.
The root of the SQL injection problem is in the mixing of the code and the data.
实际上，我们的SQL查询是合法程序. 我们正在动态创建这样的程序，动态添加一些数据.因此，数据可能会干扰程序代码甚至更改它，如每个SQL注入示例所示(所有示例均在PHP/Mysql中):
In fact, our SQL query is a legitimate program. And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program code and even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):
$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";
将产生常规查询
SELECT * FROM users where id=1
这段代码
$spoiled_data = "1; DROP TABLE users;"
$query = "SELECT * FROM users where id=$spoiled_data";
会产生恶意序列
SELECT * FROM users where id=1; DROP TABLE users;
之所以起作用，是因为我们将数据直接添加到程序主体中，并且它成为程序的一部分，因此数据可能会更改程序，并且根据传递的数据，我们将获得常规输出或表users已删除.
It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a table users deleted.
在准备好的语句的情况下，我们不会更改程序，但程序保持不变
这才是重点.
While in case of prepared statements we don't alter our program, it remains intact
That's the point.
我们首先将程序发送到服务器
We are sending a program to the server first
$db->prepare("SELECT * FROM users where id=?");
其中数据被称为参数或占位符的某些变量代替.
where the data is substituted by some variable called a parameter or a placeholder.
请注意，完全相同的查询将发送到服务器，其中没有任何数据！然后，我们通过 second 请求发送数据，该请求实际上与查询本身是分开的:
Note that exactly the same query is sent to the server, without any data in it! And then we're sending the data with the second request, essentially separated from the query itself:
$db->execute($data);
因此它不能更改我们的程序并造成任何伤害.
很简单-是吗?
so it can't alter our program and do any harm.
Quite simple - isn't it?
我必须补充的唯一一件事是在每本手册中始终省略:
The only thing I have to add that always omitted in the every manual:
准备好的语句只能保护数据文字，但不能与其他任何查询部分一起使用.
因此，一旦我们不得不添加动态的标识符(例如字段名)，准备好的语句就无济于事.我已经 最近对此事进行了解释 ，所以我不再赘述.
Prepared statements can protect only data literals, but cannot be used with any other query part.
So, once we have to add, say, a dynamical identifier - a field name, for example - prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.
这篇关于准备好的语句如何防止SQL注入攻击?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
