Using OR in SELECT statments safely

sz has asked for the wisdom of the Perl Monks concerning the following question:

For security reasons I normally use placeholders in the select statements I execute via DBI (using MySQL).

I'm working on a routine that requires a select statement that looks like this:

SELECT * FROM catalog WHERE item_id='ABC1' OR item_id='EFG2'

The actual statment might need more item_id's. In any case, I can't use a placeholder since they must represent a single value. I can, of course, build the query as a string by tacking on the items I want from @items_to_include but that raises all the issues that drive prudent developers to use place holders in the first place. Besides, it leads to very ugly looking code.

Executing multiple select statements for the sake of using placeholders would be inneficient. Is there an alternative idiom for using OR in a WHERE clause I'm not aware of?

Your help would be most appreciated.

sz

Comment on Using OR in SELECT statments safely

Replies are listed 'Best First'.
Re: Using OR in SELECT statments safely by fokat (Deacon) on Sep 14, 2002 at 20:00 UTC
If MySQL supports the `IN(..)` idiom, you might want to use code such as: `SELECT * FROM CATALOG WHERE item_id IN (?, ?, ?, ...)` [download] Up to your database's limit in the `IN(...)` clause (32 arguments for Oracle, IIRC). In your code, you could simply issue as many `$sth->bind_param()` as needed or call `->execute()` with the required number of parameters. In the case of Oracle, where I needed this for a different reason, the identical arguments to `IN(..)` were optimized away. I would expect the same from MySQL if this is supported. Also do note that `DBD::Oracle` supports placeholders like `:1`, which refer to the first paramenter no matter how many times or where do they appear in your SQL. Check if `DBD::MySQL` also supports this. If you definitely need to resort to "dynamic SQL", then by all means use `->quote()`, as this will correctly quote whatever is passed to you, thus rendering most threats innefective. Another drawback of this, is that you lose the benefit of `->prepare()`. Regards.	[reply] [d/l]
Re: Using OR in SELECT statments safely by FoxtrotUniform (Prior) on Sep 14, 2002 at 20:02 UTC
You might build a query string with placeholders: `my @items = qw(ABC1 EFG2 IJK3); my $cond = join ' OR ', ('item_id = ?') x scalar @items; $dbh->selectall_arrayref( # or whatever "SELECT * FROM catalog WHERE $cond", undef, @items);` [download] In this case, it's a very good idea to log the query string, as well as the values of each placeholder. `-- F o x t r o t U n i f o r m Found a typo in this node? /msg me The hell with paco, vote for Erudil!`	[reply] [d/l]
Re: Using OR in SELECT statments safely by blokhead (Monsignor) on Sep 14, 2002 at 20:02 UTC
I would do something like this: `my @items_to_include = qw/1 11 45 73 27/; my $query = "SELECT * FROM catalog WHERE "; $query .= join " OR ", ("item_id=?") x @items_to_include; my $sth = $dbh->prepare($query); $sth->execute(@items_to_include);` [download] It's not that ugly, IMHO. $query is generated dynamically, and it is always a valid query. Also, there will always be the same number of question mark placeholders as bind variables. And it still has the upside that you get to use placeholders for the (I assume) untrusted data in @items_to_include. blokhead	[reply] [d/l]
Re: Using OR in SELECT statments safely by sz (Friar) on Sep 14, 2002 at 21:27 UTC
Wow, that was fast! Thanks everyone!! I went with a solution similar to what blokhead and FoxtrotUniform suggested. fokat -- Thanks for reminding me about the IN(...) idiom and the positional placeholders. I'll take a look at DBD::Mysql docs and see what is supported. Regard, sz	[reply]