Check to see if a SQL Insert was successful using Perl-CodePudding

I have a script that uses Perl to insert data into a database like this:

$SQL = "INSERT into database (title,time,etc) VALUES ('$title','$time','$etc')";
eval {
    $sth = $dbh->prepare($SQL);
  }; 

  # check for errors
  if($@){
    $dbh->disconnect;
    print "Content-type: text/html\n\n";
    print "An ERROR occurred! $@\n";
    exit;
  } else {
    $sth->execute;
  } # end of if/else
  return ($sth);
}

Despite escaping offending characters, sometimes users copy and paste data from a PDF which causes the insert command to fail. How can I quickly check to make sure that the insert command was successful and if not, display an error?

CodePudding user response：

You need to use prepared statements and bind variables. You should not create SQL statements using input from users.

Read here in the docs about placeholders and bind values.

CodePudding user response：

Check the return value from execute. If it's undef, there was an error.

Some people like to set the PrintError or RaiseError attributes for their handles. The first gives you a warning and the second throws an exception.

But, as Andy said, also use placeholders. These will use the values properly so you don't have to worry about quoting, special characters, and the like. See Bobby Tables for some fun times:

my $SQL = "INSERT into database (title,time,etc) VALUES (?,?,?)";
...
my $rv = $sth->execute( $title, $time, $etc );
if( ! defined $rv ) { warn "Oops! $DBI::Error" }

Those placeholders can also constrain its values (backend support varies). For example, you can specify that it must be an integer. Look for bind_values in the docs.