为什么使用 mysql 准备好的语句比使用常见的转义函数更安全? [英] Why is using a mysql prepared statement more secure than using the common escape functions?

问题描述

另一个问题中的评论如下:

There's a comment in another question that says the following:

"说到数据库查询，总是尝试使用准备好的参数化查询.mysqli 和PDO 库支持这一点.这是比使用转义无限安全功能如mysql_real_escape_string."

"When it comes to database queries, always try and use prepared parameterised queries. The mysqli and PDO libraries support this. This is infinitely safer than using escaping functions such as mysql_real_escape_string."

来源

那么，我想问的是:为什么准备好的参数化查询更安全?

So, what i want to ask is: Why are prepared parameterized queries more secure?

推荐答案

我认为这里的人们缺少的一个重要点是，对于支持参数化查询的数据库，无需担心转义".数据库引擎不会将绑定的变量组合到 SQL 语句中，然后再解析整个事情；绑定变量保持独立，永远不会被解析为通用 SQL 语句.

An important point that I think people here are missing is that with a database that supports parameterized queries, there is no 'escaping' to worry about. The database engine doesn't combine the bound variables into the SQL statement and then parse the whole thing; The bound variables are kept separate and never parsed as a generic SQL statement.

这就是安全性和速度的来源.数据库引擎知道占位符仅包含数据，因此永远不会将其解析为完整的 SQL 语句.当您准备一次语句然后多次执行它时，速度就会提高；典型的例子是将多条记录插入到同一个表中.在这种情况下，数据库引擎只需要解析、优化等一次.

That's where the security and speed comes from. The database engine knows the placeholder contains data only, so it is never parsed as a full SQL statement. The speedup comes when you prepare a statement once and then execute it many times; the canonical example being inserting multiple records into the same table. In this case, the database engine needs to parse, optimize, etc. only once.

现在，一个问题是数据库抽象库.他们有时只是通过将绑定变量插入 SQL 语句并进行适当的转义来伪造它.不过，这比自己做要好.

Now, one gotcha is with database abstraction libraries. They sometimes fake it by just inserting the bound variables into the SQL statement with the proper escaping. Still, that is better than doing it yourself.

这篇关于为什么使用 mysql 准备好的语句比使用常见的转义函数更安全?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！