|Syntactic Confectionery Delight|
Re: Best practices and any way to have Perl Tidy clean it upby topher (Scribe)
|on Apr 17, 2013 at 05:20 UTC||Need Help??|
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:
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.