12 ms·

Scientific notation bug in MySQL left AWS WAF vulnerable to SQL injection

124 points by fipar 5y ago

RNCTX 5y ago
Reminiscent of the talk in which “SELECT 1 / 0” was offered as reason enough in one line of code to not use MySQL.
- n3t 5y ago
  What talk are you referring to?
  - RNCTX 5y ago
    https://vimeo.com/43536445 https://vimeo.com/43536445
dragonwriter 5y ago
I thought this was an SQL injection in WAF, turns out it just is a bug in the SQL injection protection provided by WAF, because WAF’s generic SQL injection protection isn’t bug-compatible with MySQL SQL parsing, so that a vulnerable app behind WAF would not be protected by WAF from an attack using the shouldn't-be-valid SQL. Honestly, I think this highlights a fundamental weakness in trying to put generic SQL injection protection in an external layer rather than just avoiding treating untrustworthy input as SQL in the first place. Conceptually, you need to replicate every protected SQL engine and the manner in which every input might be concatenated to get SQL, or you are going to have exploitable holes.
- icecap12 5y ago
  A WAF is obviously not a control designed to be a full-scale replacement for good input validation and development best practices. So yes, its inherent that there are fundamental weaknesses to WAFs. However, there are still benefits. You deploy a WAF as part of a defense in depth strategy, with one of the best use-cases being situations where you have legacy web systems that nobody is maintaining. Additionally, you can get TLS upscaling, easy HTTP rewrite capabilities, DDoS protection, and other granular controls with some SaaS offerings. So while it's true that a WAF won't stop a determined attacker, there are certainly benefits to operating them, particularly in large enterprise environments. edit: spelling
  - freeqaz 5y ago
    This is true when you think about it from a security perspective, but it's definitely not always the case. Many people _do_ rely on a WAF to outsource their security. It's often political, from my experience, that security is either punted or not prioritized in the promo process for most SWEs. It's the unfortunate reality for many companies to just not actually care about security. If you only promote people based on them shipping features but not fixing security issues, then you slowly remove all incentives to care at an individual level. It's just game theory because there are few penalties for not caring about security. Fortunately, the winds of change are blowing now with new regulations. Society is beginning to force companies to care. We're still far from living in a reality where most companies have a strong security posture with their tech though. It's going to take time and energy for frameworks and development methodologies to catch up (and legacy software to die off or be updated).
    - Kalium 5y ago
      Agreed, it's almost always political. Product has a schedule, developers find it convenient to say "Well, we have a firewall", and now suddenly they can hit the deadline! That they got there by deciding to not validate their inputs isn't important. They can handwave it away as "We make certain assumptions about the traffic coming in over the network" and Product learns a neat trick to ship faster. I've seen this exact dynamic play out several times. Lots of vulnerabilities were created, and infosec pointing to this only attracted the ire of Product. And angered several Director-grade people in engineering who were spreading the idea that a firewall is a replacement for input validation.
- chrisandchris 5y ago
  > […] AWS Web Application Firewall (WAF) customers were left unprotected to SQL injection […] If a WAF is your _last_ line of defense, you‘re doing it wrong.
  - sebazzz 5y ago
    This. It is _a_ line of defense, like so many others. Same as obscurity can also be a line of defense.
jamesfinlayson 5y ago
Interesting bug, but on reflection I find it more interesting that AWS WAF uses MySQL under the hood. edit: I see WAF doesn't use MySQL - thanks. I've seen a few false positives in Akamai's SQL-in-URL detection so not surprising that AWS let something like this slip through.
- jcims 5y ago
  It doesn’t use mysaql under the covers, it’s just that AWS WAF doesn’t know about this syntax so it doesn’t filter for it. Singling out AWS WAF is a bit awkward. I’m sure there are other products that don’t protect against this. WAF in general is a mess, the entire philosophy is broken and it’s at best a speed bump. I don’t know why the industry allowed the moniker.
  - jamesfinlayson 5y ago
    Yep right you are. Agreed though - I think Akamai thinks URLs containing an = are suspicious, even if the URL looks nothing like SQL and very much like a path to a .jpg file.
  - dikei 5y ago
    Yup. Just consider WAF as a low-pass filter that clean out the noise and let you focus on the real attacks that get through.
    - smolder 5y ago
      Another good use case is hardening web applications that you aren't licensed to modify, or otherwise can't secure in a way that's internal to the app. (That could be due to limited time, too much complexity, inaccessible source code, or missing domain knowledge, maybe.) Using a SaaS WAF for greenfield development always seemed to me like... I guess you'd say an "architecture smell" as opposed to code smell. More nodes in a network graph are worth avoiding if you can consolidate without losing functionality, velocity, etc. Using WAF to get defense in depth starts making sense to me only when the paranoia level is sufficiently high and you're out of relatively effective ways to harden the other layers further.
