SQL Prepared Statement   Leave a comment

SQL prepared statement is one of the way to prevent from the SQL injection.

SQL injection is basically when someone try to insert the SQL command using the data channel in control channel.

Let’s understand these two channel using simple SQL query, let’s consider “Select * From UserMaster Where UserName = ‘abc'”;

So, in above SQL “Select * From UserMaster Where UserName=” is the control channel and “abc” is the data channel. So, from this we can say that control channel is nothing but the code and data channel is nothing but the data, in our case it is “abc”.

In case of SQL injection what attackers do is they add the Unexpected Input to the query using the data channel.

For example, instead of passing “abc” as data they try to pass data like “abc’; delete from UserMaster–” in this case what will happen is when query get executed in database it will delete the data from UserMaster table as well. If you use the SQL prepared statement or Parameterized SQL statement then it will treat the complete value as data for parameter.

Sample SQL statements:

Create Table SQLPreparedStatement (Id Int Identity(1,1), Name varchar(100))
Insert Into SQLPreparedStatement Values (‘abc’)
Insert Into SQLPreparedStatement Values (‘pqr’)
Insert Into SQLPreparedStatement Values (‘xyz’)
Select * From SQLPreparedStatement

Declare @SQL nvarchar(max)
Set @SQL = ‘Select * From SQLPreparedStatement Where Name = @Name’
–Proper Statement
exec sp_executesql @SQL, @Parameters = N’@Name varchar(100)’, @Name = ‘abc’
–SQL injected statement 1
exec sp_executesql @SQL, @Parameters = N’@Name varchar(100)’, @Name = ‘abc Or 1 = 1′
–SQL injected statement 2
exec sp_executesql @SQL, @Parameters = N’@Name varchar(100)’, @Name = ‘abc; Drop Table SQLPreparedStatement’


Posted September 26, 2014 by Izharuddin Shaikh in SQL

Tagged with , ,

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: