Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Re^6: Best practices for closing database connections?

by Polyglot (Hermit)
on Mar 17, 2022 at 13:56 UTC ( #11142178=note: print w/replies, xml ) Need Help??

in reply to Re^5: Best practices for closing database connections?
in thread Best practices for closing database connections?

I'm asking about best practice in the OP. I'm certainly not against best practice. But why is it best practice? 1) For efficiency in the database operations? 2) For security purposes? 3) Because perl culture says so? 4) Other?

I hope it isn't wrong to inquire.



  • Comment on Re^6: Best practices for closing database connections?

Replies are listed 'Best First'.
Re^7: Best practices for closing database connections?
by haj (Priest) on Mar 17, 2022 at 15:21 UTC
    For placeholders, here's my take.
    1. Efficiency? I doubt that you'd ever spot a difference.
    2. Security? Yes, but it's not as simple as trivial SQL injection. In another message you wrote that "Quotes are escaped". I guess you are aware that different database engines allow different escaping mechanisms? That's why DBI offers the quote method to do the right thing for your particular database engine. You might, at some point in the future, fall in love with a different SQL engine. If you use placeholders, the engine's driver will use its correct quoting. Manually quoting every string with $dbh->quote works, too, but is cumbersome to review and more work if you add columns. Once you got into the habit of using placeholders, you and everyone reading your code can see that quoting has been taken care of.
    3. Because Perl culture says so? Well, every other programming language I've been using has the same recommendation: Use placeholders.

      For placeholders, here's my take.
         1. Efficiency? I doubt that you'd ever spot a difference

      You may not spot a difference with a single query if processing time is less than a millisecond but when repeated, you do notice.

      On one local postgres server (version 15 beta 1), when I compare queries with placeholder versus hard-coded, the placeholder variant is consistently 2 to 3 times faster. (I think the main reason must be that the PREPARE needs only to run once.)

Re^7: Best practices for closing database connections?
by cavac (Vicar) on Mar 21, 2022 at 15:35 UTC

    Because it helps you not forgetting to call quote() manually. Placeholders are the way to go. When you always use them as your SOP (standard operating procedure), you have already made your code safer.

    Unsafe values don't have to come from the attacker directly. They might already be stored in the database, for example by another script that quoted them safely. Now you read the data, DBI "unquotes" the values and when executing another statement with that evil value, you forget to quote it correctly. Bam, you just executed code prepared by the attacker.

    That can easily happen when you modify SQL statements and add a column. If you are NOT using placeholders, you will need to remember to call quote() every single time. Forget just once and it's pretty much over. On the other hand, if you use placeholders, this is done automatically for you.

    perl -e 'use Crypt::Digest::SHA256 qw[sha256_hex]; print substr(sha256_hex("the Answer To Life, The Universe And Everything"), 6, 2), "\n";'

Log In?

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2023-06-01 21:51 GMT
Find Nodes?
    Voting Booth?

    No recent polls found