Correct form to quote strings with (intentional) wildcards?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

There's probably a simple answer to this one, but...
I've got a string that I've read in from a web browser, and I want to
return a list of matching
entries that start with that string.

I want to do something like (Using perl/DBI):

  $sth = $dbh->prepare('SELECT * FROM Entries WHERE name like '?%'");

But that's not going to work, as the placeholder is going to get
replaced with a 'Fred', resulting
in a statement like:

  SELECT * FROM Entries WHERE name like ''Fred'%'

In a nutshell, I want to quote the input string, to protect against sql
injection hacks, but I still want to add a wildcard to the field before
the query.

Is there a simple way of doing this? Currently, I'm using $dbh->quote
to quote the input string,
then manipulating the resulting string to add a '%' wildcard character
before the closing apostrophe,
but that sure feels wrong, and I keep thinking there's got to be a
better way.

Thanks for any answers.

Re: Correct form to quote strings with (intentional) wildcards?

Quoted text here. Click to load it

Try this:
$sth = $dbh->prepare('SELECT * FROM Entries WHERE name LIKE CONCAT(?,

By the way, it's not precisely true that the ? is replaced by the string
'Fred'.  When you prepare a  SQL statement that contains parameters, the SQL
is parsed, optimized, and prepared by the RDBMS, and then it is in an
internal form that is not human-readable.  Then, when you provide a value
via the execute() function, the RBDMS doesn't need to put quotes around the
value or re-parse the SQL; it puts the value directly into that internal
data structure that represents the prepared query, and executes the query.

Bill K.

Re: Correct form to quote strings with (intentional) wildcards?

Thanks Bill, I guess I should have thought of that.


Site Timeline