mysql 存储过程 sql注入,mysql PDO和存储过程动态SQL注入

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?

Example:

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'] ) );

EDITED:

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

Example:

/* 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' )";

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.

解决方案

Yes, of course.

What if in_var is equal to ' UNION SELECT password from admins --?

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;