6 ms·

A Very Sleepy MySQL Attack

70 points by cottenio 8y ago

hyperman1 8y ago
Isn't there a way to make the database very slow by a complicated but generic query. Thinking about e.g. oracle's connect by + dual If this is the case, removing sleep doesnt help you. Just run a slow query
- bufferoverflow 8y ago
  Yeah, MySQL has recursive queries. https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive https://dev.mysql.com/doc/refman/8.0/en/with.html#common-tab... Though one can set a limit on query run time via max_execution_time.
userbinator 8y ago
why do vanilla MySQL packages come with SLEEP() even enabled? Why not make it an option? Because it's a good "canary" for SQL injection: it doesn't do any real damage, but it's noticeable enough to tell you that you have a possibly vulnerable condition.
- regecks 8y ago
  > it doesn't do any real damage I'm not sure about that. You can perform blind exfiltration of row data by using SLEEP. Handy in situations where you can run a query but can't get it reflected in the server's output.
  - cottenio 8y ago
    This is a valuable point: you can absolutely exfiltrate data this way based on timing, and it's fairly automated with tools at this point.
ishitatsuyuki 8y ago
SQL injection is pretty non-existent these days as frameworks and WAF evolves (assuming a company doing at least minimum of security practices). It doesn't seem there's a point in blocking such attacks at the DBMS side. Given the function has a valid use and there are other ways to do damage to the database system, I don't see it's something that stands out. There are numerous ways for doing database probe or even RCE, thus this is no more than one of them.
- smt88 8y ago
  > assuming a company doing at least minimum of security practices This is not a sound assumption. SQL injection is still pretty common in practice. Even frameworks that try to sanitize input may have a "roll your own" approach that has flaws in it.
  - setr 8y ago
    At some point I realized sanitizing your input for SQL is one of the dumbest ideas I've ever heard: you can trivially avoid the issue by parameterizing the variable inputs, and explicitly telling the DB that this chunk ain't SQL. Sanitizing is an absurd hack in any situation, in the fashion that parsing html with regex is; But especially with generating SQL, any notion of "sanitizing your input" should be considered a bug (perhaps a wetware one)[1] Afaict the only reason this idea ever became widespread was because PHP's native MySQL driver lacked support for it for ages (I guess till PHP5? Been years since I looked into this) [1] Obviously I'm not talking about validation eg validating a phone number; anytime you're trying to embed strings within a program but have to be worry that your string doesn't get read as part of the program, something has gone horribly wrong
    - cookiecaper 8y ago
      MySQL didn't support prepared statements until the end of 2004 with version 4.1. The LAMP craze was well underway by then. While it wouldn't be surprising to hear that it took driver developers some time to catch up to this, it's not really fair to lay the blame at their feet.
js4ever 8y ago
The real problem here is SQL injection... Not sleep
- erazor42 8y ago
  Agree, if you are vulnerable to this attack, you are probably vulnerable to a good old ' or '1'; drop databases :D
  - wongarsu 8y ago
    Yes, but an attacker is more likely to try SLEEP(10) than DROP DATABASES, because the attacker usually wants your data (and your server, but the data is a bonus). So if disabling sleep makes a few bots miss an actual vulnerability, it's a good step for defense in depth.
    - cottenio 8y ago
      Correct. It's more valuable, especially when trying to exfiltrate data or when trying to inject XSS opportunities. Plus, realistically, SLEEP allows you to scan for thousands of different test cases in a quick period and measure the hang time to figure out which vector worked.
- QuinnWilton 8y ago
  This is especially true when you realize that timing attacks don't even need SLEEP in the first place. You just need to hang the database for a measurable amount of time, like this injection will do: AND 1 IN (SELECT BENCHMARK(SOME_MULTIPLIER*15000000,MD5(CHAR(97))))--
