Safety of concatenating query string

bradcathey has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monastians,

I've always used placeholders, but was wondering about the safety, or not, of concatenating a query statement with column names. So, note the concatenation at the end of $stmt = below:

my $sort_by = ($sort eq 'date') ? 'date_of' : 'user_name';

my $stmt = 'SELECT * FROM time_sheet WHERE user_id = ? ORDER BY '.$sor
+t_by; 
my $user_time = $dbh->selectall_arrayref($stmt, {Slice => {}}, $user_i
+d);
[download]

vs. the long-hand method:

my $stmt;
if ($sort eq 'date') {
   $stmt = 'SELECT * FROM time_sheet WHERE user_id = ? ORDER BY date_o
+f'; 
} else {
   $stmt = 'SELECT * FROM time_sheet WHERE user_id = ? ORDER BY user_n
+ame';
}

my $user_time = $dbh->selectall_arrayref($stmt, {Slice => {}}, $user_i
+d);
[download]

Obviously the first one is a bit more streamlined, but unsure of the safety of it. Thoughts?

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

Comment on Safety of concatenating query string Select or Download Code

Replies are listed 'Best First'.
Re: Safety of concatenating query string by roboticus (Chancellor) on Jun 23, 2011 at 17:10 UTC
bradcathey: If your program is the source of the column names, then there shouldn't be a concern. You typically have problems with SQL-injection issues when a third-party can put in bits of code that can mess up the database. For example, in your first case, your program is supplying the names, so you don't have to worry. But if it were more like: `my $sort_by = <>; my $stmt = 'SELECT * FROM time_sheet WHERE user_id=? ORDER BY ' . $sor +t_by;` [download] Then you're opening the door for someone to hose your database. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]
Re^2: Safety of concatenating query string by bradcathey (Prior) on Jun 24, 2011 at 19:16 UTC
Thanks for the reassurance all. Good to know. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]
Re: Safety of concatenating query string by {}think (Sexton) on Jun 24, 2011 at 10:16 UTC
One VERY MINOR point is that with the placeholder version, your database sees both sort-column statements as being the same statement (ending in ORDER by ?) as opposed to two statements with differing ORDER clauses. One single statement means one single prepare / execution plan, and thus saves time. I must confess, I use BOTH techniques as I see convenient. {}think; #Think outside of the brackets	[reply]
Re^2: Safety of concatenating query string by Jenda (Abbot) on Jun 25, 2011 at 22:07 UTC
I wonder what database would allow "... ORDER BY ?", especially since different values for the order by clause would lead to (quite likely wildly) different execution plans. Jenda Enoch was right! Enjoy the last years of Rome.	[reply]
Re^3: Safety of concatenating query string by {}think (Sexton) on Jun 27, 2011 at 15:36 UTC
Oracle allows it. Right or wrong, it works. {}think; #Think outside of the brackets	[reply]
Re: Safety of concatenating query string by locked_user sundialsvc4 (Abbot) on Jun 28, 2011 at 13:25 UTC
If the column names come only from your program, and if the search criteria etc. are provided to the query by means of parameters, then this technique is safe.