Hacker News Reader

Comment by xd | Hacker News Reader

SQL injection attacks alone are almost always a result of not filtering input...

This the wrong way to look at it, and yes, the PHP world always does this wrong because they're too focused on HTML.

SQL injection occurs when you're not escaping data while producing output, namely, an SQL query sent to the DB.

XSS attacks occur when you're not escaping data while producing HTML, but you don't need angle brackets to do it. <a title="$string"> allows for XSS injection with just a quote character.

Header injection attacks occur when you're not escaping data while producing HTTP/MIME headers, and all you need is a line-break character.

The escaping always depends on the output context, and the data in the database cannot be made safe for all these contexts. You will still need to allow people named "O'Brian", let people post "<_<" smileys, use Unicode in their names, etc.

> the PHP world always does this wrong because they're too focused on HTML.

No. The PHP world recommends, time and time again, using PDO and binding variables to queries. I've yet to meet an individual who does it the other way, other than people who are relying on extremely outdated tutorials (7+ years ago). Hell, even this document does. This document, unfortunately, uses the word filter in the wrong way, but the intention is still fine.

One thing I've run into is that PDO does not let you parameterize the field name in "ORDER BY" clauses. And there are a few other little things that I've wanted to do that it doesn't allow. It may be that me even running into these problems means I'm doing something wrong at a structural level. Regardless, in those statements where I want something to be dynamic, but it's not allowed by PDO, I do an exact equality check against a white list.

Well, that's not what binding is supposed to do. After all, field names aren't something you should be exposing at all.

> I do an exact equality check against a white list.

Pretty much the way you are supposed to do it (except, I'd actually use the value in the white list rather than the matched input value, as the two should not be the same).

I suggest removing the section about filtering entirely and making sure the section about PDO explains SQL injection briefly.

I'm not the op, but I'll keep that in mind fora push request. I think filtering just needs to be renamed to validation, and improved (but then that was the point of posting it here I imagine).

Which is fine for SQL injection, but completely ignores every other context, of which there are many, and which don't have PDO-like APIs. That's what I meant by PHP getting it wrong. The entire filter pipeline is built to assume you're outputting only to HTML, and it's useless for making data safe for other contexts without completely destroying it.

In my anecdotal experience SQL injection attacks are almost as often caused by relying on filtering data and then forgetting to filter it for some fields. You should instead rely on parametrized queries and only use string operations to build queries where you must (and then do it very carefully).

SQL injections are very rare to non-existent in code where the programmers generally rely on parametrized queries.

With parametrized queries, that becomes a non-issue, but I still see no point in cluttering the database with data that's just going to be filtered out at some point - might as well filter it before it goes into the DB to begin with. The exception, of course, being those rare cases when some users need to see the filtered data and others need to see the raw data, but even then, you likely won't want to allow everything.

I am not sure if you are referring to the same sort of filtering mentioned in the document. The data is filtered before it hits the database layer, this saves resource being used.

That's what I meant. I just kind of articulated it poorly, my apologies.

Parametized queries are just another way of filtering input ..

Yes, but on the site, they make a clear distinction between parametrized queries with PDO and other types of input filtering. I misunderstood and thought you were referring specifically to the latter.

No they're not. They're a method for ensuring that what is meant to go into some field in some database actually ends up there and does not start doing things it should not be doing.

It's sometimes known as sanitising input, so I'm guessing that's what they were trying to refer to.

Output is not what goes to the user/page, in this context it is what your code sends anywhere, such as to mysql_execute or something

Reply on news.ycombinator.com