Vineet Batta here....

SQL Injection explained :

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.

Example:

   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.

Dynamic SQL Query in Stored procedures :

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))
   2: AS
   3: BEGIN
   4: declare @sqlDynamicQuery varchar(500)
   5: SET @sqlDynamicQuery = 'Select * from where ShipOrders where ShipOrder= ''' + @OrderId + '''''
   6:  
   7: EXEC @sqlDynamicQuery // UNSAFE
   8: END

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 can we fix?

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 

   1: Create Procedure GetShipOrder(@OrderID varchar(250))
   2: AS
   3: BEGIN
   4: declare @sqlDynamicQuery nvarchar(500)
   5: SET @sqlDynamicQuery = 'Select * from where ShipOrders where ShipOrder= @orderId'
   6:  
   7: EXECUTE sp_executesql @sqlDynamicQuery,N'@orderId varchar(250)',@OrderID 
   8: END

That easy. Isn't it?

Inference :

  1. Stored procedures do offer protection against SQL injection but only if the stored procedure does not include dynamic SQL query. Other wise, they are as vulnerable as plain dynamic SQL queries when  EXEC(...) SQL statement is used to run the SQL query.
  2. Avoid dynamic SQL queries in stored procedure. But in real world some times its just not possible.
  3. As a good practice always use sp_execute when executing dynamic queries in stored procedure.
  4. Because the actual text of the Transact-SQL statement in the sp_executesql string does not change between executions, the query optimizer will probably match the Transact-SQL statement in the second execution with the execution plan generated for the first execution. Therefore, SQL Server does not have to compile the second statement. So the performance benefit of using it.
  5. Use least privilege account to run stored procedures.

More on security vulnerabilities next week...