Sql Injection in Stored Procedures

Question

It is possible to carry out injection of SQL in Stored Procedures, especially when calling a Procedure for PDO of PHP?

DELIMITER //

CREATE PROCEDURE
  procedureTeste( string VARCHAR(255) )
BEGIN
   SELECT * FROM produtos WHERE nome = string;
END
//

DELIMITER ;

It would be possible to carry out SQL Injection in a Procedure using the bindParam of PDO?

$PDO = $this->PDO->prepare('call procedureTeste(:nomeProd)');
$PDO->bindParam(':nomeProd', $nome, PDO::PARAM_STR);
$PDO->execute();

Answer 1

It is possible to perform sql injection in stored procedures?

It depends on how you create these procedures, Anyway it is not safe to let the user inform any value. This is because the user can type a very long text and end up having access to error messages (if they are enabled and not filtered). This will leave your data structure exposed and how you work with it.

And obviously, he’ll know that you don’t handle input and output data.

Table structure:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| user        | varchar(32)  | NO   |     | NULL    |                |
| password    | varchar(128) | NO   |     | NULL    |                |
| last_access | timestamp    | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Stored Procedures Vulnerable:

DROP PROCEDURE IF EXISTS login;
DELIMITER //
CREATE PROCEDURE login( u VARCHAR(255), p VARCHAR(255) )
BEGIN
    SET @query = CONCAT("SELECT * FROM users WHERE `user` = '", u, "' AND `password` = '", p,"'");

    SELECT @query;

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

DELIMITER ;

See that if I run the query below, Mysql will return all records.

CALL login("' OR '1' = '1", "' OR '1' = '1");

Stored Procedures unmatched safer:

DROP PROCEDURE IF EXISTS login2;
DELIMITER //
CREATE PROCEDURE login2( u VARCHAR(255), p VARCHAR(255) )
BEGIN
    SELECT * FROM users WHERE `user` = u AND `password` = p;
END
//

DELIMITER ;

See that if I run the query below, the Mysql nay will return the records.

CALL login2("' OR '1' = '1", "' OR '1' = '1");

This occurs, because in the second way the Stored Procedures are pre-compiled. In other words, the database creates this internal software before using it. In this case, only the program code is interpreted without any parameter influence.

---

It would be possible to carry out SQL Injection in a Procedure with bindParam of the PDO?

The advantage is that the value of the parameter (with bindParam) is never interpolated following consultation.

So yes, query parameters help you avoid this security vulnerability. However, it is necessary to be aware and not do anyway in Mysql code.

---

^{References:
https://stackoverflow.com/questions/1314521/how-safe-are-pdo-prepared-statements
https://security.stackexchange.com/questions/68701/how-does-stored-procedure-prevents-sql-injection
https://blogs.msdn.microsoft.com/brian_swan/2011/02/16/do-stored-procedures-protect-against-sql-injection/}