Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    2

    Unanswered: Creating UPDATE statement when values contain single quote or double quote

    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;

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    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);

  3. #3
    Join Date
    Oct 2010
    Posts
    2

    Appied bonnets

    Quote Originally Posted by spacebar View Post
    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.

  4. #4
    Join Date
    Dec 2009
    Posts
    27
    better idea:

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

    Chanan

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    quote works, but you can let DBI do all the work.

    Code:
    $SQL = qq(UPDATE Flags SET Condition = ?,SkillCoach = ?,Notes = ? WHERE ID = ?);
    
    $dbh->do($SQL, {}, $updatecondition, $updateskillcoach, $updatenotes, $recordid) or die "$DBI::errstr";
    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:

    Code:
    print $SQL."\n";

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •