Use of ; and " in a text block.

[bwrobo]

I've had a good run of help on this site when I get to an issue I struggle to think through.

I have set-up a database through ASP that allows the site's owner to login into an Admin section and using a WYSIWYG editor dynamically change content on the site. The Editor I am using is TinyMCE.

Where I have an issue is, when the editor uses ' or " within their text blob, I get a MySQL error...

Microsoft OLE DB Provider for ODBC Drivers
error '80004005'
[MySQL][ODBC 3.51 Driver][mysqld-4.1.22-max-log]SQLBindParamete r not used for all parameters
admin/message.asp, line 66

Which I assume is because the first time an apostrophe or quotes is used, MySQL feels that is the end of the statement, and doesn't understand how to handle the rest of the content spewed from the WYSIWYG editor.

My ASP coding to handle the updated text blob is, teh variable Content being the WYSIWYG output, and MessageName, is the section of the site they are editing...
[code=asp]
query="UPDATE message SET Content='"&Cont ent&"' WHERE MessageName='"& Message&"'"
set(upd)=oConn. execute(query)
[/code]
Any help would be much appreciated. Thank You.

[Atli]

Hi.

You are probably right. By creating the query like that any quote-marks would cause syntax problems.
You would need to escape the quotes before you insert them into the query.

Not sure how you would go about doing that in ASP.
I'll move this question over to the ASP forum. I'm sure the experts there will know.

[bwrobo]

OK... researched this one out and found the solution....

The reason for the problem is that the use of ' and " within the string confuses MySQL into thinking the string has ended. The solution is to replace both " and ' with the HTML codes ´ and "

You will get an error if you try to enter in the function

Content=replace (Content,""","& quot;")

To fix this, use the " chr equivialent of chr(34).

So the solution to the problem is....

Content=replace (Content,"'","& acute;")
Content=replace (Content,chr(34 ),""")

[Atli]

Glad you found a solution.
Thanks for sharing it.

Although, ideally, you wouldn't want to put characters specific to your front-end (in your case, HTML) into the database. You would want it to be like the original data.

In PHP, we would escape characters like quotes, so a query would look like:
[code=sql]INSERT INTO tbl VALUE( 'Somebody\'s name');[/code]
The \' would be interpreted as a part of the string, and once inserted, would lose the \ and become just the single-quote.

If I am not mistaken (and please, correct me if I'm wrong here), ASP does this by doubling the quotes, like:
[code=sql]INSERT INTO tbl VALUE( 'Somebody''s name');[/code]
Note that those are two single-quotes, not a double-quote.

Perhaps it doesn't really make much of a difference in this case, but it's worth considering.

[bwrobo]

You are correct, ASP handles escapes characters as double quotes, however, in this case as my web user is updating information that will be directly displayed on the output website, it was simple to just use HTML Characters.