SQL injection in Stored Procedures , especially when calling a Procedure by PDO of PHP ?
DELIMITER //
CREATE PROCEDURE
procedureTeste( string VARCHAR(255) )
BEGIN
SELECT * FROM produtos WHERE nome = string;
END
//
DELIMITER ;
Could it be possible to perform SQL Injection in a Procedure using PDO ?
$PDO = $this->PDO->prepare('call procedureTeste(:nomeProd)');
$PDO->bindParam(':nomeProd', $nome, PDO::PARAM_STR);
$PDO->execute();
Is it possible to perform sql injection in stored procedures?
It depends on how you create these procedures , however it is not safe to let the user enter any value. This is because the user can type very long text and end up having access to error messages (if they are enabled and not filtered). This will expose your data structure and how you work with it.
And of course, it will know that you do not handle incoming and outgoing data.
Structure of tables:
+-------------+--------------+------+-----+---------+----------------+
| 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 vunerable:
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 unsustainable :
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, MySQL will not return the records.
CALL login2("' OR '1' = '1", "' OR '1' = '1");
This occurs because the second way Stored Procedures is precompiled. In other words, the database creates this internal software before using it. In this case, only the program code is interpreted without any influence of parameters.
Would it be possible to perform SQL Injection on a procedure with%
The advantage is that the value of the parameter (with bindParam ) is never interpolated in the query string.
So yes, the query parameters help you avoid this security vulnerability. However, it is necessary to be aware and not to do it in the MySQL code anyway.