Helpful Information
 
 
Category: Server side development
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();

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.

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

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! :D










privacy (GDPR)