Hello all,

I've got a quick question about how to escape single quotes for insertion/updating an MS Access db. Coming from a mySQL background, my natural inclination is to use addslashes() but do you think that's work? No sir!!!

Here's the db schema:

artist -> text
album -> text
song -> text
bitrate -> text
cat -> text
sub_cat -> text
location -> text
id -> auto_number (primary key)

and the data:

artist -> 311
album -> Soundsystem
song -> Can't Fade Me
bitrate -> High VBR
cat -> Full Albums
sub_cat -> 0 - 9
location -> CD 01

Trying to use the following statement:

$sql = "UPDATE mp3 SET artist = '311', album = 'Soundsystem', song = 'Can\'t Fade Me', bitrate = 'High VBR', cat = 'Full Albums', sub_cat = '0 - 9', location = 'CD 01' WHERE id = 4107";

gives me a 37000 error (missing operator at 'Cant\' Fade Me').

Placing the data strings inside double quotes instead of singles seems to make the Microsoft SQL Engine think that no data has been provided (tells me that it's expecting 7 parameters). Changing "Can\'t Fade Me" to "Cant Fade Me" removes all problems and updates successfully. The same issues arise for record inserts. Any ideas on what's happening here and how I can resolve it? Hmmm... BTW, I don't think it can't have anything to do with being url encoded by a GET or POST operation since the app in question is a PHP-GTK+ app and therefore HTML independent. Thanx for any help!!

Cheers,

Geoff A. Virgo

    Try using doubled single-quotes ('') for embedded single-quotes in your SQL literals. (MSDN seems to imply that this will work.) If that doesn't work, here's one thing to try (it's an ugly hack, but it will work):

    When inserting, replace all embedded single-quotes with backquotes `. When you query, replace all backquotes in the result with single-quotes.

      Thanx man,

      I'd forgotten 'bout double piping (I believe Oracle supports this as well as normal escape-slashing). Everything works properly now.

      Cheers,

      Geoff A. Virgo

        Since you where able to help out eariler on today, thought you might be able to answer this question too. How can I make PHP use File DSN's to create connections? Using User and System DSN's is no prob., but since the app is going to be a distributed client-side, I'd prefer not to have to make people create there own dsn's before they can connnect. As a collarary to that, do you know if there are any other files besides odbcjt32.dll which are needed to create Access connections? Thanx for your help!!!

        Geoff A. Virgo

          10 months later

          I fixed this problem this morning using PHP 4.12.

          Do this to your variable before you run the SQL command

          $Address = ereg_replace("'","''",$Address);

          it will replace the single ' with double '' which should work with access 2000

            Write a Reply...