| |
 |
|
SQL Server Tips by Burleson |
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 well.
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)
AS
set nocount on
DECLARE @SQL NVARCHAR(4000), @name varchar(50)
SET @SQL='select @uname=username from Table_users WHERE logname='''+@logname+'''
AND userpassword='''+@password+''''
PRINT @SQL
EXECUTE sp_executesql @SQL, N'@uname varchar(50) out', @name out
IF NOT (@name IS NULL)
SELECT 'Welcome '+@name+'!'
ELSE
SELECT 'User not authenticated!'
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', 'a1234'
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 ''''='''
Etc…
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
