Note: One, I am not sure if synthetic queries is right word for the risk I am talking about. Second, though I am considering 3-tier model of web-applications general answers are welcome in client-server situations where server side validation is not possible.

Currently I have a page where you do certain computations using what is called as DHTML, This computation generates a string which has no particular pattern as such. This string is sent to a server side script using AJAX. 

Anyone with basic training in these technologies can read the code and realize that the query is sent is something like this:  

 http://domain.com/script.php?var=theStringSoGenerated


Hoping to exploit a possible flaw, a hacker types in his browser:

 http://domain.com/script.php?var=aCompletelyRandomString


He does this a few times and sees no evident benefit and quits, but in the middle tier, the PHP script, completely helpless without a possible validation, updates and inserts the random string into the database impacting its integrity and leading to wastage of resources.

Question: How can I protect my application against such attacks?


  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.
