SQL Injection in Stored Procedures cos of Dynamic SQL

Dynamic SQL in Stored Procedures

In some scenarios, it becomes unavoidable to use dynamic SQL in stored procedures. It is not bad at all; it depends on how we form that Dynamic SQL query. As a rule of thumb, it is better to use SQL parameters as replaceable values in dynamic queries instead of concatenation

Let’s dive in a bit:

What is Dynamic SQL?

It is nothing but when you form SQL queries inside a proc and execute them using execute OR exec OR sp_executesql stored procedure.

Example:

CREATE PROCEDURE DynamicSelectProcedure

AS

BEGIN

declare @sql nvarchar(max)

set @sql = concat(N'SELECT * from Person where SSID = ', @testvalue)

execute sp_executesql @sql

END

GO

Above example is not a real-time example for dynamic SQL which could have been easily replaced with simple select with parameter passed to it.

More practical example would be a simple search based on user input. Consider procedure accepts xml or complete search string with all user selected search criteria. SQL query is formed by parsing search criteria xml or search string and executed in procedure. This is a typical example where user has a way to influence the query execution. His search criteria selection will change the dynamic SQL which procedure creates and executes. Everything looks and ola! search works as expected when user inputs like a good caring programmer.

That little room for Injection -SQL injection or SQLi

If you read through above paragraph again, we just gave a little space for a SQL injection attack. As all good books say, using Stored procedures with arguments unarguably avoids SQL injection attacks than executing SQL commands formed from UI. However, in above case we formed a SQL query inside a stored procedure is using user input. A little hacker might just want to play with your search and try to get more information or even do nasty things.

Back to little stored procedure we wrote to select a person based on SSID. We added a vulnerability by appending user input to the query we are forming inside stored procedure. This user input can be formed in a way to attack our database. Just try below user input for test value.

'''4980222'';select top 100 * from IncomeDetails;'

Attacker got for more information (must say sensitive information) than what procedure was supposed to return. Without proper access policies on stored procedure, attacker might be even able to do disastrous things to our database. See below input for stored procedure parameter,

'''4980222'';drop table IncomeDetails;'

Attacker could drop a table of high importance for the system and that’s not even the end!

What did we do wrong here?

“Forgetting the basics”

1. We total forgot parameters concept. By concatenating parameter value to SQL string, we are allowing actual query to be modified anytime dynamically. A parameter value with a query will simply get appended to existing query and hence becomes part of query execution.

When SQL uses parameters, it is considered safe.

Parameters values are not dumbly replaced wherever they’re used. Sql query optimizer will create a query execution plan with parameter as a replaceable value inside the existing query. Parameter value should not contain anything which will change “query estimation\execution plan” i.e. if value contains any dangerous SQL query optimizer identifies it. It cross-checks whether query optimized plan still sticks to original (pre-compiled with only replaced parameter value). This saves us from SQL injection through parameter values.

We can change vulnerable statement of our SP to use parameters like below:

declare @sql nvarchar(max)

   set @sql = 'SELECT * from Person where SSID = @ssid'

   execute sp_executesql @sql, N'@ssid varchar(100)', @ssid=@testValue

        or

Even predefined statement like below. In this case statement is already compiled with only parameter value as variable.

SELECT * from Person where SSID = @testvalue 

Lesson learnt:

Don’t concatenate user input with SQL. Use statements with parameters or pre-created sql statements.

2. Grant Permissions: Every procedure should be associated with correct permissions. In our above procedure, our intention was only to select the data from some table. We can easily avoid any dangerous query like drop, delete or update by controlling user or role based access. We can use or create a role which has only select permission on tables and grant that role with stored procedure execute permissions.

Permission can be granted to a role or user from SQL management studio by selecting Stored Procedures -> Properties -> Permissions. Better way would be to track it through SQL Grant permission command after Create SP code. This SQL script can be reused after dropping and recreating stored procedures with same permissions.

Example:

GRANT EXECUTE ON <<Stored Procedure>>

       TO DBUSER_Reader_Person;

GO