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~

In reply to Re^8: Best practices for closing database connections? by Polyglot
in thread Best practices for closing database connections? by Polyglot

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.