A Very Sleepy MySQL Attack | Hacker News Reader

A Very Sleepy MySQL Attack

blog.cotten.io

70 pointscottenio43 comments

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

Agree, if you are vulnerable to this attack, you are probably vulnerable to a good old ' or '1'; drop databases :D

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.

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.

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

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.

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

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.

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.

This is just not true. SQL injection attacks account for between 7-30% of web application attacks:

https://www.esecurityplanet.com/network-security/most-common...

https://www.ptsecurity.com/ww-en/analytics/web-application-a...

I admit that SQL injection seems to be exploited more than I expect. However, 7-30% doesn't hold for actual vulnerability percentages: just because that there are more attacks doesn't mean that it's actually successfully exploited more.

There are a lot if automated scanners (mostly openvas,sqlmal and the like) that are continually scanning internet targets. Attack does not equate breach of security.

Fair enough. You are right to distinguish between attempted attacks and successful exploitation. To be honest I cannot be bothered to properly read those articles I posted and see if they were talking about attempts or successful attacks. :-)

Though having worked as a penetration tester I can say that, while rare, it was certainly not unheard of for a client's web application to be vulnerable to SQL injection. And this is for clients who are willing to spend several $1000s on a penetration test for their website - imagine what its like for people who don't give a second thought to the security of their site.

It only takes one PHP developer using PDO to read an article like this one to open up SQL injection holes. http://pdo.w3clan.com/tutorial/176/like-clause-in-clause-and...

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

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

There’s a whole class of actions and values that you can’t parameterize, like anything resembling DDL queries. I have worked on several products where these types of queries (like creating tables dynamically) have been an absolute requirement. Sanitizing input is still very much a necessity there.

I'm not sure I see anything in DDLs that denies parameterization of user input, except that support might be currently non-existent; but if the support is missing, it doesn't make sanitization a correct solution... it makes it a necessary hack to work around a lacking communication protocol

Ofc if the user is putting in full custom queries, parameterization doesn't help anything, but I'm not sure what you're even sanitizing at that point (semantics? Afaik sanitization refers to syntax cleanup; It'd be even dumber to avoid people dropping your important tables by parsing a SQL string, instead of making use of the much more reliable DB permission systems...)

It's absurd: A constant source of vulnerability in websites across the world is a syntax error.

It's the definition of accidental complexity -- A program with a valid semantic understanding of what it has is somehow totally incapable of passing on that wisdom except by code generation; and this is when it is free of the burden of any real constraints beyond correctness -- its certainly not a fast method of serializing/deserializing, it certainly doesn't minimize network-size, it certainly isn't a safe or composable API... it's just a dumb, horrible thing and we pretend its all ok because you just need to sanitize your input.

I wouldn't really care if sanitization was just considered a necessary hack, especially in certain corner-cases; but its not. It's thought of as a good thing to do. In fact, a best practice.

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.

Tbh I mostly blame the users

SQL sanitization should never have been recommended to anyone else with a straight face (tears would be appropriate, for the sorry state of the world)

that it became normalized to the extent of becoming a happily provided best practice is horrific

But I doubt anyone even took notice when support properly landed, content in their ways of string-building and worrying about " replaced with \";

All languages nowadays that I'm aware of have support for prepared statements, so "sanitizing inputs" is not an issue anymore and chances of exploits within the major framework are minimal. Of course, SQL is still open to attack if developer doesn't properly understand the risks and uses dynamic composing of SQL before preparing it, doing e.g. something like prepare("UPDATE foo SET {$bar}=:bar") where $bar is insecure. That's a great way to shoot yourself in the foot, and I presume majority of attacks today amount to something like that.

Heh, one of the most common things I see when reviewing code is PDO users realizing they can't "bindValue" or "bindParam" an array of values coming into an IN() clause.

Here's a decent example of doing it right:

https://stackoverflow.com/questions/14767530/php-using-pdo-w...

Here's one showing noobs how to do it wrong:

http://pdo.w3clan.com/tutorial/176/like-clause-in-clause-and...

Look at that blind implode function. Argh. It's the danger of copy/paste echo-chambers.

Yes, I agree. I wasn't clear that injection attacks are easy to prevent with standard DB drivers in every major language that I've used, but there will always be people who make beginner mistakes like string interpolation instead of parametrization. I recently saw it in (someone else's) production code base that's been running since 2016.

Btw, PostgreSQL has pg_sleep()

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-postgre...

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.

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?

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.

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

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.

Even better is to whitelist all SQL than runs on the database using prepared statements. Anything else is a hack.

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?

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

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.

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.

Not quite true - there are permissions around other operations.

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.

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

Yeah, MySQL has recursive queries.

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.

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.

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)

Half this article is about SQL injection attacks.

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.

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.

Reply on news.ycombinator.com