mysql PDO and stored procedure dynamic SQL injection

Question

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.

score 1 · Accepted Answer · answered Mar 30 '17 at 12:46

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;

mysql PDO and stored procedure dynamic SQL injection

1 Answers1

Linked