mysql PDO和存储过程动态SQL注入 [英] mysql PDO and stored procedure dynamic SQL injection

问题描述

正如我在许多文章中看到的那样，存储过程中的动态SQL容易受到SQL注入的攻击.但是，如果我们将先前的PDO与预准备语句一起使用，这仍然不安全吗?

As I have seen in many posts, dynamic SQL in stored procedure is vulnerable to SQL injection. But if we use previously PDO with prepared statement this still be unsafe?

示例:

CREATE PROCEDURE my_sp(
  IN in_var VARCHAR(32)
)
BEGIN
    DECLARE query VARCHAR(255);

    SET @query = CONCAT("SELECT * FROM my_table  WHERE my_column = '",in_var,"' LIMIT 1;";

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;

$dbh = new PDO( $connection_params );
$dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND,"SET NAMES utf8mb4");
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$sql = "CALL my_sp( :in_var )";
$stmt = $dbh->prepare( $sql );
$stmt->execute( array( ':in_var' => $_POST['in_var'] ) );

已编辑

很抱歉再次提出这个问题，但是我还不清楚.

Sorry for rectivate that question again but there is something that is not clear to me yet.

示例:

/* php */
$in_var = " ' OR '1' = '1'; -- ";

/*If I try with PDO without prepared statement and 
without calling a stored procedure THAT WORK ... the injection is succesful*/
$sql = "SELECT * FROM my_table  WHERE my_column = '$in_var' ";

/*With prepared statement NO RESULTS no errors so no injection*/
$sql = "SELECT * FROM my_table  WHERE my_column = :in_var ";

/*Now if I call a Stored Procedure with prepared statement in PDO PHP
but in the procedure I have not used prepared statement that return NO RESULTS no errors so no injection
*/
$sql = "CALL my_sp( :in_var )";

/*If I call the Stored procedure without prepared statement in PDO PHP
that got an PDO error:
Syntax error or access violation: 1064 ...
But seem that injection was not succesful
*/
$sql = "CALL my_sp( '$in_var' )";

我是一个初学者，我知道我的逻辑可能不好，但是似乎在PDO中使用预处理语句时，尽管在过程中不再使用预处理语句，但注入不会发生.

I am a beginner and I know that my logic may not be good, but it seems that when using prepared statement in PDO the injection does not happen although it is not used again prepared statement inside the procedure.

推荐答案

当然可以.

如果in_var等于' UNION SELECT password from admins --怎么办?

为避免这种情况，您不应使用 cargo cult 预处理语句，而应使用真实的语句，用占位符代替变量.

To avoid that, you should use not a cargo cult prepared statement but a real one, substituting your variable with a placeholder.

SET @query = CONCAT("SELECT * FROM my_table  WHERE my_column = ? LIMIT 1;");

PREPARE stmt FROM @query;
EXECUTE stmt USING @in_var;

这篇关于mysql PDO和存储过程动态SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！