为什么不应该将CONCAT()用于静态字符串文字呢? [英] Why should you not use CONCAT() for static string literals?

问题描述

所以你有

$sql = "SELECT * FROM `table` WHERE `some_text_field` LIKE CONCAT('%', ?, '%')";
$stmt = $dbh->prepare($sql);
$stmt->execute(array($_POST['badies_code']));

看着另一个问题，我发现这会引起安全隐患，但是为什么呢?

And looking at another question i found that this causes a security concern, but why?

我找到了这个问题，一个被低估的答案和被高估的评论，这就是我问的原因

I found this question, a downvoted answer and an upvoted comment that is why i ask

评论说

这不是执行此操作的正确方法.您不应该将CONCAT()用于三个静态字符串文字，因为它会使您接触到特定类型的SQL注入(我忘记了名称). –西奥多·史密斯

This is not the correct way to do this. You should not use CONCAT() for three static string literals, as it opens you up to a specific type of SQL injection (i forget the name). – Theodore R. Smith

PHP PDO准备好的语句-mysql LIKE查询

推荐答案

我认为@ TheodoreR.Smith可能意味着Oracle数据库中的所谓的 Lateral SQL Injection ^[1]

I think what @TheodoreR.Smith may have meant is the so called Lateral SQL Injection in Oracle Database^[1][2].

它可以通过更改保存格式信息的环境变量来工作，例如 NLS_DATE_FORMAT 或 NLS_NUMERIC_CHARACTERS ，然后将其用于存储过程中，该存储过程可动态构建和执行语句(这是使用字符串连接的地方，由||运算符表示):

It works by changing environment variables holding format information such as NLS_DATE_FORMAT, or NLS_NUMERIC_CHARACTERS, which are then used in a stored procedure that builds and executes a statement dynamically (this is where string concatenation is used, denoted by the || operators):

CREATE OR REPLACE PROCEDURE date_proc IS
    stmt VARCHAR2(200);
    v_date DATE := SYSDATE;
BEGIN
    stmt := 'select object_name from all_objects where created = ''' || v_date || '''';
    EXECUTE IMMEDIATE stmt;
END;

此处SYSDATE以NLS_DATE_FORMAT中指定的格式返回当前日期.尽管该过程没有参数，但是将日期格式更改为' or 1=1--:

Here SYSDATE returns the current date in the format specified in NLS_DATE_FORMAT. Although the procedure has no parameter, changing the date format to something like ' or 1=1--:

ALTER SESSION SET NLS_DATE_FORMAT = ''' or 1=1--'

结果语句为:

select object_name from all_objects where created = '' or 1=1--'

此环境变量操作特定于Oracle数据库.同样，可以使用准备好的语句来减轻它:

This environment variable manipulation is specific to Oracle Database. And again, it can be mitigated using prepared statements:

CREATE OR REPLACE PROCEDURE date_proc IS
    stmt VARCHAR2(200);
    v_date DATE := SYSDATE;
BEGIN
    stmt := 'select object_name from all_objects where created = :date';
    EXEC SQL PREPARE prepared_stmt FROM :stmt;
    EXEC SQL EXECUTE prepared_stmt USING :v_date;
end;

我不知道MySQL很容易进行这种环境变量操作.

I’m not aware that MySQL is prone to this kind of environment variable manipulation.

但是，无论是在应用程序中还是在数据库中发生，没有适当处理就动态构建语句都容易导致SQL注入.因此，在存储过程中使用准备好的语句也是强制性的.

However, building statements dynamically without proper processing is prone to SQL injections, no matter whether it happens in the application or in the database. So using prepared statements in stored procedures is mandatory as well.

这篇关于为什么不应该将CONCAT()用于静态字符串文字呢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！