2013/3/25 Joó Ádám <[email protected]>: >> And how many web forms forget to check the presence of a percent sign >> and are executing SQL searches without cheking it using clauses >> similar to "WHERE table.field LIKE :parameter" by binding directly the >> submitted form value to the "parameter" variable placeholder, ignoring >> the fact that the percent sign in the right operand of a LIKE is >> parsed specially by the SQL engine ? > > Any magic character should be escaped when supplied as a prepared > statement parameter, so the above scenario is unlikely to happen in > any sane database engine.
Did you read ? Even if you bind a variable to the prepared statement, the variable is not necessarily interpreted as plain-text (like in clauses using comparators but as a SQL regular expression (iwht the LIKE or NOT LIKE operators)... But the most common preparation will be to use the plain-text parameter and prepending/appending some other characers to create that SQL regular expression. But many codes do not transform the passed parameter before making such prepending/appending when creating the effective bound parameter of a LIKE/NOT LIKE clause. The characters which could make problems are the percent sign (%), the asterisk (*) and sometimes as well the escaping backslash. Some SQL engines will support more advaned regular expressions. If the web request must be interpreted only as litteral, converting it to a syntax sompatible with the SQL regular expression which will not trigger the special semantics in expressions is still something to not forget, otherwise you could expose in the reult of the request more data than what the user is supposed to be allowed to look at. In addition, it also happens that the operand of the LIKE/NOT LIKE operation is not a bound parameter and not even a web request parameter but the value of a table column : this column may also store litteral plain text containing these special characters. In other words, a "sane" prepared statement does not solve everything (on any "sane" SQL engine)... Generally it is often better to avoid the LIKE/NOT LIKE operations and use an operations such as CONTAINS, IN... which do not require the special SQL regular expression semantics on its textual operands.
