How to secure stored procedures against SQL injection

Although SQL Server’s stored procedures help with code security by hiding the implementation of the business logic and even protecting against some kinds of SQL injection attacks — primarily those that use an operator such as AND or OR to append commands onto a valid input parameter value, simply wrapping the code into a stored procedure doesn’t mean that applications, database and SQL Server are safe from all types of SQL injection attacks. So, how to make stored procedures bulletproofed against SQL injections?

Let’s consider the following stored procedure:

CREATE PROCEDURE usp_GetEmployee @employeeName varchar(50) = NULL AS
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT EmployeeMiddleName, EmployeeSurname, SSN ' +
              ' FROM HumanResources.Employee ' +
              ' WHERE '
IF @employeeName IS NOT NULL
   SELECT @sql = @sql + ' employeeName LIKE ''' + @employeeName + ''''
EXEC (@sql)

The value for the @employeeName variable is directly taken from whatever the user inputs and concatenated with the contents of the variable @sql. In addition, the EXEC command, which takes a string as a parameter and executes it as a SQL statement, is being used. Therefore, the above stored procedure is still vulnerable to SQL injection even though the user inputs are passed to it as parameters. The reason for this is that the user input is enclosed in the single quotes and concatenated to a string to form a SQL query. Instead of the parameter being a search string to the SQL query, the user input has become the part of the query itself as it is enclosed inside the single quotes.

Therefore, if the user enters:

'1' OR '1'='1';EXEC master.dbo.xp_cmdshell 'dir'--'

then the actual SQL query, executed at the server will be:

SELECT EmployeeMiddleName, EmployeeSurname, SSN
FROM HumanResources.Employee
WHERE employeeName LIKE '1' OR '1'='1';EXEC master.dbo.xp_cmdshell 'dir'--'

which will result in returning all the rows from the table, as well as executing the Windows command DIR.

The fastest way to ensure all stored procedures are safe from SQL injection is to use ApexSQL Refactor.

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in which formats and refactors SQL code using 11 code refactors and over 200 formatting options. It expands wildcards, fully qualifies object names, renames SQL database objects and parameters without breaking dependencies and much more.

To secure stored procedures from SQL injection attacks:

1. Open the stored procedure for editing in either SQL Server Management Studio or Visual Studio
2. Select the body of the stored procedure
3. In the ApexSQL menu, select ApexSQL Refactor

4. Select the Encapsulate code as -> Stored procedure command

   

5. In the Name field type a new name of the stored procedure and specify its schema using the Schema drop-down menu
6. Click the Preview button to preview how the code will be modified:
   

7. Click the Create script button to open generated script in the Query editor
8. Click the Execute button to create new stored procedure

For instance, the unsafe stored procedure that was considered at the beginning of the article will be encapsulated as:

CREATE PROCEDURE dbo.usp_GetEmployeeSafe(@employeeName varchar(50))
AS
BEGIN
DECLARE @sql nvarchar(4000)
SELECT @sql = ' SELECT EmployeeMiddleName, EmployeeSurname, SSN ' +
              ' FROM HumanResources.Employee ' +
              ' WHERE '
IF @employeeName IS NOT NULL
   SELECT @sql = @sql + ' employeeName LIKE ''' + @employeeName + ''''
END
GO
-- Modified source:
EXECUTE dbo.usp_GetEmployeeSafe @employeeName;

This way, the user input is not enclosed inside the single quotes. It is rather being passed as parameter to the SQL statement. In addition, sp_executesql is being used to execute with the parameter list and the parameterized SQL statements. The difference between EXEC and sp_executesql is that the former treats the string parameter as an SQL statement while the latter is a system procedure whose first parameter is a parameterized SQL statement and the second parameter is a parameter-list declaration, similar to the parameter list present in the declaration of a stored procedure and all that remains are simply the parameters in that parameter-list. So, the SQL query built using this method is always the same regardless of how it gets called or the user input supplied at run time. If a malicious user enters something along the lines of:

'1' OR '1'='1'

it will simply be passed as a parameter to the SQL statement and won’t be a part of the SQL statement itself, thus rendering the stored procedure invulnerable to SQL injection attacks.