准备好的语句如何防止 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 an 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;
之所以有效，是因为我们将数据直接添加到程序体中，它成为程序的一部分，因此数据可能会改变程序，并且根据传递的数据，我们将有常规输出或表用户已删除.
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屋！
