Re^6: 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);
[download]

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.

Comment on Re^6: DBI parameterized database query with comma seperated list Select or Download Code

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.	[reply]

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.

[reply]