Re^4: Best practices for closing database connections?

Replies are listed 'Best First'.
Re^5: Best practices for closing database connections? by soonix (Canon) on Mar 17, 2022 at 13:31 UTC
even ignoring hackers and deliberate wrong input: as soon as you have legitimate input such as person names like O'Brien company names like Toys “R” Us (or even the older version Toys Я Us placeholders are way better than coping with the necessary quoting.	[reply]
Re^6: Best practices for closing database connections? by Polyglot (Chaplain) on Mar 17, 2022 at 13:52 UTC
Quotes are escaped. I always thought such was standard procedure. Blessings, ~Polyglot~	[reply]
Re^7: Best practices for closing database connections? by Fletch (Bishop) on Mar 17, 2022 at 17:40 UTC
And that just right there is a defect you've put in your armor needlessly. Rather than placeholder values which never enter the SQL engine's parsing purview you're going to hang your security on quotes "always" being escaped and, more importantly, being escaped correctly (to say nothing of not maliciously being escaped incorrectly). The "standard procedure" should be to pass values outside the context of an SQL statement with placeholders so there's no possibility of the values' contents affecting the parse of the statement itself. The cake is a lie. The cake is a lie. The cake is a lie.	[reply]
Re^7: Best practices for closing database connections? by dsheroh (Monsignor) on Mar 18, 2022 at 09:32 UTC
You thought incorrectly. Using placeholders is the standard practice. Escaping quotes (and, hopefully, other meta-characters) was standard practice for languages, database engines, and database interface layers which weren't capable of supporting placeholders... but such languages, engines, and layers are now relics of a bygone time and have mostly fallen out of use. More to the point, neither Perl, nor MySQL/MariaDB, nor DBI have that defect. You are using a set of technologies which are sufficiently modern to provide end-to-end support for placeholders, thus, using them is the standard practice (and universally-acknowledged Best Practice) for those technologies. So, why are placeholders the preferred practice? Because, when using placeholders, the structure of the query (with placeholders to later insert data values) is presented to the database engine separately from the data values themselves. This makes it absolutely impossible (barring serious bugs in the database engine) for data to be misinterpreted as an SQL instruction. In cases where similar queries are re-used multiple times, it can also provide some performance benefits by allowing you to only do the structural parsing once and then running it with different data values, but this is a secondary benefit, not the primary purpose for using placeholders. Thus, "I'm only going to run this query once" is not a sensible reason to forego their use. When done properly, escaping can provide a good level of protection from SQL-based attacks or SQL parsing errors resulting from strange data values. But doing escaping properly is hard and it can be time-consuming as well. Using placeholders, in contrast, provides absolute protection and is dead easy. The only way to use them incorrectly is to not use them.	[reply]
Re^5: Best practices for closing database connections? by marto (Cardinal) on Mar 17, 2022 at 13:19 UTC
"No one accesses these webpages/queries without logging in, and those logging in are definitely not computer-savvy enough to deliberately inject code such as for dropping tables; much less would they have the desire, seeing as it is their own work at stake." Don't assume the attack vector, e.g. Cross-site_request_forgery, a user click a specially crafted link in an email, say hello to little Bobby Tables. Update: how do placeholders and bind variables make things significantly less efficient?	[reply]
Re^6: Best practices for closing database connections? by Polyglot (Chaplain) on Mar 17, 2022 at 14:06 UTC
I'm not understanding most of your remarks here, and I'm not sure how they would apply in my case. I don't use cookies, and the pages the visitor accesses are behind a login screen. They are not public, nor would an online search find them indexed. As for your question, perhaps I would ask one in return: What would be the advantage of using "study" before a hairy regex if that were the only regex expression in the script and it would only be executed once? My present understanding of placeholders is that they inform the database as to the form or pattern of subsequent queries, essentially providing structure to those queries which are yet to come. But, if this is true, hardly any of my queries will follow the same pattern, and therefore would not be made more efficient by creating a placeholder expression for each one. Blessings, ~Polyglot~	[reply]
Re^7: Best practices for closing database connections? by hippo (Bishop) on Mar 17, 2022 at 14:22 UTC
My present understanding of placeholders is that they inform the database as to the form or pattern of subsequent queries, essentially providing structure to those queries which are yet to come. I think perhaps I see where the misconception is here. What you are describing is query preparation and while that can be done for a single, isolated query it may not be necessary. However, placeholders are also used to safely interpolate data into the content of the current query. So you can use placeholders in a single query without performing explicit preparation. eg. `my $rows = $dbh->selectall_arrayref ('SELECT foo FROM bar WHERE quux = + ?', undef, $unsafe_input);` [download] Always use placeholders. Always. 🦛	[reply] [d/l]
Re^8: Best practices for closing database connections? by Polyglot (Chaplain) on Mar 17, 2022 at 15:11 UTC
Re^9: Best practices for closing database connections? by hippo (Bishop) on Mar 17, 2022 at 15:56 UTC
Some notes below your chosen depth have not been shown here
Re^9: Best practices for closing database connections? by haj (Vicar) on Mar 17, 2022 at 15:59 UTC
Re^7: Best practices for closing database connections? by marto (Cardinal) on Mar 17, 2022 at 15:45 UTC
CSRF can and is exploited to execute functions on logged in systems, say for example a URL in an email linking to an exploitable system. The end user doesn't know, they just click and if they're already logged in the command will run as though they'd been malicious. The point I specifically addressed falls into this category. Not using simple existing methods of coping with this, either CSRF or SQL injection when they exist, I'd safely describe that as not best practice. When it comes to security it's best not to make assumptions. The reason I asked about performance was that my experience is that people who ask such questions tend not to have profiled their application or tuned their database. Your mileage may vary, however Advanced DBI is worth reading, it contains a lot which is well worth working through, including connection caching options, I notice someone had mentioned Mojolicious persistence elsewhere in the thread, it's not specific to this framework. Perhaps of interest: OWASP webgoat. Cross-Site Request Forgery (CSRF) Explained (YouTubes warning). Mojolicious - protect against CSRF other frameworks have their own way of doing this.	[reply]
Re^8: Best practices for closing database connections? by Polyglot (Chaplain) on Mar 17, 2022 at 16:38 UTC
Re^9: Best practices for closing database connections? by marto (Cardinal) on Mar 17, 2022 at 16:40 UTC
Some notes below your chosen depth have not been shown here
Re^5: Best practices for closing database connections? by erix (Prior) on Mar 17, 2022 at 13:06 UTC
What is considered the best practice? Using placeholders is best practice. Arguing against best practices makes no sense.	[reply]
Re^6: Best practices for closing database connections? by Polyglot (Chaplain) on Mar 17, 2022 at 13:56 UTC
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. Blessings, ~Polyglot~	[reply]
Re^7: Best practices for closing database connections? by haj (Vicar) on Mar 17, 2022 at 15:21 UTC
For placeholders, here's my take. Efficiency? I doubt that you'd ever spot a difference. 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. Because Perl culture says so? Well, every other programming language I've been using has the same recommendation: Use placeholders.	[reply] [d/l] [select]
Re^8: Best practices for closing database connections? by erix (Prior) on Jun 16, 2022 at 09:56 UTC
Re^7: Best practices for closing database connections? by cavac (Parson) 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";'`	[reply] [d/l]