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

A couple ways I've implemented the actual text to make it a little cleaner. All very subjective. Qualified by I nearly always format my statements with a here-doc:

my $sql = <<EOSQL;

SELECT  something 
FROM    atable 
WHERE   data IN (?)

EOSQL
[download]

Using baby cart

my $sql = <<EOSQL;

SELECT  something 
FROM    atable 
WHERE   data IN (@{[join ',', ('?') x $size]})

EOSQL
[download]

Using $"

my $sql = do {
    my @holders = ('?') x $size;
    local $" = ',';
    <<EOSQL;

SELECT  something 
FROM    atable 
WHERE   data IN (@holders)

EOSQL
};
[download]

Using sprintf

my $sql = sprintf <<EOSQL, join ',', ('?') x $size;

SELECT  something 
FROM    atable 
WHERE   data IN (%s)

EOSQL
[download]

I've generally been using #2 recently.

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

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

Replies are listed 'Best First'.
Re^3: DBI parameterized database query with comma seperated list by Pope-O-Matik (Pilgrim) on Dec 20, 2015 at 00:26 UTC
Don't do that. Dynamic SQL is bad news. Every RDBMS has this issue, because stored procedures are not able to use a dynamic list. The way to work this out changes by RDBMS, but all of them are ways it split the values in SQL itself. Anyway, a simple answer would be to use a recursive CTE to split the values into separate records. Once that is done, IN() or EXISTS can SELECT from the entire CTE. Here's the basic idea. I quickly looked at mysql functions to write this, but have not tested it at all. Hopefully it shows the basic idea: `WITH RECURSIVE split_csv(x, rest) AS ( SELECT SUBSTRING_INDEX(?, ',', 1), SUBSTRING(?, FROM INST +R(?, ',') + 1) UNION ALL SELECT SUBSTRING_INDEX(rest, ',', 1), SUBSTRING(rest, FROM I +NSTR(?, ',') + 1) FROM split_csv) SELECT something FROM atable WHERE data in (SELECT x FROM split_csv);` [download]	[reply] [d/l]
Re^4: DBI parameterized database query with comma seperated list by kennethk (Abbot) on Dec 21, 2015 at 01:34 UTC
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.	[reply]
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
Re^7: DBI parameterized database query with comma seperated list by Pope-O-Matik (Pilgrim) on Dec 22, 2015 at 04:44 UTC
Re^4: DBI parameterized database query with comma seperated list by Apero (Scribe) on Dec 21, 2015 at 02:06 UTC
In addition to the on-point reply by kennethk, it should be noted that MySQL does not implement the SQL:1999 feature for CTEs, including RECURSIVE expressions. This is a well-known bug/limitation (reported in 2006!) and there appears little interest in fixing it upstream. While CTEs don't appear to be overly beneficial for this problem in terms of SQL input safety, lack of this support is certainly a drawback for those using or considering MySQL as a platform. Given that every other major RDBMS supports CTEs, "Buyer Beware."	[reply]
Re^5: DBI parameterized database query with comma seperated list by Pope-O-Matik (Pilgrim) on Dec 21, 2015 at 11:42 UTC
it should be noted that MySQL does not implement the SQL:1999 feature for CTEs Wow! Didn't know that at all. I just assumed.... Thank you for the heads up! A quick search shows people doing this with functions. It's likely better than doing it in another layer, but certainly not as good as using straight SQL (for both performance and security reasons.)	[reply]