kayodelycaon 5y ago
Ideally, a web application firewall is a first line of defense. (If you use one.) It’s effectively an advanced form of security through obscurity. It stops routine attacks, reducing the work of the machines behind the firewall. If it’s your only protection against SQL injection, you’re going to learn WAF’s limitations at a very inconvenient time.
a-dub 5y ago
> The situation is complex. If requests are malformed, it is natural that security products wouldn’t consider them valid SQL, thus making them unnecessary to block. what?! "this doesn't look like valid sql, so let it pass and have the potentially vulnerable sql server with all the data and creds and tunnels on it parse and reject" sounds ridiculous to me! isn't the whole point of having a security proxy to check if requests are well-formed, and if they're not, block those requests so that the more complicated implementation in the actual server on the production host is protected from malformed requests that are potentially malicious? i get it, building a parser and validator that is independent of the one that is in the actual database server, yet implements the same language is hard... but in my mind, the whole point of using some sort of firewall is that it looks for quasi-valid things and blocks them! (where the parser in the database server, and the parser in the firewall, are two independently hardened pieces of software developed from the language spec that provide redundancy.) edit: ok, i think i see the complexity now. forgot that sql injections are not complete queries, but rather fragments of sql, so they're harder to detect since sql fragments can look so general. (ie; a strict parser for finding them is required otherwise all sorts of reasonable things would get blocked) isn't this why we have pre-tokenized sql queries? they've been around a long, long time.
- Kalium 5y ago
  How sure are you that you can get your parser and validator correct in every case? And how sure are you that you can correctly define and then notice "quasi-valid" at the edge of the defined spec and implementation? How sure are you of your version-specific bug-for-bug compatibility, beyond the edges of the spec? I find it helps to remember that a firewall should be just one layer. You should have others, each of which blocks things detectably malicious and lets the rest go. It's not reasonable to expect a firewall to stop any and all potentially malicious traffic. It's not a replacement for input validation and good development practices, even though I have had coworkers who thought this.
  - a-dub 5y ago
    > I find it helps to remember that a firewall should be just one layer. totally agree.
collegeburner 5y ago
This is why in my new project I decided to use prepared statements wherever possible, it seemed like simply the best choice because sanitizing is never perfect. Here's the article that convinced me in case anybody else wants to read: https://kevinsmith.io/sanitize-your-inputs/ https://kevinsmith.io/sanitize-your-inputs/
- MattPalmer1086 5y ago
  This is absolutely the best mitigation for SQL injection. It can also speed up your queries as it allows better caching of the query plan. It always annoys me when SAST products on detecting injection advise using better input validation. No! Use parameterised queries and be done with it.
  - illvm 5y ago
    Input validation is more general and helps mitigate other types of vulnerabilities, such as overflows and other injection attacks.
    - MattPalmer1086 5y ago
      I'm all for defence in depth, and agreed there are many other types of injection attack this doesn't mitigate.
- Shorn 5y ago
  Until someone wants to do something slightly different, can't figure out how to make it work because they've tried nothing and are all out of ideas - so they just use string concatenation and execute the string dynamically. Ask me how I know.
snvzz 5y ago
AWS WAF might be very useful, but there's only so much you can do when the database is mysql. Postgresql has been around for a while. There's literally no reason to use mysql except legacy code in life support mode.
- mrweasel 5y ago
  > There's literally no reason to use mysql except legacy code in life support mode. Multi-master setups? I don't think Postgresql is capable of that yet. Does anyone actually use/depend on a WAF (AWS or others)? It feeling like a clever solution, but also remarkably like snake oil.
  - dagw 5y ago
    Multi-master setups? I don't think Postgresql is capable of that yet. It doesn't support it "out of the box", but there at least two different third-party solutions that add multi-master replication to PostgreSQL.
    - snvzz 5y ago
      There's also Amazon's own Aurora in postgresql mode. Relevant because AWS context (AWS WAF was discussed).
