Dynamic SQL

Execute SQL statement provided as string

In some cases, you would need to execute SQL query placed in string. EXEC, EXECUTE, or system procedure sp_executesql can execute any SQL query provided as string:

sp_executesql N'SELECT * FROM sys.objects'
-- or
sp_executesql @stmt = N'SELECT * FROM sys.objects'
-- or
EXEC sp_executesql N'SELECT * FROM sys.objects'
-- or
EXEC('SELECT * FROM sys.columns')
-- or
EXECUTE('SELECT * FROM sys.tables')

This procedure will return the same result-set as SQL query provided as statement text. sp_executesql can execute SQL query provided as string literal, variable/parameter, or even expression:

declare @table nvarchar(40) = N'product items'
EXEC(N'SELECT * FROM ' + @table)
declare @sql nvarchar(40) = N'SELECT * FROM ' + QUOTENAME(@table);
EXEC sp_executesql @sql

You need QUOTENAME function to escape special characters in @table variable. Without this function you would get syntax error if @table variable contains something like spaces, brackets, or any other special character.

Dynamic SQL executed as different user

You can execute SQL query as different user using AS USER = ‘name of database user’

EXEC(N'SELECT * FROM product') AS USER = 'dbo'

SQL query will be executed under dbo database user. All permission checks applicable to dbo user will be checked on SQL query.

SQL Injection with dynamic SQL

Dynamic queries are

SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = ''' + @user + ''' AND Password = ''' + @pass + ''''
EXEC(@sql)

If value of user variable is myusername” OR 1=1 — the following query will be executed:

SELECT COUNT(*)
FROM AppUsers 
WHERE Username = 'myusername' OR 1=1 --' AND Password = ''

Comment at the end of value of variable @username will comment-out trailing part of the query and condition 1=1 will be evaluated. Application that checks it there at least one user returned by this query will return count greater than 0 and login will succeed.

Using this approach attacker can login into application even if he don’t know valid username and password.

Dynamic SQL with parameters

In order to avoid injection and escaping problems, dynamic SQL queries should be executed with parameters, e.g.:

SET @sql = N'SELECT COUNT(*) FROM AppUsers WHERE Username = @user AND Password = @pass
EXEC sp_executesql @sql, '@user nvarchar(50), @pass nvarchar(50)', @username, @password

Second parameter is a list of parameters used in query with their types, after this list are provided variables that will be used as parameter values.

sp_executesql will escape special characters and execute sql query.