Raw SQL

When you're writing SQL -- for anything that takes human input really, a lot of things have been done to avoid the injection.

Everyone that's heard of SQL injection knows that (I'm going to use PHP as a sample) doing something like this isn't safe:

$sql = "SELECT * FROM `users` 
.  WHERE `userName` = "{$_POST["username"]}" 
.  AND `pass` = "{$_POST["pass"]}";";


Magic

Then of course, someone came out with the idea of using "magic escape quotes" to deal with program input that wasn't sanitized correctly and directly put into sql as a result of bad practices. This didn't really solve the issue with SQL injection, but it did mean all user input got mangled up.

Adding slashes

So, some people turned off magic quotes. Then, they parsed user input before the point of SQL through addslashes() which in theory escapes all the quotes and your hacker can't do ' OR 1=1, but even the documentation for addslashes it's self say that you shouldn't use addslashes, it says use the database-specific function such as mysql_real_escape_string(), but this is still said to not be enough by some.

Adding slashes specific to Database

So, we can't use DBMS specific *_real_escape_string, we can't use add slashes, the "magic quotes" thing caused lots of issues, and the web is full of short worded quotes such as:


  "A dedicated hacker will find a way to
  jump through your quote-escaping loops,
  just use the DBAL prepared statements" - John Q any programmer


Okay, so that scared me enough to use prepare statements and a DBAL. It didn't really explain anything, but it sounds good because I've heard it a lot.

Prepared statements

So now we're using PDO, or a DBAL from a framework, or something else that wraps all our sql and makes sure someone can't run an sql injection.

My question is basically a "why not?", not a "what should I use?". The web's full of people telling you to use this or use that or whatever, but no explanations of why these things had to happen.

Direct questions

Pointed questions (reminder, I'm asking about SQL, PHP was an example language because of it's bad rep around SQL, concepts are universal):


Why can't we escape all user input using "magic" ?
Why wasn't addslashes "good enough"?
Whats wrong with using DB-specific escape functions, and why was it better than addslashes?
Why are prepared statements with frameworks and PDO being hailed as the gold standard of SQL? Why are they better? Why can't I do an SQL injection with these, where as I COULD have with the previously mentioned means? Can a programmer not somehow manage to still screw this up? What should they look out for?
Any other concerns I haven't brought up?



  Why can't we escape all user input using "magic"?


At the time the magic is applied, it is unknown where the data will end up. So magic quotes are destroying data that, unless it is written unescaped to a database.

It may just be used in the HTML response sent back to the client. Think of a form that has not beem filled in completely and is therefore shown again to the user. With magic quotes, the data entered on the first attempt will now be SQL escaped, which is meaningless on an HTML page. Even worse: on the second submission the data is SQL escaped again.


  Why wasn't addslashes "good enough"?


It has issues with multibyte characters:

' 27
\ 5c
뼧 bf 5c


Those are two bytes, but only one Unicode character.

Since addslashes does not know anything about Unicode, it converts the input bf 27 to bf 5c 27. If this is read by a Unicode-aware program, it is seen as 뼧'. Boom.

There is a good explanation of this issue at http://shiflett.org/blog/2006/jan/addslashes-versus-mysql-real-escape-string


  Whats wrong with using DB-specific escape functions, and why was it better than addslashes?


They are better because they ensure that the escaping interprets the data in the same way the database does (see the last question). 

From a security point of view, they are okay if you use them for every single database input. But they have the risk that you may forget either of them somewhere.

Edit: As getahobby added: Or that you use xxx_escape_strings for numbers without adding quotation marks around them in the SQL statement and without ensuring that they are actual numbers by casting or converting the input to the appropriate data type /Edit

From a software development perspective they are bad because they make it a lot harder to add support for other SQL database server software.


  Why are prepared statements with frameworks and PDO being hailed as the gold standard of SQL? Why are they better? 


PDO is mostly a good thing for software design reasons. It makes it a lot easier to support other database server software. It has an object orientated interface which abstracts many of the little database specific incompatibilities.


  Why can't I do an SQL injection with these [prepared statements], where as I COULD have with the previously mentioned means?


The "constant query with variable parameters" part of prepared statements is what is important here. The database driver will escape all the parameters automatically without the developer having to think about it.

Parametrized queries are often easier to read that normal queries with escaped parameters for syntactic reasons. Depending on the environment, they may be a little faster.

Always using prepared statements with parameters is something that can be validated by static code analysis tools. A missing call to xxx_escape_string is not spotted that easily and reliably.


  Can a programmer not somehow manage to still screw this up? What should they look out for?


"Prepared statements" imply that the are constant. Dynamically generating prepared statements - especially with user input - still have all the injection issues.
