> "Sometimes SQLi isn't always obvious"

Pretty simple: 100% of the time use prepared statements, NEVER build an SQL query yourself.

Tada, 0% SQLi.

But then you add a few layers of frameworks on top of that, each with its own mix of convenience, abstraction and SQLI protection. Occasionally, people will use available the escape hatch mechanisms to deeper levels when the convenience (or their understanding of it) does not align well with what they need to do. It's not impossibly hard to get confused about where the handholding stops in a complex stack like that.

I might naively underestimate the amount of pure "naked SQL" code still written, but I suspect that in this age, more SQLi accidents happen in "95% safe" environments. Kind of like the class of accidents we expect from 95% autonomous cars, where an unfocused and out of practice driver is expected to take over the wheel when the robot brain decides that driving is to difficult.

Yet researchers have actually investigated the problem of automatically generating prepared statements from existing code and it turns out to be weirdly challenging.

The problem isn't that some people are idiots. The problem is that we, as an industry, don't have great security solutions.

In the words of Bruce Schneier - "Security is a process, not a product" [1]

Process that could help mitigate this bugs -

- Security / safe coding training

- Code audits

- Testing - static and dynamic

- Policies that are communicated such as "Always use prepared statements" or "use X framework"

[1] https://www.schneier.com/crypto-gram/archives/2000/0515.html

While this is true, the developer implementing this can still make a mistake. I've seen (esp. on long multiline queries that get modified over some time), a mix of prepared variables for things like userids and string concat for things like table names but the dba or the dev doesn't realize the attacker has control over the table name due to how they are handling user input on that particular endpoint. Maybe the table name is passed in on one endpoint because it's old and janky. Then it fools people because they see it's a str passed to prepared statement func and assume it's safe. I've seen this in some place in a large number of the apps I've worked on.

If you have to let the attacker control a direct substring of SQL, then use a whitelist of allowed characters – for tablenames, [a-zA-Z0-9_] is usually good enough, and then put that in quotes (as some databases reserve keywords such as "user" or "password", which is bad if you want to name columns like that).

I’ve had quite a few codebases I’ve worked on where I had to replace naive code [1], and until now, it’s always been easily possible to ensure that the entire space of possible inputs is limited enough to prevent SQL injections.

Sure, there are rare projects where you have to do such very complicated systems, but for 99%, it’s possible to get guaranteed protection from SQL injections.

    ________________________

1: "db->query('SELECT 1 FROM users WHERE username = "' + $_POST['username'] + '" AND password = "' + md5($_POST['password']) + ';"');" was real code I’ve seen

4) Intentional negligence.