Scientific notation bug in MySQL left AWS WAF vulnerable to SQL injection
gosecure.net
gosecure.net
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.
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
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).
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.
If a WAF is your _last_ line of defense, you‘re doing it wrong.
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.
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.
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.
But nope, let's just concatenate some strings.
If it’s your only protection against SQL injection, you’re going to learn WAF’s limitations at a very inconvenient time.
Postgresql has been around for a while. There's literally no reason to use mysql except legacy code in life support mode.
I personally still prefer PostgreSQL but can't deny the benefits of a well configured MySQL installation.
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.
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.
Relevant because AWS context (AWS WAF was discussed).
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.
Because it's not a silver bullet. At least for postgres, they only allow parameters with literals. Something like
SELECT * FROM users WHERE username = :username
SELECT * FROM users WHERE :column = :value
that's by design. in 99% of cases you should never, ever do this.
maybe if you're building a database exploration/management/manipulation tool, sure... but for all other cases (including weird manual table sharding schemes), the queries should be literals that are included in static code (possibly codegened if there's too many of them).
under no circumstances should a user string ever be used to construct a column name.
> Of course, using an ORM or query builder can potentially solve this issue, but getting those to work and/or refactoring your code to do so is non-trivial,
orms are really complicated. query builders are probably okay for solving the issue most of the time. if i was building production software that i thought may be targeted, i would either write all the queries by hand, or codegen them ahead of time. if i had to pick them at runtime based on user input, i'd have one lookup function that takes the user data and returns a handle to the prepared query.
> and it's not guaranteed that they're safe from sql injections either.
is that actually true? i'm pretty sure if you use a prepared/tokenized query, all your sql injection nightmares will go away.
sanitize untrusted strings and use prepared queries. problem solved.
personally i think database drivers should have a mode that removes any capability for doing queries with raw sql strings.
Not as easy as just dropping in the user's choices, but guarantees they have no control over the resulting query string.
that depends entirely on how many columns/rows you're returning.
> prepared queries avoid repetitive string parsing of the query language and give the db backend more opportunities to optimize.
On postgres at least, prepared statements only last for a session, so the benefits you state is dubious. Even for databases that support persistent prepared statements, the fact that they're part of the database makes it a nightmare to deploy. Every time you update a query, you'll need a mechanism to update the corresponding prepared statement.
sessions should live for the lifetime of the processes of the backend application server, so they would get updated every time you deploy. if you don't have a long living backend, then usually a long living proxy is used.
if you're setting up a new session on every request, it seems there's not really much point to worrying about performance!
anyway, every application is different and it's worth benchmarking. if i were building something today that used a sql backend, i would err on the side of using prepared statements as much as possible, both for security and performance reasons. maybe string queries could make sense, but they're both more expensive to run and more likely to result in a vulnerability, even if they were coded carefully at first implementation.
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.
totally agree.
Isn‘t injection safe SQL development beaten into everyones head from all directions, all the time?
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.
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.
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.