Hello. I am trying to figure out the correct syntax for an SQL statement to UPDATE a table with a value that contains a quote or apostrophe. I am pasting a snippet below. I have included in my code substitute reg expressions to escape quotes and apostrophes. I get a software error whether or not I include the escapes. The error: Syntax error (missing operator) in query expression
Thank you for looking.

$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=\\\\cessna3\\dept425\\YellowFlag\\atdf lags.mdb')
or die "Can't open the database." ;

$updatecondition =~ s/(['"])/\\$1/g;
$updateskillcoach =~ s/(['"])/\\$1/g;
$updatenotes =~ s/(['"])/\\$1/g;

$SQL = qq(UPDATE Flags SET Condition = '$updatecondition',SkillCoach = '$updateskillcoach',Notes = '$updatenotes' WHERE ID = $recordid);

$dbh->do($SQL) or die "$DBI::errstr";


$dbh->disconnect;

Show what $SQL evaluates to right before you execute it:

Code:

$SQL = qq(UPDATE Flags SET Condition = '$updatecondition',SkillCoach =  '$updateskillcoach',Notes = '$updatenotes' WHERE ID = $recordid);

I don't know how to do that. I think I got it working anyway. I ended up escaping the apostrophe with another apostrophe and I was incorrect in thinking that a double quote needs escaped. So it is updating correctly for now. Thanks for looking.

better idea:

check cpan using DBI for quote method:
DBI - search.cpan.org

Chanan

quote works, but you can let DBI do all the work.

Read about placeholders and bind values here. There's also a section describing how the do method works.

To print the SQL you're trying to pass, just: