Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Updating my database...or not

by molly (Novice)
on Aug 02, 2007 at 22:27 UTC ( #630411=perlquestion: print w/ replies, xml ) Need Help??
molly has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks,

I am very frustrated with a subroutine wherein I am attempting to update a table in my ms access db. Here is the string of code i keep getting error messages on:

sub SearchResultUpdateExpertiseDB { # update SearchResult with info re +trieved from PubMed search my (%InsertComponent) = %{$_[0]}; # dereference the reference to the + %InsertComponent hash my $FinalInsertSQL = ''; my $StartInsertSQL = 'insert into SearchResult('; my $InsertColumnSQL = ''; my $InsertValuesSQL = ' values('; my $counter=0; my $flag=0; my $AtLeastOneValueToInsert=0; my $sth; foreach my $key (keys (%InsertComponent)) { if ($SearchResultsColumnType{$key} eq 'N') { # this is a number - +-> no need for quotes $InsertColumnSQL = "$InsertColumnSQL" . "$key"; $InsertValuesSQL = "$InsertValuesSQL" . "$InsertComponent{$key} +"; $flag = 1; $AtLeastOneValueToInsert=1; if ($debug) {print "SearchResultUpdate LOOP1: Column = $key Colu +mnType = $SearchResultsColumnType{$key} InsertComponent{$key} = $Inse +rtComponent{$key}\n"}; # debug }; if ($SearchResultsColumnType{$key} eq 'T') { # this is text --> ne +ed quotes $InsertColumnSQL = "$InsertColumnSQL" . "$key"; $InsertComponent{$key} =~ s|\'|\'\'|g; # check for single quote +in the string so that it can be escaped for inserting into DB $InsertValuesSQL = "$InsertValuesSQL" . '\'' . "$InsertComponen +t{$key}" . '\''; $flag = 1; $AtLeastOneValueToInsert=1; if ($debug) {print "SearchResultUpdate LOOP2: Column = $key Colu +mnType = $SearchResultsColumnType{$key} InsertComponent{$key} = $Inse +rtComponent{$key}\n"}; # debug }; if (($counter < ((keys (%InsertComponent)))-1) && ($flag == 1)) { $InsertColumnSQL = "$InsertColumnSQL" . ','; $InsertValuesSQL = "$InsertValuesSQL" . ','; $flag = 0; } $counter++; } # close the foreach $InsertColumnSQL = "$InsertColumnSQL" . ')'; # close this part of th +e insert statement $InsertValuesSQL = "$InsertValuesSQL" . ')'; # close this part of th +e insert statement if ($AtLeastOneValueToInsert == 1) { $FinalInsertSQL = $StartInsertSQL . $InsertColumnSQL . $InsertValu +esSQL; if ($debug1 == 1) {print "now updating SearchResults table\n"}; if ($debug == 1) { print "Updating SearchResult: FinalSQL = $FinalInsertSQL \n\n"; +# debug } $sth = $dbh->prepare($FinalInsertSQL); $sth->execute() # Execute the insert or die "Couldn't execute statement: " . $sth->errstr; $sth->finish;

I keep getting errors about uninitialized values, when clearly all my variables are defined at the beginning of the subroutine. The error output also claims that there is a syntax error in my "INSERT INTO" SQL statement. This program worked fine in earlier runs, and though no portions of the code have changed since( beyond adjusting input into the program in a virtually unrelated subroutine), I randomly am getting these errors. I had similar error messages earlier on in the debugging process, and it appeared to take care of itself once I dealt with (seemingly) unrelated formatting issues in my Access db. Are these kind of errors common as false flags that point toward other errors that anyone might be aware of? Any help would be amazing. Thanks.

Comment on Updating my database...or not
Download Code
Re: Updating my database...or not
by roboticus (Canon) on Aug 02, 2007 at 22:54 UTC

    molly:

    If the code hasn't changed, and the data input has, then I'd suspect that you've got a quote in your input data that's munging your SQL statement into something like:

    insert into searchresult values ('foo', 'alphabet'soup', 'gourmand');delete users;')

    Instead of building a string like:

    insert into searchresult values ('a','b','c')

    you should use placeholders and make your sql like:

    insert into searchresult values (?, ?, ?)

    Then instead of executing your SQL with:

    $sth->execute()

    you could use:

    $sth->execute(@ParmList);

    Of course, you'll have to change the code a little and build the @ParmList array. But it'll shield you from problems like you're getting.

    ...roboticus

Re: Updating my database...or not
by andreas1234567 (Vicar) on Aug 03, 2007 at 05:57 UTC
    Read more on Placeholders and Binding Columns in chromatic's article DBI is OK on perl.com. There are both security- and performance-related benefits of using bind variables, particularly when using database engines like PostgresQL, Oracle or IBM DB2.

    Do also consider using stored procedures if your database engine supports it.

    --
    Andreas
Re: Updating my database...or not
by CountZero (Bishop) on Aug 03, 2007 at 06:11 UTC
    Indeed, you should throw out all the routines you use for quoting the data and either use placeholders or the DBI quote-method.

    Or even better, for composing your SQL-statements, think of using SQL::Abstract or go all the way and use DBIx::Class.

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://630411]
Approved by grep
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (10)
As of 2014-07-30 12:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (231 votes), past polls