- paulryanrogers 5y ago
  MySQL has undo logs while PostgreSQL has vacuum based MVCC. It's a significant difference for some workloads. And MySQL is closer to standard SQL on some things like PL/PSM. I personally still prefer PostgreSQL but can't deny the benefits of a well configured MySQL installation.
deepsun 5y ago
It still fascinates me why people take human-focused formats (SQL, shell commands in one line) and use them in automated programs. If we'd used application-focused formats, there would be much less room for injections. E.g. MsgPack for SQL statements. If a program does not interpret shell commands, but composes raw ["program", "argument"] and runs it, there's no room for sh-injections. Same can be said for HTML (it was designed for human writing/reading), but it's too late to change that. But SQL and shell commands can be fixed today in application access.
- defanor 5y ago
  > It still fascinates me why people take human-focused formats (SQL, shell commands in one line) and use them in automated programs. I tend to assume that it happens because it is what's available, and possibly easier (at least in some cases, but then the lowest common denominator wins). Unintended and not quite appropriate use of technologies in general seems to often happen that way. > Same can be said for HTML (it was designed for human writing/reading), but it's too late to change that. But SQL and shell commands can be fixed today in application access. Well, HTML 5 includes the XML-based syntax, which is quite machine-friendly. And for running programs one can indeed skip a shell and just run it with provided arguments and environment variables when it's done programmatically. While to SQL there's usually no alternatives in relational databases. Though I think even SQL isn't inherently bad: a complete, specified, and sensible grammar, together with a proper parser, would allow sane composition and processing, and exclude the bugs such as this one.
- rini17 5y ago
  The root problem is programming languages don't have the symbolic facilities to construct SQL statement safely, or they are incredibly awkward. Distinguishing keywords, parameters and table names at source code level would allow for strict version of SQL with simple unambiguous syntax throughout. But nope, let's just concatenate some strings.
  - zokier 5y ago
    What about LINQ?
Traubenfuchs 5y ago
Why would anyone ever need a WAF against SQL injections? Isn‘t injection safe SQL development beaten into everyones head from all directions, all the time?

124 points by fipar 5y ago

RNCTX 5y ago

Reminiscent of the talk in which “SELECT 1 / 0” was offered as reason enough in one line of code to not use MySQL.

n3t 5y ago
What talk are you referring to?
- RNCTX 5y ago
  https://vimeo.com/43536445 https://vimeo.com/43536445

dragonwriter 5y ago

I thought this was an SQL injection in WAF, turns out it just is a bug in the SQL injection protection provided by WAF, because WAF’s generic SQL injection protection isn’t bug-compatible with MySQL SQL parsing, so that a vulnerable app behind WAF would not be protected by WAF from an attack using the shouldn't-be-valid SQL. Honestly, I think this highlights a fundamental weakness in trying to put generic SQL injection protection in an external layer rather than just avoiding treating untrustworthy input as SQL in the first place. Conceptually, you need to replicate every protected SQL engine and the manner in which every input might be concatenated to get SQL, or you are going to have exploitable holes.

icecap12 5y ago
A WAF is obviously not a control designed to be a full-scale replacement for good input validation and development best practices. So yes, its inherent that there are fundamental weaknesses to WAFs. However, there are still benefits. You deploy a WAF as part of a defense in depth strategy, with one of the best use-cases being situations where you have legacy web systems that nobody is maintaining. Additionally, you can get TLS upscaling, easy HTTP rewrite capabilities, DDoS protection, and other granular controls with some SaaS offerings. So while it's true that a WAF won't stop a determined attacker, there are certainly benefits to operating them, particularly in large enterprise environments. edit: spelling
- freeqaz 5y ago
  This is true when you think about it from a security perspective, but it's definitely not always the case. Many people _do_ rely on a WAF to outsource their security. It's often political, from my experience, that security is either punted or not prioritized in the promo process for most SWEs. It's the unfortunate reality for many companies to just not actually care about security. If you only promote people based on them shipping features but not fixing security issues, then you slowly remove all incentives to care at an individual level. It's just game theory because there are few penalties for not caring about security. Fortunately, the winds of change are blowing now with new regulations. Society is beginning to force companies to care. We're still far from living in a reality where most companies have a strong security posture with their tech though. It's going to take time and energy for frameworks and development methodologies to catch up (and legacy software to die off or be updated).
  - Kalium 5y ago
    Agreed, it's almost always political. Product has a schedule, developers find it convenient to say "Well, we have a firewall", and now suddenly they can hit the deadline! That they got there by deciding to not validate their inputs isn't important. They can handwave it away as "We make certain assumptions about the traffic coming in over the network" and Product learns a neat trick to ship faster. I've seen this exact dynamic play out several times. Lots of vulnerabilities were created, and infosec pointing to this only attracted the ire of Product. And angered several Director-grade people in engineering who were spreading the idea that a firewall is a replacement for input validation.
