Hacker News Reader

Comment by ishitatsuyuki | Hacker News Reader

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.

View on HN

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

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 \";

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.

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.

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.

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.

Reply on news.ycombinator.com