Secure Stored Procedure
Prevent SQL injection attacks against stored procedures
As explained in the article about SQL injection attacks against stored procedures, it is possible to create procedures vulnerable to SQLIA. This article details how you can secure your code against SQL injections. As recommended in previous defense articles, you should use always use parameterized statements. It also apply to your stored procedure code, however input sanitizing is still a valid alternative.
Parameterized Statements
Using prepared statements in stored procedures is by far the best way to protect them against SQL injection. This technique makes your code bullet proof against this security issue and it creates a query plan which can drastically improve performance. Let’s take a look at the secure stored procedure code.
Microsoft SQL Server
Microsoft SQL Server provides a built-in procedure named sp_executesql which can take parameterized variables. For security and performance reasons you should always use sp_executesql instead of EXEC/EXECUTE when working with Transact-SQL.
Secure Microsoft SQL Server stored procedures.
CREATE PROC secure (@v_sp_param VARCHAR(1000))
AS
DECLARE @vsql NVARCHAR(4000)
DECLARE @vparamdefinition NVARCHAR(500)
SET @vsql = N'SELECT description FROM products WHERE name = @val'
SET @vparamdefinition = N'@val VARCHAR(1000)'
EXECUTE sp_executesql @vsql, @vparamdefinition, @val = @v_sp_param
GO
Be careful not to misuse sp_executesql however. If you simply supply it a concatenated string with no parameter definition you will not fix your security problem.
MySQL
Let’s now take a look at the equivalent with MySQL's syntax.
Writing secure MySQL stored procedure.
CREATE PROCEDURE secure (IN param VARCHAR(1000))
BEGIN
PREPARE stmt FROM 'SELECT description FROM products where name=?';
SET @name = param;
EXECUTE stmt USING @name;
DEALLOCATE PREPARE stmt;
END
Here again, this procedure is immunized against SQL injection.
Avoid String Concatenation
Another efficient way to prevent SQL injection is to avoid executing queries stored in strings. Queries integrated as batch commands into a stored procedure are treated like implicit prepared statements. The following example uses Microsoft SQL Server syntax but it could easily be adapted to any DBMS.
Safely using user supply parameter in stored procedures.
CREATE PROCEDURE nostring (@vname varchar(1000))
AS
DELETE FROM products WHERE name = @vname
GO
In the last stored procedure, no SQL injection is possible and when it gets executed only products that exactly match the parameter will be deleted.
Input Sanitizing
The last way would be to secure against SQL injection is to sanitize parameters. Since stored procedures define parameters’ data type it is no longer possible to inject SQL statements in numeric input as described in the tutorial. Only string parameters (VARCHAR, NVARCHAR) will have to be validated. This validation can be done in the API code or within the stored procedure, but the principle remains the same. It is important to keep in mind that input sanitizing is a last resort solution and using parameterized statements is by far a better choice. For more information about this technique, consider reading the article about input sanitizing (available soon).
Reference
If you want more information about creating secure stored procedure, you might be interested by this complete article created by Erland Sommarskog, a SQL Server MVP.
