PDO 准备好的语句有多安全 [英] how safe are PDO prepared statements

问题描述

不久前开始使用 PDO 准备好的语句，据我所知，它为您完成了所有的转义/安全.

Started using PDO prepared statements not too long ago, and, as i understand, it does all the escaping/security for you.

例如，假设 $_POST['title'] 是一个表单字段.

for example, assuming $_POST['title'] is a form field.

$title = $_POST['title'];
$query = "insert into blog(userID, title) values (?, ?)"
$st = $sql->prepare($query);
$st->bindParam(1, $_SESSION['user']['userID'], PDO::PARAM_INT);
$st->bindParam(2, $title);
$st->execute();

这样真的安全吗?我还需要做什么吗?我还需要考虑什么?

Is this really safe? Do i have to do anything else? what else do i have to take into consideration?

谢谢.

推荐答案

严格来说，实际上不需要转义，因为参数值永远不会插入到查询字符串中.

Strictly speaking, there's actually no escaping needed, because the parameter value is never interpolated into the query string.

查询参数的工作方式是当你调用prepare()时查询被发送到数据库服务器，参数值在你调用execute()时发送代码>.因此，它们与查询的文本形式分开.永远不会有 SQL 注入的机会(假设 PDO::ATTR_EMULATE_PREPARES 为假).

The way query parameters work is that the query is sent to the database server when you called prepare(), and parameter values are sent later, when you called execute(). So they are kept separate from the textual form of the query. There's never an opportunity for SQL injection (provided PDO::ATTR_EMULATE_PREPARES is false).

是的，查询参数可以帮助您避免这种形式的安全漏洞.

So yes, query parameters help you to avoid that form of security vulnerability.

它们是否 100% 能抵御任何安全漏洞?不，当然不是.您可能知道，查询参数仅取代 SQL 表达式中的单个文字值.您不能用单个参数代替值列表，例如:

Are they 100% proof against any security vulnerability? No, of course not. As you may know, a query parameter only takes the place of a single literal value in an SQL expression. You can't make a single parameter substitute for a list of values, for example:

SELECT * FROM blog WHERE userid IN ( ? );

您不能使用参数使表名或列名动态:

You can't use a parameter to make table names or column names dynamic:

SELECT * FROM blog ORDER BY ?;

您不能将参数用于任何其他类型的 SQL 语法:

You can't use a parameter for any other type of SQL syntax:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;

因此，在很多情况下，您必须在 prepare() 调用之前将查询作为字符串进行操作.在这些情况下，您仍然需要仔细编写代码以避免 SQL 注入.

So there are quite a few cases where you have to manipulate the query as a string, prior to the prepare() call. In these cases, you still need to write code carefully to avoid SQL injection.

这篇关于PDO 准备好的语句有多安全的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！