SQL Injection in Stored Procedures via MySQL Connector

Question

I use the mysql-connector-python driver for executing database operations. Recently, I came across stored procedures in MySQL and decided to migrate some of my APIs from utilizing cursor.execute() to cursor.callproc(proc_name, args=()). This transition has proven successful, and everything works seamlessly. However, I'm uncertain about the vulnerability of these stored procedures to SQL injection.

To assess this, I created a test stored procedure and examined its susceptibility to SQL injection payloads.

Stored Procedure:

DELIMITER //
CREATE PROCEDURE TestProcedure(IN arg_test VARCHAR(150))
  BEGIN
    IF EXISTS (SELECT 1 FROM Random_Table WHERE test = arg_test) THEN
        SELECT 'success' AS message;
    ELSE
        SELECT 'failed' AS message;
    END IF;
  END //
DELIMITER ;

Payloads:

• ' or 1=1 --
• " or 1=1 --

Surprisingly, none of these payloads yielded any successful results. Seeking further assurance, I consulted one of the database administrators in the company. Although he expressed uncertainty, he suggested that these stored procedures function similarly to prepared statements. This aligns with my observation that the cursor.callproc() method accepts user inputs as arguments via the args parameter, resembling prepared statements.

If this interpretation is accurate, it implies that using the cursor.callproc() method ensures a secure backend, free from SQL injection concerns. Despite these positive indications, I would like to seek additional confirmation here to guarantee the safety of this approach.

Answer 1

Like it often happens, the problem is in the terminology. And it creates a lot of trouble (and vulnerabilities). A programmer should be always strict in their definitions.

What you are talking about here is using stored procedure's parameter. Such a parameter is a special case related to stored procedures, and very similar in nature to SQL variables.

In this case your database doesn't add the contents of arg_test into the SQL body (as it would have been in python if you did something like + arg_test +) and then evaluate the resulting SQL. On the contrary, is uses arg_test as though it's a variable (in this sense, it's indeed similar to prepared statements, but technically it's entirely different matters).

While speaking of stored procedures, they are irrelevant to injections. By itself, just using a procedure doesn't guarantee anything. One can write a procedure which is immune to injection, and write another which is prone to injection. Just like any other code.

But as long as you are just using a parameter passed to the procedure - it's safe.

Answer 2

If this interpretation is accurate, it implies that using the cursor.callproc() method ensures a secure backend, free from SQL injection concerns.

Simply using cursor.callproc() does not by itself ensure that the procedure is free from SQL injection risks. It depends on the code in the body of the procedure.

SQL injection vulnerabilities are caused when untrusted payload is introduced to the query before it is parsed, so the content in that payload can affect the syntax of the query.

But using procedure parameters does not interpolate the value of the parameter into the SQL syntax. Syntactically, it works exactly as if you had used a string literal, like this:

IF EXISTS (SELECT 1 FROM Random_Table WHERE test = 'my test') THEN
...

Using a parameter in place of the string literal cannot affect the syntax of the query. The syntax is fixed when you created the procedure. The parameter can only behave as a single scalar value.

The same is true of local variables and user-defined variables in a stored procedure.

Is it possible to design a stored procedure that has an SQL injection vulnerability? Yes, it's possible, but your procedure isn't doing that.

You'd have to write a new query as a string, interpolate the parameter into that string, and then use PREPARE and EXECUTE to run the string as a query. This way the syntax parsing happens after you have combined the untrusted payload, and the payload can affect the syntax of the query. After the string has been formed with concatenation, MySQL isn't aware of which characters in the query were part of the hard-coded query and which characters came from the parameter.

An example of an unsafe query might look like this:

CREATE PROCEDURE TestProcedure(IN arg_test VARCHAR(150))
  BEGIN
    SET @sql = CONCAT('SELECT 1 INTO @result FROM Random_table WHERE test = ''',
        arg_test, '''');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    IF @result = 1 THEN
    ...

This is a contrived example, of course. I'm making it similar to your procedure as a demonstration.

There are cases where using PREPARE & EXECUTE in a procedure is necessary, and in those cases we should not use string concatenation to add variables to the query. We can pass arguments to EXECUTE instead. For historical reasons, they must be user-defined variables, not local variables or procedure parameter variables, so we'd just have to copy payload to a user-defined variable.

CREATE PROCEDURE TestProcedure(IN arg_test VARCHAR(150))
  BEGIN
    SET @sql = 'SELECT 1 INTO @result FROM Random_table WHERE test = ?';
    PREPARE stmt FROM @sql;
    SET @param = arg_test;
    EXECUTE stmt USING @param;
    DEALLOCATE PREPARE stmt;
    IF @result = 1 THEN
    ...

The ? placeholder is a fixed syntactic element. It behaves only as a single scalar value, no matter what the content of the @param variable is. The query is parsed during PREPARE, before the variable has been combined with the query. The prepared query notes that there's a placeholder, and each placeholder can only be a single scalar value. So there's no way the variable, passed to the query during EXECUTE, can change the query syntax. It only fits into the place held for it at the last moment during execution.

Understanding this difference between query preparation and execution is crucial for identifying SQL injection risks.