Re^8: Best practices for closing database connections?

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
[download]

Feel free to clarify what I may still be missing.

Blessings,

~Polyglot~

Comment on Re^8: Best practices for closing database connections? Download Code

Replies are listed 'Best First'.
Re^9: Best practices for closing database connections? by hippo (Bishop) on Mar 17, 2022 at 15:56 UTC
From this code sample I infer that you aren't using strict. This combination of ignoring industry-wide best practice of using strict and placeholders seemed familiar and yes, it turns out that we've trodden this path before. I'm out. 🦛	[reply]
Re^10: Best practices for closing database connections? by Polyglot (Chaplain) on Mar 17, 2022 at 16:08 UTC
You inferred incorrectly this time. The very first line of the file is this: `use strict 'vars';` [download] Blessings, ~Polyglot~	[reply] [d/l]
Re^11: Best practices for closing database connections? by dsheroh (Monsignor) on Mar 18, 2022 at 09:09 UTC
The inference was mostly correct. You're using `strict 'vars'`. There is more to `strict` than just `vars`. You are not using all of `strict` (and, indeed, are not even using the majority of it).	[reply] [d/l] [select]
Re^9: Best practices for closing database connections? by haj (Vicar) on Mar 17, 2022 at 15:59 UTC
This makes me shudder: `$statement = sanitize($statement);` Sanitizing a statement as a whole either means that you actually need to parse the statement, or, more likely, you are making some assumptions about your statements, like "column names don't contain single quotes". This may work for your application as of now, but I'd not recommend it.	[reply] [d/l]


good chemistry is complicated, and a little bit messy -LW
	PerlMonks