Hacking - SQL Injection - False measures and bad practices

A grave delusion, still shared by almost every PHP user (and even OWASP, as you can see). Consists of two parts: “escaping” and “user input”:

• escaping: escaping“ is strongly connected to the old mysql_real_escape_string. It does only part of the job, for only one literal type. And when used alone or not in the proper place is a sure call for disaster. It does not support multi-byte character sets.

• safe user input: There should be no such words in the context of injection protection. Every variable is potentially dangerous - no matter of the source! Or, in other words - every variable has to be properly formatted to be put into query - no matter of the source again. It's destination that matters. The moment a developer starts to separate the sheep from the goats, he does his first step to disaster. moreover, even the wording suggests bulk escaping at the entry point, resembling the very magic quotes feature - already despised, deprecated and removed.

• magic quotes: Bad! It's already removed from the PHP language. Magic quotes also use the generic functionality provided by PHP's addslashes() function, which is not Unicode-aware and is still subject to SQL injection vulnerabilities in some multi-byte character encodings. Does not consider where the data will end up, so magic quotes end up destroying (changing) the data, unless it is written unescaped to a database.

• data validation: Input (in the meaning of user input) data validation has absolutely nothing to do with SQL. Really. No validation rule can help against SQL injection if a free-form text is allowed. Yet we have to format our SQL despite of any validation anyway - remember John O'Groats who bears a name which is perfectly valid from the user input point of view. Also remember that validation rules may change.

• htmlspecialchars (and also filter_var(), strip_tags() and the like): It's HTML encoding, if you didn't notice yet. It has absolutely nothing to do with SQL. It helps nothing in the matter, and should never be used in context of SQL injection protection. It's absolutely inapplicable for SQL, and cannot protect your query even if used as a string escaping function. Leave it for other parts of your application. Also, please understand, that SQL formatting should never ever touch the data. Then you put your jewelery in a safe, you want it back intact, not some parts amended or replaced “for your own safety!”. Same here. A database is intended to store your data, not to “protect” it. And it is essential to store the exact data you want back (means your silly base64 attempt is wrong as well, by the way).

• Universal “clean'em all” sanitization functon: Such a function just should have never exists. Beside the fact that each context we are going to use our data in (SQL query, HTML code, JS code, JSON string, etc. etc. etc.) require different formatting, there are even distinct sub-types of data literals in all these mediums - each require it's own formatting too. Which makes it's just impossible to create a single all-embracing function to format 'em all in once, without spoiling the source data yet leaving huge security holes at the same time.

• Filtering out malicious characters and sentences: This one is simple - it's absolutely imaginary measure. It fails reality check. Nobody ever used it on a more-or-less viable site. Just because it will spoil user experience.

• Stored procedures: NOT in web-development. this suggestion is from another realm, where one would have fancy of limiting database users to a set of stored procedures. It is more like general security measure, pointed against local users, rather than against an intruder. Anyway the approach is just inviable in the web-development. It can add nothing to what prepared statements already offer, yet in a way inconvenient and toilsome way.

• Separate DB accounts for running SELECT and DML queries: Again not a protection measure, but a just a [worthless] attempt to soften the consequences of already preformed attack. Worthless because SELECT-based injection as a disaster alone. And of course useless because we are protected already by formatting our queries properly.