larry

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.pre...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@garfieldtech.com

per

Depends on what you perceive the purpose of the prepared statement to
be :-) In this context, I tend to think of performance only. Which is
generally why I can't be bothered with prepared statements in php.


/Per Jessen, Zürich

larry

Actually in most cases in PHP you don't get much performance. What you do get is added security, because prepared statements are cleaner than cleaner and more reliable than string escaping. Of course, then we run into the % problem above.

--Larry Garfield

per

I don't really buy that - the string escaping is just a call to a mysql
API, surely that's perfectly reliable.

How do you see prepared statements being cleaner and more reliable?


/Per Jessen, Zürich

aballard

True. I wish PDO would add an option of creating a parameterized query
WITHOUT preparing it, at least for SQL Server. Why? There is overhead
to creating the statement that way. I prefer using the "prepared
statement" method as it decreases the exposure and risk to SQL
injection.

I'd like to see an option like the Microsoft ADO library so that I can
prepare the statement if I will be running it several times with
different parameter values each time, or choose not to incur the
overhead if I'm only going to run a statement once.

Andrew

larry

I've solved that at least for the given page request with a caching layer on top of PDO. It caches and reuses the statement objects. The problem is the issue with LIKE as described above, which I still haven't figured out yet.

--Larry Garfield

adam

Larry,

I agree that having to escape values in a stored procedure does run counter
to expectations. It's likely other developers have the potential for
short-circuiting their LIKE conditions without realizing it.

I've dealt with this issue, too, and haven't been especially pleased with
any of the solutions I've undertaken. Recently, I've been avoiding LIKE
conditions and using INSTR, LOCATE, CHARINDEX, etc. to avoid the potential
for unescaped wildcards.

Adam

larry

Hm. So your solution is "don't use LIKE"? I can't say I'm wild about that. :-/

--Larry Garfield

adam

Like I said, I'm not 'especially pleased' with any idea up until now. I'm
certainly open to any other ideas.

Adam

asnyder

Depending on what database you're using you might be able to use LIKE
while maintaining security. For instance PostgreSQL has the functions
quote_ident(), and quote_literal() which would allow you to use LIKE
since these functions would automatically make the string suitable for
comparison.

So for instance in a stored procedure in PostgreSQL you could do:

SELECT * FROM sometable WHERE quote_ident(column) LIKE quote_ident($1);

Note that in the above line $1 would be the first parameter of the
stored procedure.

-Asher