||SQL Server Tips by Gama and Naughter
SQL injection in dynamic SQL
SQL injection is not exclusive to
web applications, any application is vulnerable if SQL code is put
together with user input as part of it and no security measures. An
SP that uses dynamic SQL might be subject to this kind of attack as
The SP validate_user will
authenticate the users by retrieving the user name from the user
table, filtered by login name and password. If the user name is NULL
it means that the input log name and password have no match in the
database. Therefore, the user would not be authenticated.
CREATE PROCEDURE validate_user @logname
varchar(50), @password varchar(20)
set nocount on
DECLARE @SQL NVARCHAR(4000), @name
SET @SQL='select @uname=username
from Table_users WHERE logname='''+@logname+''' AND userpassword='''+@password+''''
EXECUTE sp_executesql @SQL, N'@uname
varchar(50) out', @name out
IF NOT (@name IS NULL)
SELECT 'Welcome '+@name+'!'
SELECT 'User not
The SP will also print the SQL
statement that will run within, so that it will be easier to
understand how the query is modified.
This is a call with the correct
log name and password:
EXEC validate_user 'mike',
All the techniques already
examined are still possible, the only difference is that some single
quotes will have to be doubled:
EXEC validate_user ''' OR 1=1--',
EXEC validate_user ''' OR
''''=''', ''' OR ''''='''
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter