|
in reply to Re^4: Best practices for closing database connections?
in thread Best practices for closing database connections?
"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? |
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.
| [reply] |
|
|
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);
Always use placeholders. Always.
| [reply]
[d/l] |
|
|
I appreciate the explanation and advice.
My foo, bar, and quux, per your example, will vary virtually every time. It's not a matter of only the WHERE clause changing. I'm operating on a variety of tables and requesting various columns from them. This is why placeholders seem impractical to my situation.
Essentially, I have a script in which I have created subroutines to collect each piece of information needed. To query the database, I call a subroutine. The subroutine creates the query statement, then passes it to a connectdb subroutine where that statement, after sanitizing, is sent to database (query execution). The database returns are sent back to the calling subroutine where they are processed as needed based on the expected form of the results. (A subroutine querying for a single piece of data will parse it differently than one expecting multiple columns and rows that need to be returned in table format.)
In terms of security, I cannot help but think that my subroutine system, which provides the foo-bar structure of your example, does essentially the same thing, security-wise, as the placeholder setup.
For example:
sub getBCVfromRecord {
my $table = shift @_;
my $r = shift @_;
my @answers;
my $statement=qq|SELECT Book,Chapter,Verse FROM $table WHERE `Reco
+rdNum`="$r";|;
@answers = query_database($statement,'getBCVfromRecord');
return @answers;
} #END SUB getBCVfromRecord
sub isProjectTable {
my $tbl = shift @_;
my $response = 0;
my $statement = qq| SELECT EditTable from $metatable WHERE TableNa
+me = '$tbl'; |;
my @results = &query_database($statement, 'isProjectTable');
my $result = shift @results;
if ($result eq 'yes') {
$response = 1;
}
return $response;
} #END SUB isProjectTable
sub query_database {
my $statement = shift @_ || '';
my $from_subroutine = shift @_;
my @results = ();
&connectdb($statement, "query_database: $from_subroutine");
while(my @row = $quest->fetchrow_array()) {
foreach my $item (@row) {
push @results, $item;
}
}
return @results;
} # END SUB query_database
sub connectdb {
my $statement = shift @_;
my $incoming_sub = shift @_; #USED ONLY FOR DEBUGGING
$statement = sanitize($statement);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password, {
mysql_enable_utf8 => 1
}) or die "Can't connect to the DB: $DBI::errstr\n
+";
$dbh->{PrintError} = 1;
$dbh->{RaiseError} = 1;
$quest = $dbh->prepare($statement, { RaiseError => 1 }) or die "Ca
+nnot prepare statement! $DBI::errstr\n";
#$questrows = $dbh->prepare("SELECT FOUND_ROWS();") or die "Cannot
+ prepare statement! $DBI::errstr\n";
$quest->execute()
or die qq|\n\n<table width="60%" bgcolor="#DCDCDE" style="mar
+gins:auto"><tr><th> CONNECT DATABASE Statement: </th></tr><tr><td>$s
+tatement</td></tr><tr><td>FROM: $incoming_sub</td></tr></table><p> Er
+ror in database statement! <p>$DBI::errstr<p>$statement\n|;
#$dbh->disconnect(); #THESE LINES HAVE NOT SEEMED HELPFUL
#$dbh->finish();
} # END SUB connectdb
Feel free to clarify what I may still be missing.
| [reply]
[d/l] |
|
|
|
|
|
|
| |
|
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:
| [reply] |
|
|
There's no way to be already logged in on my system. I don't use cookies. If you so much as reload the page, you'll have to login again the way I have it. A link to the URL will force a login. The only way that an exploit could be easily done is to create an imitation on a different website/URL that would emulate my page and entice the user to surrender his or her login credentials (username/password)--and that could then be used to login on the real site by a malicious user. I don't know any way to prevent an attack of this sort, however, even with the best of security practices in place; virtually any site could be spoofed. But the site is hardly much of a target, and would not be worth a hacker's time, as I see it.
Further to this, the site is not using the GET protocol. The URL is always basic, without additional hackable tokens. It's all based on POST.
Though the server itself has been subjected to multiple DoS attacks and hacking attempts over this period, perhaps it is enough that for all the supposed weaknesses in the system, this application has been online for over eight years without a single break-in/hack-in. Nor do I expect any significant trouble in the years to come, barring the site achieves a much greater level of notoriety than it now has (unlikely).
But we digress, and I fear I am still nearly as ignorant about database connections as when I first posted.
| [reply] |
|
|
|
|
|
|
|
