while placeholders are better than literals, you will still end up with potentially hundreds of varieties of this sql depending on the varying number of placeholders used. If you always have the same number or approximately the same number of placeholders, than a series of placeholders with a bind of the array of values is a good choice. If it varies per query, you instead might consider this approach which ensures you only parse one unique query. http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:210612357425 If your in clause has only a few values an the use of an index on the IN column is generally helpful for your query, ensure you read this if you are 9i or earlier to properly set the cardinality for the nested table so that the optimizer chooses the index (if that is helpful to you) dynamic sampling in 10g fixes this. Read about how to use the cardinality hint here to solve this problem if you are 9i: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3779680732446#15740265481549 Job
"CAMPBELL, BRIAN D (BRIAN)" <[EMAIL PROTECTED]> wrote: I believe placeholders (?) could be a better alternative to quote(). Handling of the IN operator was addressed by a thread last October, and additional information like placeholders which allows for possible prepare statement optimization. You can jump in on my contribution if you like, and then work your way through the thread... http://www.nntp.perl.org/group/perl.dbi.users/24638 Aren't archives wonderful? -----Original Message----- From: Ronald J Kimball [mailto:[EMAIL PROTECTED] Sent: Thursday, May 26, 2005 7:06 AM To: 'Jared Still'; [EMAIL PROTECTED] Cc: DBI List Subject: RE: How to store query results in an array? Jared Still [mailto:[EMAIL PROTECTED] wrote: > Here's a fun and slightly obfuscated method to do that: > > my $usql=q{select username from dba_users}; > my $aryRef = $dbh->selectall_arrayref($usql); > my @users = map { $aryRef->[$_][0] } 0..$#{$aryRef}; > my $newSql = q{select from users where username in ('} > . join(q{','},@users) . q{')}; > > print "$newSql\n"; Regardless of the method you use to construct the query, you should not quote the values by hand. This approach will fail if a value contains a single quote, and may make you vulnerable to SQL injection attacks. Instead, either call $dbh->quote() or use placeholders. For example: my @users = map $_->[0], @$aryRef; my $newSql = 'SELECT FROM users WHERE username IN (' . join(', ', map $dbh->quote($_), @users) . ')'; Ronald --------------------------------- Do You Yahoo!? Yahoo! Small Business - Try our new Resources site!
