Stored Procedure Attacks
Understand SQL injection attacks against stored procedures and functions
It is often believed that stored procedures are not vulnerable to SQL injection attacks, but the reality is totally different. In fact, stored procedures and PL/SQL can be vulnerable to SQLIA. This article covers the different situations where stored procedures can be exploited. If you are looking to secure you code against this security flaw, consider reading the article about writing secure stored procedures.
Vulnerable Code
Many database management systems support user defined stored procedures. Even though the syntax may slightly vary from a DBMS to another, the principle stays the same. When a stored procedure integrates user supplied input into a dynamically built query, we encounter exactly the same SQL injection vulnerability we presented in the tutorial. There is basically two ways to create a vulnerable stored procedure. Microsoft SQL Server syntax is used in examples, but you could adapt it could be converted to almost any DBMS syntax with minimal changes. Let’s take a look at the code behind those security flaws.
Vulnerable EXEC statement
The most common mistake with stored procedures is to misuse the EXEC statement. If the user input was not sanitized before integrating it in the query, a SQL injection is created.
Vulnerable stored procedure using EXEC statement.
CREATE PROCEDURE getDescription
@vname VARCHAR(50)
AS
EXEC('SELECT description FROM products WHERE name = '''+@vname+ '''')
RETURN
As you can guess, the previous example will execute any correctly crafted user input as if it was part of the query.
Vulnerable Dynamic Cursors
Dynamic cursors are not as frequently used as EXEC statements in stored procedures. However, that does not make them less vulnerable. In fact, it might represent a little advantage for the attacker since security flaws in dynamic cursors are less likely to be identified in a code review (article available soon). Let’s take a look at an insecure use of dynamic cursors.
Vulnerable stored procedure using dynamic cursor.
CREATE PROCEDURE printDescriptions
@vname VARCHAR(100)
AS
DECLARE @vdesc VARCHAR(1000)
DECLARE @vsql VARCHAR(4000)
SET @vsql = 'SELECT description FROM products WHERE name='''+@vname+''''
DECLARE cur CURSOR FOR EXEC @vsql
OPEN cur
FETCH NEXT FROM cur INTO @vdesc
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @vdesc
FETCH NEXT FROM cur INTO @vdesc
END
CLOSE cur
DEALLOCATE cur
RETURN
Here again, the user input is integrated directly in the query. Let’s now see how the stored procedure could be manipulated by the attacker.
Attacking Stored Procedures
In both cases the attack is quite straight forward. There is nothing fancy to do in order to attack stored procedures and everything goes like a classic SQLIA. Below is an example that could be applied to successfully attack vulnerable procedures presented earlier in this article.
Malicious user input.
z' AND 'a'='b' UNION SELECT password FROM members WHERE username='admin
Query generated in both cases.
SELECT description FROM products WHERE name ='z' AND 'a'='b' UNION SELECT password FROM members WHERE username='admin'
When the stored procedure is executed, it returns the password of the administrator.
Limitations
There is no specific limitation when attacking stored procedures, although you need to keep in mind that the injected code will be limited to the stored procedure permissions (available soon). For example, the attack will be voided if you attempt to inject an INSERT statement in a stored procedure which has only read acces. In addition, DBMS/API specific limitations (available soon) are still taking effect.
Advanced Attacks
Many advanced attacks such as privilege escalation (available soon), SQL injection buffer overflow (available soon) and advanced information gathering (available soon) rely on stored procedures. Those are not covered here since they habitually only apply to a specific (unpatched) version of database management system. For more information about those advanced techniques (available soon), refer to mentioned articles.
