How to extract information using a SQL injection on PHP?

Question

I'm new to SQL injections so please forgive me if this appear a little amateur.

I was working on a website where I managed to bypass authentication and login as a normal user:

username: ' OR 1 -- -
password: <empty>

The resultant page shows "You are logged in as: ' OR 1 -- -". So I figured if I could dump the database details out in the username field it should display.

However when I try to take a step further by enumerating the database version / information , it does not work (as in I could no longer bypass the authentication).

I tried the following:

username: ' OR 1;SELECT @@VERSION -- -
username: ' OR 1;SELECT user_name -- -

The website is running Apache/2.4.7 (Ubuntu) server and I suspect the database is running MySQL.

Answer 1

There is no problem into being new to SQL injection (this one seems quite a classical example and therefore constitute a perfect start :) ), but I think it is very important to have some reliable background on SQL requests: syntax, tables, results sets, what are they and how they are processed, etc.

The goal in SQL injection is indeed to manage to figure out what is going on server's side by twisting the request in every possible way (no holds barrels when the situation deserves it!) and, eventually, gain the ability to actually control server-side processing.

Find an easily exploitable SQL injection

For the sake of illustration, I will reuse the example given in the main answer of "What is SQL injection?". If you need more background information on SQL injection, it might be a good place to start.

I will therefore imagine that the server is executing the following code:

sql = "select id, username from users'
      + ' where username='" + username + "' and password='" + password +"'";

• Upon successful authentication, this request will return a result set similar to this one, with a single row:

  +----+----------+
  | id | username |
  +----+----------+
  | 42 | jdoe     |
  +----+----------+
  

• Failed authentication produce an empty result set, no row:

  +----+----------+
  | id | username |
  +----+----------+
  +----+----------+
  

Now what you already managed to do and which shows that the server-side code is vulnerable is to bypass the authentication by setting the username to ' OR 1 --.

Still taking the same example as above, the code would produce the following SQL request:

select id, username from users where username='' OR 1 --

