Background story:
I have created a working test case that will lead to a generic update routine for a mysql database having multiple tables. The tables have different structures and being Lazy I didn't want to hard code the column names so I could reuse the code for all tables. This would allow passing the eventual subroutine a table name and hash of values to update a single record. The subroutine is intended for use in a web based application, so I am using parameter binding to avoid SQL injection hacks. On a lark I tried 'eval' of a string containing the names of the variables to insert for the parameters and to my pleasant surprise it worked.

Question: Are there any drawbacks to this method of binding? If so, what techniques should I use instead?

thanks for any pointers
- nextguru (that's NeXT guru, not the next guru) 

#!/usr/local/bin/perl -w
use strict;
use DBI;

my $username  = 'xxxxxx';
my $password  = 'xxxxxx';
my $host      = 'localhost';
my $database  = 'xxxxxx_innoTest';
my $tableName = 'members';
my %memberRecord;

$memberRecord{'nameLogin'} = 'testloginname; DROP TABLE members;';
$memberRecord{'password'}  = 'testpwd';
$memberRecord{'nameFirst'} = 'testfirstname';
$memberRecord{'nameLast'}  = 'testlastname';
$memberRecord{'email'}     = 'testemail@mycompany.com';

my $columnNames;
my $columnBindings;
my $ToBeEvaled;

foreach my $keyName ( keys %memberRecord ) {
    if (not $columnNames) {
        $columnNames = $keyName;
        $columnBindings = '?';
        $ToBeEvaled = "\$memberRecord{'".$keyName."'}";
    }
    else {
        $columnNames = join( ', ', $columnNames, $keyName );
        $columnBindings = join( ', ', $columnBindings, '?' );
        $ToBeEvaled = join(', ', $ToBeEvaled, "\$memberRecord{'".$keyN
+ame."'}" );
    }
}

my $sqlStatement = sprintf("INSERT INTO %s (%s) VALUES (%s);", $tableN
+ame, $columnNames, $columnBindings);

my $dbh = DBI->connect( "DBI:mysql:database=$database;host=$host", $us
+ername, $password )
    or die $DBI::errstr;

my $sth = $dbh->prepare( $sqlStatement )
    or die $DBI::errstr;

$sth->execute( eval $ToBeEvaled )
    or die $DBI::errstr;

$sth->finish();
$dbh->disconnect()
    or warn "Disconnection failed: $DBI::errstr\n";

exit;

[download]

In reply to drawbacks to 'eval' parameters/placeholders/binding in DBI calls to mysql database by nextguru

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.