Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"
 
PerlMonks  

Re^6: DBI parameterized database query with comma seperated list

by kennethk (Abbot)
on Dec 21, 2015 at 23:23 UTC ( #1150914=note: print w/replies, xml ) Need Help??


in reply to Re^5: DBI parameterized database query with comma seperated list
in thread DBI parameterized database query with comma seperated list

Please review the possible outputs listed in Re^2: DBI parameterized database query with comma seperated list and, noting all these statement sgenerate SQL to be passed to $db->prepare, illustrate a risk factor that is present because of how I've done it.
my $db = DBI->connect(...); my @list = CGI->param('arguments'); my $sql = do { my @holders = ('?') x @list; local $" = ','; <<EOSQL; SELECT something FROM atable WHERE data IN (@holders) EOSQL }; my $query = $db->prepare($sql); $query->execute(@list);
Note I've pulled a whole list of arguments straight from the web, and passed them to the interface unscrubbed.

#11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.

Replies are listed 'Best First'.
Re^7: DBI parameterized database query with comma seperated list
by Pope-O-Matik (Pilgrim) on Dec 22, 2015 at 04:44 UTC

    Please review the possible outputs listed

    Actually, on second look, that's not bad. You're generating placeholders and then passing the values. For an IN() list (as opposed to one that dynamically specifies columns) that ought to work from a security standpoint.

    That being said, the second issue is query caching, which cannot be done when the statement changes at all. In that case, letting the RDBMS do it, may have a (light) performance benefit if the query is executed more than once.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1150914]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (5)
As of 2021-09-23 17:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?