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’
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’