For example, you might have a name in the database. What if someone is called O'Beirne? How do we get this into the database? Do we only allow them to have the name OBeirne?

But what if someone passes you an email address that's really 500 comma-separated email addresses?

What if someone passes 'admin=true' to your user update URL?

Your output sanitization doesn't do much for those.

I did not explain very clearly. What I was trying to say was that output normalisation will prevent SQL injection attacks.

I am considering the components of the SQL statement, such as the string O'Beirne, to be things that have to be normalised before being output to the database in the SQL statement. This output normalisation, as the other replies to my post have correctly said, is best done for you by a library. It cannot be done properly by restricting the inputs, as my O'Beirne example shows, only by output normalisation of the SQL.

I think you're trying to say that content neutralization (turning ' into &quot;, for instance) stops SQLI. It might or it might not, depending on the vector (tablespace injection doesn't care about metacharacters, for instance). It's at least more accurate than saying "if you make sure that the web app doesn't spit out [!@#$%^&*(){}:"<>?] you're safe".

http://www.reddit.com/r/programming/comments/86kgp/xss_cross...

which is the context of my quote of larholm above and is a discussion about this page

http://www.owasp.org/index.php/XSS_(Cross_Site_Scripting)_Pr...

Perhaps this is not common usage, but within this context I believe I am correct in saying output normalization is what prevents SQL injection.

larholm goes on to say:

"The lack of output normalization IS the security vulnerability."

"You can either normalize your output for each specific location as you encounter it, or normalize your input once in advance for all current and future output locations."

"The former beats the latter, as it is impossible for you to know how the data will be output in the future."

which also seems correct.

What is "tablespace injection"? I just googled it and there are no references to it anywhere.

http://www.google.com/search?q=%22tablespace+injection%22...

If I understand the idea of output-normalization correctly you'd keep an email address that's really 500 comma-seperated addresses in the database in exactly that form, but when it came time to send an email you would normalize the value, and at that stage you would check for such a case and handle it as appropriate (i.e. remove all commas, send to only the first, etc.) The point is that you don't try to create a stored value that can be used safely everywhere, rather you make the value safe at each instance where it's used.

P.S. This appears to be the relevant conversation, interesting reading: http://www.reddit.com/r/programming/comments/86kgp/xss_cross...

(There's almost never a good reason to construct SQL on the fly.)

A rule of thumb is that if you are concatenting user input strings into your SQL query strings you are doing it wrong.

SQL Injection vulnerabilities happen when inputs influence the parsing of the query. You can't do that when the database already has an AST for the query.

Parameterized queries are not just a different kind of quoting.

b) I'm guessing there are plenty of databases that execute parametrized queries entirely differently than a plaintext ones, including the absence of an escaping step. Since the whole point of quoting + escaping is to demarcate the "data" parts of a query, and you've already done that with parameters, there's no reason a query engine can't use the parameter directly rather than run it through a useless escape then parse-and-unescape cycle.

People have been playing charset games to get past SQL quoting for almost 10 years now, and not just in PHP.