Vineet Batta here....
SQL injection attack is the way to manipulate the SQL statement (insert malicious code) from applications to query or execute commands against the database. This can allow an attacker to not only steal data from your database, but also modify and delete it.
It is among the TOP 10 vulnerabilities found in applications space.
1: string ShipOrder = TxtOrder.Text; // Order from the TexTBox
2: string shipDynamicQuery = string.Empty;
3: shipDynamicQuery = "select * from shipOrders where ShipOrder = '" + ShipOrder + "'";
In the above example if the user was to pass shipOrder value from UI (TextBox control)
string shipOrder = "12 ' ; delete from ShipOrders --"; // This is coming as input from text box.
So the final Query that will be build will be
Select * from ShipOrders where ShipOrder = '12' ; Delete from ShipOrders --
The semicolon (;) denotes the end of one query and the start of another. The double hyphen (--) indicates that the rest of the current line is a comment and should be ignored. If the modified code is syntactically correct, it will be executed by the server. When SQL Server processes this statement, SQL Server will first select one record in ShipOrder table where ShipOrder Id is 12. Then, SQL Server will delete all records from ShipOrder table.
Further, real world application want to accept everything as user input to support rich text entry from UI. This affects the ability to filter out potentially dangerous characters like '-',[quote] ,';' etc.
Hence, dynamic SQL query formed using invalidated user inputs are vulnerable to SQL injection attacks.
The common ways to prevent SQL injection are to use parameterized queries or stored procedures. Since we are focusing on stored procedures lets look into into them.
Example : Unsafe way of using stored procedure when executing SQL dynamic queries
1: Create Procedure GetShipOrder(@OrderID varchar(250))
4: declare @sqlDynamicQuery varchar(500)
5: SET @sqlDynamicQuery = 'Select * from where ShipOrders where ShipOrder= ''' + @OrderId + '''''
7: EXEC @sqlDynamicQuery // UNSAFE
Is the above stored procedure vulnerable to SQL injection even though the user inputs are passed to it as parameters? The answer is yes.
Note: If the application is using dynamic SQL statements with EXEC(...) in stored procedure as above, stored procedures offer no protection from SQL injection attacks. If the @OrderID is passed the values as
12 ' ; delete from ShipOrder --; // BAD INPUT
This will try to pull out 1 record from ShipOrder table and then delete all the records from ShipOrder table.
How to code the dynamic SQL in a secure way? You should use sp_executesql statement when executing dynamic queries in stored procedures.
sp_executesql executes a transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters. More information here.
Lets modify the procedure to make it resilient to SQL injection attacks:
Example : Safe way of using stored procedure when executing SQL dynamic queries
4: declare @sqlDynamicQuery nvarchar(500)
5: SET @sqlDynamicQuery = 'Select * from where ShipOrders where ShipOrder= @orderId'
7: EXECUTE sp_executesql @sqlDynamicQuery,N'@orderId varchar(250)',@OrderID
That easy. Isn't it?
More on security vulnerabilities next week...