^{(-- effectively comments out the rest of the request's line which is therefore not shown here)}

Here it's time to understand what this request does: this request fetches the ID and username of every user (every user with an empty username or true, which practically means every user).

This is not a good news for you (unless the first row in the result set happens to be the website administrator, but this is out-of-scope for this post where we want to focus on information extraction).

Why? Here again, you have to try to imagine what may be going on on server-side.

This is not a good news for you because this means that what you see displayed on the "You are logged in" web page:

• Is not extracted from the database query result ($result[0]['username']), as otherwise you would see some random username displayed instead.
• Is instead most likely directly directly taken from the form's field value received by the server (getPostData("username");).

On legitimate use cases, this does not change anything since both should return the same value. In our case this is bad because this means that we will be most probably unable to extract and display database information in this place.

There are more advanced SQL injection methods (error-based SQLi, blind SQLi, ...) which allow you to extract information from the database when the website does not intend to explicitly display it. However, they come with their own set of issues and I will consider them out-of-topic in this post. Indeed, given the care given by the website developer to secure the authentication page, most chances are that other pages are also vulnerable, other pages which this time will display information fetched from the database (the user's profile page may be your best friend :) ).

^{(If some pages are not happy with the fact that your forged username returns several rows instead of a single one, just add a LIMIT statement to you username: ' OR 1 LIMIT 1, 1 --, and of course you are free to modify its parameters to switch from one user to another... If you happen to know some valid username, you may try to use it as well : admin' --.)}

While in this post I will continue to take the authentication page as an example, this will work the very same way on SQL injections affecting any other pages.

Determine the result set layout

At the beginning of this post we assumed that the result set was a two-column table with the username stored in the second column:

+----+----------+
| id | username |
+----+----------+
| 42 | jdoe     |
+----+----------+

However, until know this was just some supposition and we didn't care. Unfortunately we will now have to know this:

• Knowing the exact number of columns is required for technical reasons, otherwise the UNION statement we will use later will not be happy.
• Knowing from which column the server fetches the displayed data will allow us to put the injected value at the right location in the result set (we won't need to know the actual column name, only its position).

^{(If the server happens to use PostgreSQL instead of MySQL, you may even have to determine the correct column types to satisfy UNION, more a nuisance than a real issue but it is something to keep in mind.)}

Determine the number of columns

The easiest way is to ask the server to order the result as below:

username: ' OR 1 ORDER BY 1 -- -

Increment the clause ORDER BY 1 until you get an error, the last working value corresponds to the number of columns in the result set.

If I would run this against the example result set, I would obtain:

• username: ' OR 1 ORDER BY 1 -- -: OK
• username: ' OR 1 ORDER BY 2 -- -: OK
• username: ' OR 1 ORDER BY 3 -- -: error, so the example result set contains two columns.

Determine which column(s) is/are usable

Now that you know the correct number of columns, you can go a step forward and inject a command like this one:

username: ' OR 1 UNION SELECT 1,2 -- -

If you had three columns, you would do SELECT 1,2,3, for five columns SELECT 1,2,3,4,5, etc.

The goal here will be to inject the actual numbers into the result set, and check in the web page (either the rendered one or its source-code) which one is displayed by the server.

The advantage of using numbers is that they are easily casted as string by MySQL if the columns type requires it. While "1,2,3" is given as example here, feel free to use the numbers you like ("1234,2345,3456" would work as well and produce results easier to spot).

In the example, would the resulting web page say "You are logged in as: 2", you would know that the username is fetched from the second column in the result set.

Attention: Understand how the UNION operator works: would the server only use the first row you may need to ensure that the left-side request does not produce any row!

In fact, the UNION operators concatenate two result sets:

+----+----------+         +----+----------+
| 42 | jdoe     |  UNION  | 1  | 2        |
+----+----------+         +----+----------+

Will result in:

+----+----------+
| id | username |
+----+----------+
| 42 | jdoe     |
+----+----------+
| 1  | 2        |
+----+----------+

Which may not be the expected result.

To get:

+----+----------+
| id | username |
+----+----------+
| 1  | 2        |
+----+----------+

You may want to ensure that the left-side query does not return any entry:

username: ' AND 0 UNION SELECT 1,2 -- -

See how OR 1 has been inverted to AND 0 to ensure that the left-side query will be evaluated as false no matter the database rows content.

Exploit the SQL injection

And now the fun begins :) !

With all this information in hand, you are now free to extract the information you want from the database.

For instance:

• To get MySQL version:

  username: ' AND 0 UNION SELECT 1,@@VERSION -- 
  

• To retrieve table and column names:

  username: ' AND 0 UNION SELECT 1,GROUP_CONCAT(table_name,0x2e,column_name) FROM information_schema.columns WHERE table_schema=database() -- 
  

There are a lot of websites out there referencing SQL requests suitable for injection. Pay attention to the version of the MySQL server you have in front of you as some syntax may be version dependent.

Conclusion

SQL injection easiness can vary a lot depending on the details of the server's SQL request and the processing applied to the result. It's all up to your luck factor here.

Nevertheless, manually building a successful SQL injection string always boils down to this loop:

1. Inject a request in the server.
2. Observe the result.
3. Try to interpret this result from the server's perspective: what kind of processing could have lead to such result? (this is were your knowledge about SQL really matters)
4. Create a new injection string designed either to solve your issue or check some hypothesis on server's behavior.
5. Go back to step 1.

At last, doing this manually may not scale very well. Depending on your needs, you may find some tools like sqlmap helpful to automate all this .

Answer 2

Stacked Queries in PHP/MySQL

MySQL + PHP only support stacked queries with multi_query which is rarely used in practice.

That is why your attack doesn't work. You try to add a second query by using ;, which just isn't supported.

You can still extract data with this injection though! There are two options as far as MySQL is concerned: Error based injection and blind injection.

Error Based Injection

First of, as the name suggests, error based injection only works if the MySQL error message is actually displayed.

The idea is simple: If you cannot extract data with your injection, because the results are not displayed, simply create a MySQL error which contains the data you want. This is often done via extractvalue, in your case it would be:

' OR extractvalue(1,version()) -- -

Blind SQL Injection (Content-Based)

In comparison to error based injection, blind injection will always work, even when you do not see error messages.

The idea is again simple. While you cannot display data, you do know if a query did fetch results or if it did not. In your case, you are either logged in (the True state) or not (the False state).

A very simple attack would be:

' OR substring(version(),1,1)='5

This would likely result in a successful login, so you know that the database version starts with a 5. A different value would not result in a login.

The idea now is to retrieve one character at a time.

Instead of asking for the version, you can add more complex queries as well, just remember to LIMIT your result to just one row at a time.

This attack is rather noisy, but can be improved upon. One possibility is to convert the character to its ascii value via the ascii function, which allows you to use less-than and greater-than comparisons.

Blind SQL Injection (Timing-Based)

In some cases, you will not even get a true/false feedback from your injection. In those cases, you can use response times to still ask true/false questions:

' AND if(substring(version(), 1, 1)='5',BENCHMARK(50000000,ENCODE('msg','msg')),null) -- -

The idea here is that some expensive function is called if a specific condition is true, thus resulting in a longer response time.

As before, you can perform more complex queries and also increase the performance by using the ascii function.