oci_bind_by_name 是否可以安全地防止 SQL 注入? [英] Does oci_bind_by_name prevent SQL injection safely?

问题描述

我已经阅读了 oracle 提供的文档这里，其中说明:

I have read the documentation provided by oracle here, where it states that:

绑定对于 Oracle 数据库性能很重要，也是避免 SQL 注入安全问题的一种方式.

使用 oci_bind_by_name 转义变量有多安全?是否有更好的做法来避免 SQL 注入，或者 oci_bind_by_name 是否就足够了?

How safe is it to use oci_bind_by_name to escape variables? Are there better practices to avoid SQL Injection, or does oci_bind_by_name suffice?

TIA！

推荐答案

在常见情况下使用绑定参数就足够了，也是避免 SQL 注入的好习惯.

Using bound parameters is sufficient in common cases, and good practice for avoiding SQL injection.

但是准备好的语句中的参数只能用于 SQL 表达式中的值.换句话说，您通常会在其中编写带引号的字符串文字、带引号的日期文字或数字文字.一个参数 == 一个值(没有列表).

But a parameter in a prepared statement can be used only for a value in an SQL expression. In other words, where you would normally write a quoted string literal, quoted date literal, or a numeric literal. And one parameter == one value (no lists).

你应该在这些情况下使用绑定参数.如果你问这个问题是因为你认为如果有人回答他们不够用，你可能想跳过使用绑定参数，那么抱歉，您不会因为安全编程实践而获得原谅.

You should use bound parameters for those cases. If you're asking this question because you think you may want to skip using bound parameters if someone answers that they aren't sufficient, then sorry, you're not going to get excused from secure programming practices.

但是，还有其他(可能不太常见)的情况下，绑定参数不起作用.如果您需要使用动态表名、列名或其他标识符、或整个表达式或 SQL 关键字编写查询，那么您需要另一种方法.这些情况必须在准备时在 SQL 语法中固定，因此它们不能被参数化.

However, there are other (perhaps less common) cases for which bound parameters don't work. If you need to write a query with a dynamic table name, column name, or other identifier, or a whole expression, or an SQL keyword, then you need another method. These cases must be fixed in the SQL syntax at prepare time, so they cannot be parameterized.

例如，这里有一个使用变量表示的动态部分的查询，变量不能是参数:

For example, here's a query with dynamic parts denoted by use of variables, which cannot be parameters:

$sql = "SELECT * FROM mytable ORDER BY $column_of_users_choice $asc_or_desc";

对于这些情况，您应该使用白名单.换句话说，确保作为动态表名插入到查询中的字符串实际上是数据库中存在的表之一.确保 SQL 关键字是合法关键字.

You should use whitelisting for those cases. In other words, make sure that a string you interpolate into your query as a dynamic table name is actually one of the tables that exists in your database. Make sure that SQL keywords are legitimate keywords.

从不逐字获取用户输入并将其插入到 SQL(或在运行时解析的任何其他代码，例如您提供给 eval() 或 shellexec()).不仅仅是用户输入可能是不安全的内容.

Never take user input verbatim and interpolate it into SQL (or any other code that is parsed at runtime, like the argument you feed to eval() or shellexec()). And it's not just user input that can be unsafe content.

另见我的演示文稿 SQL 注入误区和谬误更多解释.

See also my presentation SQL Injection Myths and Fallacies for more explanation.

这篇关于oci_bind_by_name 是否可以安全地防止 SQL 注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！