How to prevent SQL Injection in Stored Procedures

Introduction

Security is the most important attribute for any system.
Providing secure experience is one of the key principles in the process of gaining customer confidence for a system. Nowadays, almost all the websites are asking to store user’s personal information in servers to understand the customer and serve better. It’s the responsibility of an organization to confirm that customer’s data is safe and accessed in a secured manner.

Security in web application is always a big headache for the developer but providing secure environments is one of the key principles in the process of gaining customer confidence for a system. In this era of web applications, almost all websites are dynamic, i.e., database driven and large data will be accepted from user.
SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input. This article explains how SQL Injection is prevented in ASP.NET.


The easiest way to prevent SQL injection from happening, is to use parameters and sp_executesql to execute the dynamically generated search statement.

SQL Injection Attack

For illustration, this is the table and records, we will use for our examples. T-SQL is used in this tip.

CREATE TABLE tbl_Goods
(
Name NVARCHAR(50),
Qty INT,
Price FLOAT
)

GO

INSERT INTO tbl_Goods(Name, Qty, Price) VALUES (N'Shampoo', 200, 10.0);
INSERT INTO tbl_Goods(Name, Qty, Price) VALUES (N'Hair Clay', 400, 20.0);
INSERT INTO tbl_Goods(Name, Qty, Price) VALUES (N'Hair Tonic', 300, 30.0);

This is the stored procedure with dynamic query.

ALTER PROCEDURE sp_GetGoods(@Name NVARCHAR(50))
AS
BEGIN
    DECLARE @sqlcmd NVARCHAR(MAX);
    SET @sqlcmd = N'SELECT * FROM tbl_Goods WHERE Name = ''' + @Name + '''';
 
    EXECUTE(@sqlcmd)
END

If @Name contains malicious string (see below) from the input of the C# program.

Shampoo'; DROP TABLE tbl_Goods; --

The complete query string becomes

SELECT * FROM tbl_Goods WHERE Name = 'Shampoo'; DROP TABLE tbl_Product; --'

The last quote sign is interpreted as comment. The tbl_Goods is dropped. This can be prevented by denying the right to drop table to stored procedure caller.
It is obvious that a direct select (below) would suffice without using dynamic query. I could have used a complex query but the example is kept simple not to distract readers from the key point I am trying to drive home.

ALTER PROCEDURE sp_GetGoods(@Name NVARCHAR(50))
AS
    SELECT * FROM tbl_Goods WHERE Name = @Name; 

Solution 

The solution, as mentioned before, is to use parameters and sp_executesql. The second argument of sp_executesql should be set to the name and type of the parameters to expect in string form.

ALTER PROCEDURE sp_GetGoods(@Name NVARCHAR(50))
AS
BEGIN
    DECLARE @sqlcmd NVARCHAR(MAX);
    DECLARE @params NVARCHAR(MAX);
    SET @sqlcmd = N'SELECT * FROM tbl_Goods WHERE Name = @Name';
    SET @params = N'@Name NVARCHAR(50)';
    EXECUTE sp_executesql @sqlcmd, @params, @Name;
END

Now the SQL injection fails after the sp_GetGoods is altered.