Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re: Best practices and any way to have Perl Tidy clean it up

by topher (Scribe)
on Apr 17, 2013 at 05:20 UTC ( #1029058=note: print w/ replies, xml ) Need Help??


in reply to Best practices and any way to have Perl Tidy clean it up

I think everyone has explained well why perltidy isn't touching the SQL code inside, as it is a quoted string. So, I'm going to offer some related advice regarding best practices with SQL.

Feel free to ignore these suggestions if you aren't interested.

If I were doing similar code, I'd do something along the lines of:

my %sql = foo_select => qq{ SELECT t.id, t.emp_name, t.emp_email, t.req_status, t.date_req +uested, t.mngr_email, t.date_submitted, ( SELECT GROUP_CONCAT( (case when req_status = 'Approved' +then emp_name end) separator ', ') FROM requests AS s WHERE s.date_requested = t.date_requested ) AS approve +d FROM requests t WHERE req_status = 'Pending' AND mngr_email = ? AND date_requested >= ? ORDER BY emp_name, date_requested }; my $sth = $dbh->prepare($sql{foo_select}); $sth->execute($filter, $date);
There's a couple of things I want to note. First, I'm a believer in defining all of your non-trivial SQL code together. You've got one spot to check for anything SQL, and it makes it much easier to abstract the SQL code out if you need to later (for example, to add support for a different database).

The more important bit though, are the question marks instead of the explicit variables in the SQL string. These are placeholders (or bind variables). There are a couple very good reasons to use them.

The first reason is to protect yourself against Bobby Tables and related SQL injection attacks. Unsanitized database inputs are a significant and serious source of problems.

The second reason is for performance. This doesn't matter as much if you only call the statement once, for any query that gets run repeatedly, you can potentially get a performance boost (or remove a performance penalty) by using bind variables.

When you prepare a statement, the database (in better DBs) will take that query and do their initial processing on it. This includes parsing the query, analyzing execution paths, and developing the optimized query plan. This can be a non-trivial amount of effort for the database. When you embed Perl variables directly in your SQL, each statement reaches the DB as a new and unique SQL query, forcing the DB to do all of that work each time. When you use bind variables, the database can process the query once, and then cache the results. You can then execute that query with different values repeatedly without having to repeat the pre-processing step.

Christopher Cashell


Comment on Re: Best practices and any way to have Perl Tidy clean it up
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1029058]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (19)
As of 2014-07-23 15:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (145 votes), past polls