| |
 |
|
SQL Server Tips by Burleson |
Ideas to prevent SQL injection
By doubling single quotes and removing some dangerous characters it
might be possible to deter most SQL injection attacks but there will
be no record of the attacks. It is better to know when and how an
attack occurred and maybe store some info about the attacker,
certainly the IP address. By doubling the single quotes it is
possible to end up with strings longer than the destination field,
truncating the string seems reasonable but it is still vulnerable as
we will examine.
Adding a few lines in “logon.asp”, after reading the input:
StrLogName=replace(StrLogName,
"'", "''")'double single quotes
StrPassword=replace(StrPassword, "'", "''")
StrLogName=left(StrLogName,10)'trunc extra characters
StrPassword=left(StrPassword,10)
Trying one attack:
'OR 1=1-- a
SELECT username from Table_users WHERE logname='''OR 1=1--' AND
userpassword='a'
User not authenticated!
That worked ok. Knowing that the string will be truncated to 10
characters, the attacker might create strings that will contain a
single quote at the end that will be doubled but only one single
quote will remain, afterwards.
This is a possible situation:
123456789' 'OR 1=1--
SELECT username from Table_users WHERE logname='123456789'' AND
userpassword='''OR 1=1--'
Welcome Ed min!
Removing the comment tokens would solve this problem but there are
still workarounds:
123456789' ' or '='
SELECT username from Table_users WHERE logname='123456789'' AND
userpassword=''' or ''=''
Welcome Ed min!
Removing the single quote if there is only one on the rightmost
character wouldn’t work because more than one single quote could be
inserted in the input like this:
123456789' 'or'<''
SELECT username from Table_users WHERE logname='123456789'' AND
userpassword='''or''<''''
To avoid this it is better to verify the length of the input and, if
it is the maximum allowed, then check the rightmost character. If
this character is a single quote, then remove it, if the new
rightmost character is a single quote, remove it too, and so forth.
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 |