amingilani 8y ago
If you take away SLEEP, the attacker will exploit something else during their test. Better SLEEP than really badly customized DROP or DELETE statements to see if their last created resource is affected. Why not monitor for SLEEP execution instead?
- cottenio 8y ago
  These are valid points, but don't necessarily reflect the reality of a production web environment whose user ONLY has SELECT access to read caches and view data from the database. Being able to enumerate vulnerable spots or exfiltrate data can arguably be more devastating than performing a DROP that's noticed immediately and the LKG table from backup is imported to fix it.
  - QuinnWilton 8y ago
    You can perform timing attacks without SLEEP by just writing slow queries. I usually use the BECHMARK command to run a few million MD5 calculations: AND 1 IN (SELECT BENCHMARK(SOME_MULTIPLIER*15000000,MD5(CHAR(97))))--
    - cottenio 8y ago
      One suggestion is to allow further segregation of permissions for functions like SLEEP, BENCHMARK, etc. A front-end request has no need for it. It’s the exposition of things that “act” on lax query permission sets that “appear” read-only (but in fact have interrupt style executions) that leads to trivialization of abuse.
      - JetSpiegel 8y ago
        Even better is to whitelist all SQL than runs on the database using prepared statements. Anything else is a hack.
tzury 8y ago
If you are vulnerable to `sleep()` you are vulnerable to `drop table...` and `select * from users...` . Sleep is not the issue at all.
- sverhagen 8y ago
  A lot of other commands require parameters that the attacker may not know the values for. Those require more knowledge about the system that first needs to be exfiltrated. Not every (hacked) query has its output directly plastered back in the UI, webpage or API response. So that would need more hacking, even if the "sleep" tells the hacker they're on to something. Disabling "sleep" would take away the canary, would it not? Maybe the hacker isn't even interested in dropping tables.
  - PeterisP 8y ago
    Disabling sleep would not take away the canary, it would make it more inconvenient - every instance of sleep can be replaced with some convoluted query that does a very slow calculation.
- kibibu 8y ago
  Not quite true - there are permissions around other operations.
  - cottenio 8y ago
    Yes, thank you - since the most "secure" method of generating UI/UX while still depending on a database at least requires SELECT permission, even if INSERT/DROP/DELETE are enabled, having SLEEP() not require special permissions makes it trivial to use in vulnerability enumeration.
sqldba 8y ago
I don't get it. "This makes discovery very slightly easier (only in the most awful sql-injection code you can imagine)". Why should we care about that?
dbks 8y ago
that is why you put your database behind a firewall and not expose its port to the public internet. There is really no need except for a handful of really edge cases where you have to expose the port to the internet even then you can limit the access to specific hosts only.
- skrebbel 8y ago
  Half this article is about SQL injection attacks.
- wongarsu 8y ago
  The article already assumes you don't have direct access to the database server. Just a backend that legitimately uses the database but creates queries by simply concatenating strings (with some strings being user-supplied, which makes this in practice as good as direct database access once you have guessed how the query is quoted)
- bufferoverflow 8y ago
  That won't help you, because the author assumes an SQL injection is at play. Which is quite a stretch. Who is dumping direct POST/GET data straight into SQL without sanitation these days? Maybe some noobs, but then you have a bigger problem.
  - lowercased 8y ago
    I still see it today, in code being written in 2018. One call say "noob", but... every year there's new people coming in to the field. "Noobs" is an evergreen problem.
pmontra 8y ago
Btw, PostgreSQL has pg_sleep() https://www.postgresql.org/docs/current/functions-datetime.html https://www.postgresql.org/docs/current/functions-datetime.h... SELECT pg_sleep(1.5); SELECT pg_sleep_for('5 minutes'); SELECT pg_sleep_until('tomorrow 03:00'); Maybe useful for debugging? https://www.endpoint.com/blog/2012/11/05/how-to-make-postgresql-query-slow https://www.endpoint.com/blog/2012/11/05/how-to-make-postgre...
- coder543 8y ago
  The article seems to be saying that MySQL's sleep function pauses the entire database for the duration, whereas PostgreSQL's will just sleep that connection for the specified duration and other connections can keep working.

