On Mon, Aug 4, 2008 at 1:36 AM, Larry Garfield <larry@xxxxxxxxxxxxxxxx> wrote:
> Hi folks. I am trying to figure out the best way to handle an interesting
> issue in PDO prepared statements. Consider:
>
> $search = 'mystring';
>
> $stmt = $dbh->prepare("SELECT * FROM mytable WHERE myfield LIKE :myfield");
> $stmt->execute(array(':myfield' => $search . '%'));
>
> The above will search for any record whose myfield entry begins with the value
> in $search. Great. And because it's a prepared statement, the database
> handles SQL injection protection for us.
>
> According to the manual, that is the correct way of handling LIKE statements:
>
> http://us3.php.net/manual/en/pdo.prepared-statements.php
>
> (See Example #6)
>
> But! Now consider this:
>
> $search = "100% pure PHP";
>
> When that is run, the % in the literal string will get interpreted by the SQL
> server as another wildcard character. That is not desired.
>
> IIRC, the way in SQL to circumvent that is to convert "100%" into "100%%".
> However, that does rather defeat the purpose of a prepared statement if I
> have to do my own escaping anyway, does it not? We also cannot use
> $dbh->quote(), as that is intended for cases where you're building a query
> string directly rather than using a prepared statement.
>
> How do other folks handle this issue?
>
> --
> Larry Garfield
> larry@xxxxxxxxxxxxxxxx
>
I don't see any way around it, nor do I see it as defeating the
purpose of prepared statements. I also don't see that this is any
special limitation of PDO when compared to any other database library.
How can any escaping mechanism know that in one case you are looking
for a literal percent sign and in another you are using a wildcard?
You have to provide it with hints, which is what the escape character
does.
Escaping a percent sign inside a string literal used as the pattern
for a LIKE comparison is not the same thing as escaping single quotes
within a string. Most of the time a percent sign in a pattern will be
a wildcard; matching a literal percent sign is usually the exception.
If you fail to escape a percent sign in your condition, the query will
still execute successfully. You may get more (but not less) results
than you desired or intended, but it will still work. However, if you
fail to escape literal delimiters like single quotes, your query will
not (usually) run at all. (And if it does run, it will likely produce
undesired results or side effects, which is the definition of SQL
injection.) As I see it, this is one of the purposes of prepared
statements.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
