Home > SQL, SQL Server2008 > Dynamic Queries, Stored Procedures and SQL Injections

Dynamic Queries, Stored Procedures and SQL Injections


As every one knows that Ad hoc dynamic queries are prone to SQL Injection attacks, I am not going to touch that. But there is still some confusion hanging over usage of dynamic sql with in a stored procedure. This is what I thought of blogging about.

Point 1: Using dynamic SQL with in stored procedure are prone to SQL Injection attack.

Other disadvantages of using dynamic SQL includes:

  1. Not readable and there for un maintainable code.
  2. Execution path is not saved there fore every time a stored procedure is run execution path is calculated again and again.
    But there are cases when we might need to use dynamic queries inside a stored procedure. What have to be done in this case?
    To demonstrate the sql injection attacks and to give a sample how to avoid this, I created a table named test with just one column [name].

    Table Definition

    USE [ASPNETDEV]
    GO
    /****** Object:  Table [dbo].[test1]    Script Date: 10/28/2009 15:14:07 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[test](
        [name] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]

    Insert the below values in to the table.

    insert into test values (‘muthu’);

    insert into test values (‘muthu1’);

Case 1: Procedure using static query

create procedure testsi
(@name nvarchar(10))
as
select * from test where [name]=@name

when we execute the above procedure with normal parameters (‘muthu’) it brings just 1 row.

exec testsi ‘muthu’

Now I give a value that introduces SQL injection as below

exec testsi ‘muthu”;drop table test;–”select * from test;’

When you look in to the value passed to the parameter @name ; you can very well see the SQL injection in the form of ‘’;drop table test;—. As you see, this  just closes the single quote and drops the table test. Well this is sql injection.

But to our surprise executing this does not drop the table and promptly brings in one row.

muthu

Because what we passed is just a value for the column [name] and obviously we don’t have any row in the table [test] with the column [name] having value ‘muthu”;drop table test;–”select * from test;’

Case 2: Procedure with dynamic Query 

create procedure testsid
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
set @sql=’select * from test where [name]=”’ + @name + ””
print @sql
execute (@sql)

The line given in bold is the place where we use dynamic query.

Now lets execute this procedure using our SQL Injection value.

exec testsid ‘muthu”;drop table test;–”select * from test;’

Opps! Now the table is lost.

when you see the “Messages” tab in the Management Studio to your surprise it will be as follows:

select * from test where [name]=’muthu’;drop table test;–‘select * from test;’

(1 row(s) affected)

When you separate the statement using semicolon you will get 2 statements as follows; never mind the third one is commented.

  1. select * from test where [name]=’muthu’;
  2. drop table test;
  3. –‘select * from test;’

So this is SQL injection and this doesn’t just disappear if you use stored procedure.

Case 3: Procedure with dynamic query and avoiding SQL injection

However Microsoft has introduced a new way to run dynamic queries from the stored procedure using sp_executesql.

From MSDN:

To execute a string, we recommend that you use the sp_executesql stored procedure instead of the EXECUTE statement. Because this stored procedure supports parameter substitution, sp_executesql is more versatile than EXECUTE; and because sp_executesql generates execution plans that are more likely to be reused by SQL Server, sp_executesql is more efficient than EXECUTE.

 

Please refer to the SQL server 2008 books online to get more information about this sp_executesql.

alter procedure testside
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
declare @ParamDefinition nvarchar(500)
set @ParamDefinition = N’@name nvarchar(1000)’
set @sql=’select * from test where [name]=@name’

exec sp_executesql @sql, @ParamDefinition,@name

print @sql

In the above procedure we create a dynamic parameterized  query and we pass the query, the parameter definition and the value for the parameter to sp_excutesql procedure.

exec testside ‘muthu”;drop table test;–”select * from test;’

Even though we run the procedure with SQL Injection values to our surprise the table test does not get dropped.

But remember just using sp_executesql will not avoid sql injection attacks. It must be used sensibly. For example an example as follows is still susceptible to sql injection.

How not to use sp_executesql?

create procedure testsides
(@name nvarchar(1000))
as
declare @sql as nvarchar(1000)
declare @ParamDefinition nvarchar(500)
set @ParamDefinition = N’@name nvarchar(1000)’
set @sql=’select * from test where [name]=”’ + @name + ””
exec sp_executesql @sql, @ParamDefinition,@name

print @sql

Even though the procedure uses “sp_executesql” it is still prone to SQL injection because it does not use parameterized query.

But there are times when one cannot use parameterized queries, in this case there is no way but to use dynamic query. But in this case one must take extra-ordinary steps to validate the data. This article may help further understanding.

Update:

A live example for sql injection attack: Barackobama.com!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: