Click here to Skip to main content
13,763,800 members
Click here to Skip to main content
Add your own
alternative version

Stats

69.8K views
408 downloads
17 bookmarked
Posted 12 Apr 2016
Licenced CPOL

How to prevent SQL Injection in Stored Procedures

, 12 Apr 2016
Rate this:
Please Sign up or sign in to vote.
How to prevent SQL injection attacks when using dynamic SQL in stored procedures

Introduction

Some database programmers believe that by using stored procedures, their code are safe from SQL injection attacks. That is not entirely true if dynamic query is used inside the stored procedures and the dynamic query is constructed by concatenating the parameters. In circumstances, where the complicated query may use one, many, all or none of the parameters, it warrants the use of dynamic query. 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_Product
(
Name NVARCHAR(50),
Qty INT,
Price FLOAT
)

GO

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

This is the stored procedure with dynamic query.

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

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

Shampoo'; DROP TABLE tbl_Product; --

The complete query string becomes

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

The last quote sign is interpreted as comment. The tbl_Product 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_GetProduct(@Name NVARCHAR(50))
AS
    SELECT * FROM tbl_Product 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_GetProduct(@Name NVARCHAR(50))
AS
BEGIN
    DECLARE @sqlcmd NVARCHAR(MAX);
    DECLARE @params NVARCHAR(MAX);
    SET @sqlcmd = N'SELECT * FROM tbl_Product WHERE Name = @Name';
    SET @params = N'@Name NVARCHAR(50)';
    EXECUTE sp_executesql @sqlcmd, @params, @Name;
END

Now the SQL injection fails after the sp_GetProduct is altered.

The C# calling code is not shown. Interested reader may download the VS2008 source code and T-SQL script.

Reference Books

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Cake Processor
Software Developer (Senior)
Singapore Singapore
Right now, I am picking up DevOps skills at Pluralsight and pursuing CCNA certification. Stay tuned for my CCNA related article!

Coding Tidbit Blog

Latest blogpost: C++ – The Forgotten Trojan Horse by Eric Johnson

IT Certifications

  • IT Infrastructure Library Foundational (ITIL v3)
  • Scrum Alliance Certified Scrum Master (CSM)
  • Certified Secure Software Lifecycle Professional (CSSLP)

View my certificates here.

You may also be interested in...

Comments and Discussions

 
QuestionDoesn't prevent by Injection : EXECUTE sp_executesql @sqlcmd, @params, @Name; Pin
Amit kumar pathak14-Jun-16 22:41
memberAmit kumar pathak14-Jun-16 22:41 
GeneralMy vote of 4 Pin
thund3rstruck13-Apr-16 3:05
memberthund3rstruck13-Apr-16 3:05 
Questionmy Vote 5 Pin
King Fisher25-Dec-14 19:30
professionalKing Fisher25-Dec-14 19:30 
QuestionPrevent SQL Injection Pin
World Traveler21-May-13 22:50
memberWorld Traveler21-May-13 22:50 
GeneralMy vote of 5 Pin
Carsten V2.05-May-13 9:05
memberCarsten V2.05-May-13 9:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.181113.4 | Last Updated 12 Apr 2016
Article Copyright 2016 by Cake Processor
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid