Re: apostrophe error

[Matthew Stuart]

Man I have been banging away at this for a week and I am only just 
getting to a point where I am finding a resolution. There seems to be a 
lot of info out there for this kind of thing, but very little on how to 
implement it.

I use ASP and VBScript with Macromedia Dreamweaver so with asp I can 
use the replace() function (PHP has many variations apparently). I have 
found that Dreamweaver during construction of the sql insert statement 
adds some code to supposedly escape quotes - it doesn't, but with a bit 
of jiggery-pokery I have managed to get it to insert the slash. This is 
the code in the insert statement which sits above the html headers etc:

If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then  ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"'","\''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
The problem with this is I have also added it to an update page and 
when the update is submitted, another slash is added - this undoes the 
initial escape of the quote and escapes the first escape... if you see 
what I mean. O\'Brien becomes O\\'Brien.

I have added this directly below the above code in the same sql insert 
statement to try to prevent the second escape but it's not working:

If (MM_altVal <> "") Then
MM_formVal = MM_altVal
ElseIf (MM_delim = "'") Then  ' escape quotes
MM_formVal = "'" & Replace(MM_formVal,"\\'","\''") & "'"
Else
MM_formVal = MM_delim + MM_formVal + MM_delim
End If
I get an error code of:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing 
operator) in query expression '''O\'''Brien'' where id = 27'.

/quotetest_site/TMP29oavr7xvf.asp, line 123

Line 123 is the execute command

I have tried two identical replace values in the one sql insert 
statement and the insert works but gives results of \'O\\'\'Brien\' for 
some reason. I think I am on the right track but can't quite figure it 
out. I don't know if there should be two separate insert statements, or 
two replace() values in one insert statement. For the time being, I 
have managed to over come it (I think) by using replace values of 
"'","&acute;" but this isn't ideal.

If any asp and vb gurus could help me with this I think it would also 
help a few others too.

I have heard of 'Magic Quotes', but got no results on a search of 
mysql.com and I also had a look at the mysql_real_escape_quotes 
function, but I could make head nor tail of it.

Mat