chrisandchris 5y ago
> […] AWS Web Application Firewall (WAF) customers were left unprotected to SQL injection […] If a WAF is your _last_ line of defense, you‘re doing it wrong.
- sebazzz 5y ago
  This. It is _a_ line of defense, like so many others. Same as obscurity can also be a line of defense.

jamesfinlayson 5y ago

Interesting bug, but on reflection I find it more interesting that AWS WAF uses MySQL under the hood. edit: I see WAF doesn't use MySQL - thanks. I've seen a few false positives in Akamai's SQL-in-URL detection so not surprising that AWS let something like this slip through.

jcims 5y ago
It doesn’t use mysaql under the covers, it’s just that AWS WAF doesn’t know about this syntax so it doesn’t filter for it. Singling out AWS WAF is a bit awkward. I’m sure there are other products that don’t protect against this. WAF in general is a mess, the entire philosophy is broken and it’s at best a speed bump. I don’t know why the industry allowed the moniker.
- jamesfinlayson 5y ago
  Yep right you are. Agreed though - I think Akamai thinks URLs containing an = are suspicious, even if the URL looks nothing like SQL and very much like a path to a .jpg file.
- dikei 5y ago
  Yup. Just consider WAF as a low-pass filter that clean out the noise and let you focus on the real attacks that get through.
  - smolder 5y ago
    Another good use case is hardening web applications that you aren't licensed to modify, or otherwise can't secure in a way that's internal to the app. (That could be due to limited time, too much complexity, inaccessible source code, or missing domain knowledge, maybe.) Using a SaaS WAF for greenfield development always seemed to me like... I guess you'd say an "architecture smell" as opposed to code smell. More nodes in a network graph are worth avoiding if you can consolidate without losing functionality, velocity, etc. Using WAF to get defense in depth starts making sense to me only when the paranoia level is sufficiently high and you're out of relatively effective ways to harden the other layers further.

kayodelycaon 5y ago

Ideally, a web application firewall is a first line of defense. (If you use one.) It’s effectively an advanced form of security through obscurity. It stops routine attacks, reducing the work of the machines behind the firewall. If it’s your only protection against SQL injection, you’re going to learn WAF’s limitations at a very inconvenient time.

a-dub 5y ago

> The situation is complex. If requests are malformed, it is natural that security products wouldn’t consider them valid SQL, thus making them unnecessary to block. what?! "this doesn't look like valid sql, so let it pass and have the potentially vulnerable sql server with all the data and creds and tunnels on it parse and reject" sounds ridiculous to me! isn't the whole point of having a security proxy to check if requests are well-formed, and if they're not, block those requests so that the more complicated implementation in the actual server on the production host is protected from malformed requests that are potentially malicious? i get it, building a parser and validator that is independent of the one that is in the actual database server, yet implements the same language is hard... but in my mind, the whole point of using some sort of firewall is that it looks for quasi-valid things and blocks them! (where the parser in the database server, and the parser in the firewall, are two independently hardened pieces of software developed from the language spec that provide redundancy.) edit: ok, i think i see the complexity now. forgot that sql injections are not complete queries, but rather fragments of sql, so they're harder to detect since sql fragments can look so general. (ie; a strict parser for finding them is required otherwise all sorts of reasonable things would get blocked) isn't this why we have pre-tokenized sql queries? they've been around a long, long time.

