The Phoenix wrote:Mordred wrote:Nope, mysql_real_escape_string() is not enough, here's why (with plenty of examples):
http://www.webappsec.org/projects/articles/091007.shtml
Generic filtering of input is good, but it works on another level, the DB-aware layers should concentrate on proper syntax and escaping.
Thats an excellent article about the full lifecycle of validation and escaping, but I'm struggling with a few issues.
Starting from #4 down, #4 highlights an issue with validation - not escaping. Further, even you state:
The reader must realise that input validation (in our case making sure that what we expect to be an int is really int) and escaping the parameter before giving it to the query are two different security steps. In this particular case either one will suffice, but in general, for in-depth security, you must always do both. Also, the two tasks will most probably be carried by two different subsystems in your real-life code, so the validating and escaping code will not be adjacent as displayed in this simplified case.
I don't think a database abstraction layer has sufficient awareness to be able to determine whether an item is intended to be a string or an int, and as a result, can't reliably perform the validation. That has to be performed in a different subsystem, correct?
In example 4 the issue is twofold, that's why it can be solved in two different ways. Either the application layer forces the value to be integer (i.e. we rely on validation), and we leave the field unquoted (which is a valid SQL syntax), OR (
much better) we leave this to the database layer, by adding quotes (which is also valid SQL syntax, although maybe only for some DB systems, MySQL for sure) and doing escaping. The best ( I called it
robust in the article, because it will continue to work even after source modifications) makes use of both techniques at the same time.
So yes, the validation is done on a different level (to emphasize this, I put an elipsis between the validation and escaping). As a side note, though, in the cases where we're talking about ints, the database layer
is aware enough of the type of data it needs - after all the column type in the table is known.
The Phoenix wrote:
#5 is a variation on the same issue, albeit with a much more harsh bite. Nevertheless, it still puts the requirement on validation, which the db abstraction layer isn't sufficiently informed to do.
On the contrary, see the above comment. The database layer knows where the variable will be put in the query, and it knows that it wants an integer. In a sense, casting to int is equivalent to escaping - all invalid characters (non-digits in our case) are "escaped" by being removed.
The Phoenix wrote:
#6 is validation prevented as well, this time with hex instead of ints.
Oh, #6 means
chapter 6 ... I thought is it Example 6. Ok.
No, it is not a validation issue, frankly it is the developer being an idiot

Alas, I've seen it happen in the wild both with column names and sort directions... Anyway, as in the case of ints above, this is not validation. The systems that handle input shouldn't know such intimate details about the database structure, it is the DB-layer's job to ensure that what it is being given is not invalid names or sorting keywords. Ideally, this shouldn't happen at all. I personally implement ordering instructions by encoding them with a number - $n/2 gives the column number, $n%2 gives ASC/DESC. In that way only valid column names and order keywords go in the dynamic query.
Also the hex encoding is just a way to use the vulnerability without quotes, it goes around the
escaping mechanism, not the validation mechanism. An attacker could also use MySQL string functions (CHAR/CONCAT) to construct string values,
Again, the fault is not in the lack of validation! I very much dislike the idea that secure code need validation, but that's a topic for another rant.
The Phoenix wrote:
#8 is covered, because the db abstraction layer in question prevents multiple queries in a single call.
So that really leaves #7 as an issue that the db abstraction layer can/should effectively work to prevent, is that correct?
#7 is easy for me to understand, but I'm stumbling on understanding the proper prevention. I guess I'm lost because of the line:
Test 3 demonstrates that in our case we don't need to escape the LIKE escape character, as mysql_real_escape_string() already escaped it.
Even though above that it showed mysql_real_escape_string did NOT escape it. I'm sure I'm just not understanding effectively.
So, with validation not something the db abstraction layer can effectively provide, is there more that needs to be done to escape the query than just mysql_real_escape_string (at the db abstraction layer only)?
I suspect from #7 that there is. Perhaps if we detect a % sign (or another wildcard for LIKE), we run addcslashes?
Thanks for the informative post!
I admit that the wording of that paragraph could be clearer. It describes the highly theoretical scenario of using another escape character for the LIKE statement. The "
LIKE escape character" is backslash (\), and mysql_real_escape_string() does escape it. The reason I mention it, is because if we choose another escape character, lets say '*', and we use addcslashes, the attacker could give us a query containing the new escape character:
Code: Select all
$search = '*'; //attack
$search = mysql_real_escape_string($search);
$search = addcslashes($search, "%_");
RunQuery("SELECT * FROM `table` WHERE `column` LIKE '$search*%' ESCAPE '*' "); //we want to search for values ending in '%'.
What would happen is that the injected '*' will escape the constant '*', and the '%' sign will
lose its escaping, and will start to act as a wildcard. As I said this is highly theoretical (haven't seen it done in the real world) and it is much milder than being able to inject wildcards in the
beginning of a LIKE parameter.
Edit: To reemphasize my answer to that question:
So, with validation not something the db abstraction layer can effectively provide, is there more that needs to be done to escape the query than just mysql_real_escape_string (at the db abstraction layer only)?
All prevention steps are done entirely in the database layer. It knows all information it needs to do so. No fancy-shmancy validation is needed to have secure database code.