Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jun 2002
    Location
    Dallas
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy Help with SQL insert statement

    My problem is with a single quotation mark, or apostrophe, and sql to update a database.

    These are the results if a single quote (apostrophe) is used in a field, in this case, O'Hare (oh, and by the way double quotes work fine).

    SQL failed. Error: [-3100] [2] [0] "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''O'Hare')'." insert into Tester ( TestID, FirstName, LastName, RentalLocation) values ( 7, 'Amy', 'Gatewood', 'O'Hare')

    It is imperative that a single quote in a field work. Please help! This is the Perl code (the entire test code, with only a 4 fields in the database - TestID, FirstName, LastName, RentalLocation). If needed, I can attach the test database, and copy the HTML code for full testing. Although at that time you would have to set up the ODBC driver name to TEST, etc. I have added comments to help guide you through the code. Thanx in advance, Amy

    #!C:\Perl\bin\perl.exe -wT
    use Win32::ODBC;
    use CGI ':standard';

    #accept HTML form data

    my $strDSN = "TEST";
    my $strFName = param(txtFName);
    my $strLName = param(txtLName);
    my $strRLoc = param(txtRLoc);
    my $intTestID;

    #initial query to obtain primary key to be used in insert statement
    #TestID is an autonumber - I obtain the highest autonumber #already in the database and add one to that for the next record

    my $strQuery = "select TestID from Tester order by TestID";

    print header;

    if (!($Data = new Win32::ODBC($strDSN))) {
    print "Error connecting to $strDSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
    }

    if ($Data->Sql($strQuery)) {
    print "SQL failed.\n";
    print "Error: " . $Data->Error() . "\n";
    $Data->Close();
    exit;
    }

    while ($Data->FetchRow()) {
    %Data = $Data->DataHash();
    $intTestID = $Data{TestID};
    }

    $intTestID = $intTestID + 1;

    $Data->Close();


    #Now, I form my queries..
    #They are set up so that a blank field may be entered

    my $strQuery1 = "insert into Tester ";
    my $strQuery2 = "( TestID";
    my $strQuery3 = ") values ( " . $intTestID;

    if ($strFName ne "") {
    $strQuery2 = $strQuery2 . ", FirstName";
    $strQuery3 = $strQuery3 . ", '$strFName'";
    }

    if ($strLName ne "") {
    $strQuery2 = $strQuery2 . ", LastName";
    $strQuery3 = $strQuery3 . ", '$strLName'";
    }

    if ($strRLoc ne "") {
    $strQuery2 = $strQuery2 . ", RentalLocation";
    $strQuery3 = $strQuery3 . ", '$strRLoc'";
    }

    $strQuery3 = $strQuery3 . ")";

    $strQuery1 = $strQuery1 . $strQuery2 . $strQuery3;

    if (!($Data = new Win32::ODBC($strDSN))) {
    print "Error connecting to $strDSN\n";
    print "Error: " . Win32::ODBC::Error() . "\n";
    exit;
    }

    if ($Data->Sql($strQuery1)) {
    print "SQL failed.\n";
    print "Error: " . $Data->Error() . "\n";
    print $strQuery1;
    $Data->Close();
    exit;
    }

    #HTML page is now displayed indicating a successful update
    #note that this page does not display if there was an error in the #sql, instead the error will be displayed (which I copied above)

    print << "HTML code0";

    <html>
    <head><title></title>
    </head>
    <body>

    <strong>Update successful!</strong>

    HTML code0

    print end_html;

    $Data->Close();

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dave's right... SQL does work fine if you replace a single quote with two single quotes; upon insertion, SQL magically changes this to a *single* "single quote" - yes indeed it does.

    Just include a function like this into your page (keep in mind this only works in ASP as coded below, but I'm quite sure PHP has a similar method):

    Function NoSingleQuote(StringS) '********************************************

    IF StringS <> "" AND NOT IsNull(StringS) AND NOT IsEmpty(StringS) THEN

    StringS = Replace(CStr(StringS),"'","''")
    NoSingleQuote = StringS

    END IF

    End Function 'NoSingleQuote(StringS) '********************************************

    And when inserting to the database, wrap this function around your variables:

    myString = NoSingleQuote(myString)

    I'm not sure of the syntax in PHP... sorry.
    Last edited by whammy; 06-21-2002 at 02:33 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #3
    New Coder
    Join Date
    Jun 2002
    Location
    Dallas
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Aaagggggghhhh! Of all things. It likes two single quotes, but not one single quote. And why is it that none of my million of computer books talk about this - even the one dedicated just to SQL? Oh, and I'm sure there is a function in Perl that does that, but since I'm pretty new to Perl, I don't know what that is...but I know I can make a little function to validate the form data and replace it for me.

    (I was able to put O''Hare - with two single quotes - in my test data, and it worked)

    Thank you both.

    -Amy

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hehe... you're welcome. I bet there is a really small paragraph somewhere in your SQL book that mentions this vaguely...

    But I don't know for sure, so I'm not betting any money on it!
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

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