70 points by cottenio 8y ago

Isn't there a way to make the database very slow by a complicated but generic query. Thinking about e.g. oracle's connect by + dual If this is the case, removing sleep doesnt help you. Just run a slow query

bufferoverflow 8y ago
Yeah, MySQL has recursive queries. https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive https://dev.mysql.com/doc/refman/8.0/en/with.html#common-tab... Though one can set a limit on query run time via max_execution_time.

userbinator 8y ago

why do vanilla MySQL packages come with SLEEP() even enabled? Why not make it an option? Because it's a good "canary" for SQL injection: it doesn't do any real damage, but it's noticeable enough to tell you that you have a possibly vulnerable condition.

regecks 8y ago
> it doesn't do any real damage I'm not sure about that. You can perform blind exfiltration of row data by using SLEEP. Handy in situations where you can run a query but can't get it reflected in the server's output.
- cottenio 8y ago
  This is a valuable point: you can absolutely exfiltrate data this way based on timing, and it's fairly automated with tools at this point.

ishitatsuyuki 8y ago

SQL injection is pretty non-existent these days as frameworks and WAF evolves (assuming a company doing at least minimum of security practices). It doesn't seem there's a point in blocking such attacks at the DBMS side. Given the function has a valid use and there are other ways to do damage to the database system, I don't see it's something that stands out. There are numerous ways for doing database probe or even RCE, thus this is no more than one of them.

smt88 8y ago
> assuming a company doing at least minimum of security practices This is not a sound assumption. SQL injection is still pretty common in practice. Even frameworks that try to sanitize input may have a "roll your own" approach that has flaws in it.
- setr 8y ago
  At some point I realized sanitizing your input for SQL is one of the dumbest ideas I've ever heard: you can trivially avoid the issue by parameterizing the variable inputs, and explicitly telling the DB that this chunk ain't SQL. Sanitizing is an absurd hack in any situation, in the fashion that parsing html with regex is; But especially with generating SQL, any notion of "sanitizing your input" should be considered a bug (perhaps a wetware one)[1] Afaict the only reason this idea ever became widespread was because PHP's native MySQL driver lacked support for it for ages (I guess till PHP5? Been years since I looked into this) [1] Obviously I'm not talking about validation eg validating a phone number; anytime you're trying to embed strings within a program but have to be worry that your string doesn't get read as part of the program, something has gone horribly wrong
  - cookiecaper 8y ago
    MySQL didn't support prepared statements until the end of 2004 with version 4.1. The LAMP craze was well underway by then. While it wouldn't be surprising to hear that it took driver developers some time to catch up to this, it's not really fair to lay the blame at their feet.

js4ever 8y ago

The real problem here is SQL injection... Not sleep

erazor42 8y ago
Agree, if you are vulnerable to this attack, you are probably vulnerable to a good old ' or '1'; drop databases :D
- wongarsu 8y ago
  Yes, but an attacker is more likely to try SLEEP(10) than DROP DATABASES, because the attacker usually wants your data (and your server, but the data is a bonus). So if disabling sleep makes a few bots miss an actual vulnerability, it's a good step for defense in depth.
  - cottenio 8y ago
    Correct. It's more valuable, especially when trying to exfiltrate data or when trying to inject XSS opportunities. Plus, realistically, SLEEP allows you to scan for thousands of different test cases in a quick period and measure the hang time to figure out which vector worked.
QuinnWilton 8y ago
This is especially true when you realize that timing attacks don't even need SLEEP in the first place. You just need to hang the database for a measurable amount of time, like this injection will do: AND 1 IN (SELECT BENCHMARK(SOME_MULTIPLIER*15000000,MD5(CHAR(97))))--

amingilani 8y ago

If you take away SLEEP, the attacker will exploit something else during their test. Better SLEEP than really badly customized DROP or DELETE statements to see if their last created resource is affected. Why not monitor for SLEEP execution instead?

cottenio 8y ago
These are valid points, but don't necessarily reflect the reality of a production web environment whose user ONLY has SELECT access to read caches and view data from the database. Being able to enumerate vulnerable spots or exfiltrate data can arguably be more devastating than performing a DROP that's noticed immediately and the LKG table from backup is imported to fix it.
- QuinnWilton 8y ago
  You can perform timing attacks without SLEEP by just writing slow queries. I usually use the BECHMARK command to run a few million MD5 calculations: AND 1 IN (SELECT BENCHMARK(SOME_MULTIPLIER*15000000,MD5(CHAR(97))))--
  - cottenio 8y ago
    One suggestion is to allow further segregation of permissions for functions like SLEEP, BENCHMARK, etc. A front-end request has no need for it. It’s the exposition of things that “act” on lax query permission sets that “appear” read-only (but in fact have interrupt style executions) that leads to trivialization of abuse.
    - JetSpiegel 8y ago
      Even better is to whitelist all SQL than runs on the database using prepared statements. Anything else is a hack.

tzury 8y ago

If you are vulnerable to `sleep()` you are vulnerable to `drop table...` and `select * from users...` . Sleep is not the issue at all.

sverhagen 8y ago
A lot of other commands require parameters that the attacker may not know the values for. Those require more knowledge about the system that first needs to be exfiltrated. Not every (hacked) query has its output directly plastered back in the UI, webpage or API response. So that would need more hacking, even if the "sleep" tells the hacker they're on to something. Disabling "sleep" would take away the canary, would it not? Maybe the hacker isn't even interested in dropping tables.
- PeterisP 8y ago
  Disabling sleep would not take away the canary, it would make it more inconvenient - every instance of sleep can be replaced with some convoluted query that does a very slow calculation.
kibibu 8y ago
Not quite true - there are permissions around other operations.
- cottenio 8y ago
  Yes, thank you - since the most "secure" method of generating UI/UX while still depending on a database at least requires SELECT permission, even if INSERT/DROP/DELETE are enabled, having SLEEP() not require special permissions makes it trivial to use in vulnerability enumeration.

sqldba 8y ago

I don't get it. "This makes discovery very slightly easier (only in the most awful sql-injection code you can imagine)". Why should we care about that?

dbks 8y ago

that is why you put your database behind a firewall and not expose its port to the public internet. There is really no need except for a handful of really edge cases where you have to expose the port to the internet even then you can limit the access to specific hosts only.

skrebbel 8y ago
Half this article is about SQL injection attacks.
wongarsu 8y ago
The article already assumes you don't have direct access to the database server. Just a backend that legitimately uses the database but creates queries by simply concatenating strings (with some strings being user-supplied, which makes this in practice as good as direct database access once you have guessed how the query is quoted)
bufferoverflow 8y ago
That won't help you, because the author assumes an SQL injection is at play. Which is quite a stretch. Who is dumping direct POST/GET data straight into SQL without sanitation these days? Maybe some noobs, but then you have a bigger problem.
- lowercased 8y ago
  I still see it today, in code being written in 2018. One call say "noob", but... every year there's new people coming in to the field. "Noobs" is an evergreen problem.

pmontra 8y ago

Btw, PostgreSQL has pg_sleep() https://www.postgresql.org/docs/current/functions-datetime.html https://www.postgresql.org/docs/current/functions-datetime.h... SELECT pg_sleep(1.5); SELECT pg_sleep_for('5 minutes'); SELECT pg_sleep_until('tomorrow 03:00'); Maybe useful for debugging? https://www.endpoint.com/blog/2012/11/05/how-to-make-postgresql-query-slow https://www.endpoint.com/blog/2012/11/05/how-to-make-postgre...

coder543 8y ago
The article seems to be saying that MySQL's sleep function pauses the entire database for the duration, whereas PostgreSQL's will just sleep that connection for the specified duration and other connections can keep working.