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