Kalium 5y ago
How sure are you that you can get your parser and validator correct in every case? And how sure are you that you can correctly define and then notice "quasi-valid" at the edge of the defined spec and implementation? How sure are you of your version-specific bug-for-bug compatibility, beyond the edges of the spec? I find it helps to remember that a firewall should be just one layer. You should have others, each of which blocks things detectably malicious and lets the rest go. It's not reasonable to expect a firewall to stop any and all potentially malicious traffic. It's not a replacement for input validation and good development practices, even though I have had coworkers who thought this.
- a-dub 5y ago
  > I find it helps to remember that a firewall should be just one layer. totally agree.

collegeburner 5y ago

This is why in my new project I decided to use prepared statements wherever possible, it seemed like simply the best choice because sanitizing is never perfect. Here's the article that convinced me in case anybody else wants to read: https://kevinsmith.io/sanitize-your-inputs/ https://kevinsmith.io/sanitize-your-inputs/

MattPalmer1086 5y ago
This is absolutely the best mitigation for SQL injection. It can also speed up your queries as it allows better caching of the query plan. It always annoys me when SAST products on detecting injection advise using better input validation. No! Use parameterised queries and be done with it.
- illvm 5y ago
  Input validation is more general and helps mitigate other types of vulnerabilities, such as overflows and other injection attacks.
  - MattPalmer1086 5y ago
    I'm all for defence in depth, and agreed there are many other types of injection attack this doesn't mitigate.
Shorn 5y ago
Until someone wants to do something slightly different, can't figure out how to make it work because they've tried nothing and are all out of ideas - so they just use string concatenation and execute the string dynamically. Ask me how I know.

snvzz 5y ago

AWS WAF might be very useful, but there's only so much you can do when the database is mysql. Postgresql has been around for a while. There's literally no reason to use mysql except legacy code in life support mode.

mrweasel 5y ago
> There's literally no reason to use mysql except legacy code in life support mode. Multi-master setups? I don't think Postgresql is capable of that yet. Does anyone actually use/depend on a WAF (AWS or others)? It feeling like a clever solution, but also remarkably like snake oil.
- dagw 5y ago
  Multi-master setups? I don't think Postgresql is capable of that yet. It doesn't support it "out of the box", but there at least two different third-party solutions that add multi-master replication to PostgreSQL.
  - snvzz 5y ago
    There's also Amazon's own Aurora in postgresql mode. Relevant because AWS context (AWS WAF was discussed).
paulryanrogers 5y ago
MySQL has undo logs while PostgreSQL has vacuum based MVCC. It's a significant difference for some workloads. And MySQL is closer to standard SQL on some things like PL/PSM. I personally still prefer PostgreSQL but can't deny the benefits of a well configured MySQL installation.

deepsun 5y ago

It still fascinates me why people take human-focused formats (SQL, shell commands in one line) and use them in automated programs. If we'd used application-focused formats, there would be much less room for injections. E.g. MsgPack for SQL statements. If a program does not interpret shell commands, but composes raw ["program", "argument"] and runs it, there's no room for sh-injections. Same can be said for HTML (it was designed for human writing/reading), but it's too late to change that. But SQL and shell commands can be fixed today in application access.

defanor 5y ago
> It still fascinates me why people take human-focused formats (SQL, shell commands in one line) and use them in automated programs. I tend to assume that it happens because it is what's available, and possibly easier (at least in some cases, but then the lowest common denominator wins). Unintended and not quite appropriate use of technologies in general seems to often happen that way. > Same can be said for HTML (it was designed for human writing/reading), but it's too late to change that. But SQL and shell commands can be fixed today in application access. Well, HTML 5 includes the XML-based syntax, which is quite machine-friendly. And for running programs one can indeed skip a shell and just run it with provided arguments and environment variables when it's done programmatically. While to SQL there's usually no alternatives in relational databases. Though I think even SQL isn't inherently bad: a complete, specified, and sensible grammar, together with a proper parser, would allow sane composition and processing, and exclude the bugs such as this one.
rini17 5y ago
The root problem is programming languages don't have the symbolic facilities to construct SQL statement safely, or they are incredibly awkward. Distinguishing keywords, parameters and table names at source code level would allow for strict version of SQL with simple unambiguous syntax throughout. But nope, let's just concatenate some strings.
- zokier 5y ago
  What about LINQ?

Traubenfuchs 5y ago

Why would anyone ever need a WAF against SQL injections? Isn‘t injection safe SQL development beaten into everyones head from all directions, all the time?