nextguru has asked for the wisdom of the Perl Monks concerning the following question:
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)
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;
|
---|
Back to
Seekers of Perl Wisdom