Updating my database...or not

[molly]

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.

[roboticus]

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

[andreas1234567]

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.

[CountZero]

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.