Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Safety of concatenating query string

by bradcathey (Prior)
on Jun 23, 2011 at 16:38 UTC ( #911111=perlquestion: print w/ replies, xml ) Need Help??
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);

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);

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
Re: Safety of concatenating query string
by roboticus (Canon) 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;

    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.

      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
Re: Safety of concatenating query string
by {}think (Acolyte) 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

      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.

        Oracle allows it. Right or wrong, it works.
        {}think; #Think outside of the brackets
Re: Safety of concatenating query string
by 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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://911111]
Approved by Corion
Front-paged by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (5)
As of 2014-09-20 05:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (153 votes), past polls