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

Reread the 'dynamic' SQL above. Note that the dynamic component is the number of placeholders, and so there is no attack surface exposed. There are safe and robust ways to do dynamic SQL; it's just always important to understand how to fundamentally limit what could possibly be included. I expect your proposal would bypass the Perl escaping layer entirely, thus increasing the number of attack vectors.

<pedantic>Technically, there is additional attack surface, since you could create some kind of denial of service based upon a really long query, but that's really getting into the weeds.</pedantic>

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

Comment on Re^4: DBI parameterized database query with comma seperated list

Replies are listed 'Best First'.
Re^5: DBI parameterized database query with comma seperated list by Pope-O-Matik (Pilgrim) on Dec 21, 2015 at 11:28 UTC
There are safe and robust ways to do dynamic SQL; That's what everyone thinks. And, even it it were true, it only stays true until another feature is added (to the code or the RDBMS.) If you're trying to prove what can and cannot be done, you better be sure you have checked every aspect. However, with properly parameterized SQL, the placeholders are strictly typed as data and cannot be used for anything other than data. Hence, the statement is guaranteed to be safe. This approach enhances security immensely. As a general rule, each layer should handle its own security.	[reply]
Re^6: DBI parameterized database query with comma seperated list by kennethk (Abbot) on Dec 21, 2015 at 23:23 UTC
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.	[reply] [d/l] [select]
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]