2013/3/25 Jo dm <adam_at_jooadam.hu>:
>> 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.
Received on Mon Mar 25 2013 - 10:25:25 CDT
This archive was generated by hypermail 2.2.0 : Mon Mar 25 2013 - 10:25:25 CDT