INSERT INTO sometable (name) VALUES ('Paul O'Brien');
This is because the single quote (apostrophe) in the middle ends the string, leaving syntactic garbage after it. What you need to do is this:
INSERT INTO sometable (name) VALUES ('Paul O\'Brien');
The backslash in front of the apostrophe lets mysql know that the apostrophe is part of the string (data) and not the end of the string. If you then select this row, you will see that the name is stored as
Paul O'Brien
which is as it should be. Do you see? There is no need for backslashes in the stored string. I think this is what Dreamweaver's automatic escaping does for you.
It looks to me like you are doing extra work to add backslashes to the actual data. My suspicion is that you have written code to put a backslash in front of the apostrophe, then Dreamweaver is putting a backslash in front of both the backslash and the apostrophe. You don't want that. I expect if you looked at your final query, it would look like this:
INSERT INTO sometable (name) VALUES ('Paul O\\\'Brien');
The first backslash tells mysql to treat the second backslash as data (rather than a backslash escape), then the third backslash tells mysql to treat the apostrophe as data (rather than as the end of the string).
If you were to select this row, you would see the name stored as
Paul O\'Brien
which isn't the correct name, so isn't what you want.
Finally, magic_quotes is a PHP feature.
Michael
Matthew Stuart wrote:
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 "'","´" 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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
