replace quotes with UTF-8 character

Aleks
Aleks used Ask the Experts™
on
I have an ASP page that updates information into my SQL 2008R2 database.
The use of quotes and other characters is causing issues so I have a function that replaces then with their Unicode character, below is the function:

function clean( str )
    if str <> "" and not isNull( str ) then
        str = Replace( str, "<", "" )
        str = Replace( str, ">", "" )
        str = Replace( str, "&", "&amp;" )
		str = Replace( str, "'", "&apos;" )
        str = Replace( str, "&quot;", "\""" ) 
		str = Replace( str, "&amp;apos;", "'" )
		str = Replace( str, chr(226) & chr(128) & chr(156), "" )    'replaces left smart quote
		str = Replace( str, chr(226) & chr(128) & chr(157), "" )    'replaces right smart quote
		str = Replace( str, chr(226) & chr(128) & chr(153), "" )    'replaces left smart apostrophe
		str = Replace( str, chr(226) & chr(128) & chr(152), "" )    'replaces right smart apostrophe
    end if                
    clean = str
end function

Open in new window


I am trying to apply this into the ASP code but I am doing something wrong that is not working. Here is what I have. I could use some help.
* This is just part of the code, but the part I think is relevant to the issue.

 Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_bluedot_STRING
    MM_editCmd.CommandText = "UPDATE dbo.AdminChecklist SET Docname = ?, Docfrom = ? WHERE docid = ?" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 100, (Request.Form(clean("Docname")))) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 150, Request.Form("Docfrom")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Big MontyWeb Ninja at large

Commented:
change your code to

 Set MM_editCmd = Server.CreateObject ("ADODB.Command")
    MM_editCmd.ActiveConnection = MM_bluedot_STRING
    MM_editCmd.CommandText = "UPDATE dbo.AdminChecklist SET Docname = ?, Docfrom = ? WHERE docid = ?" 
    MM_editCmd.Prepared = true
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 100, (clean(Request.Form("Docname")))) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 150, Request.Form("Docfrom")) ' adVarWChar
    MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) ' adDouble
    MM_editCmd.Execute

Open in new window

Aleks

Author

Commented:
Seems the function is missing code to replace the double quotes  :$   (")  can this be added?

function clean( str )
    if str <> "" and not isNull( str ) then
        str = Replace( str, "<", "" )
        str = Replace( str, ">", "" )
        str = Replace( str, "&", "&amp;" )
		str = Replace( str, "'", "&apos;" )
        str = Replace( str, "&quot;", "\""" ) 
		str = Replace( str, "&amp;apos;", "'" )
		str = Replace( str, chr(226) & chr(128) & chr(156), "" )    'replaces left smart quote
		str = Replace( str, chr(226) & chr(128) & chr(157), "" )    'replaces right smart quote
		str = Replace( str, chr(226) & chr(128) & chr(153), "" )    'replaces left smart apostrophe
		str = Replace( str, chr(226) & chr(128) & chr(152), "" )    'replaces right smart apostrophe
    end if                
    clean = str
end function

Open in new window

Big MontyWeb Ninja at large

Commented:
if str <> "" and not isNull( str ) then
        str = Replace( str, """", "" )
        str = Replace( str, "<", "" )
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Aleks

Author

Commented:
I now have this 

function clean( str )
    if str <> "" and not isNull( str ) then
	    str = Replace( str, """", "" ) 
        str = Replace( str, "<", "" )
        str = Replace( str, ">", "" )
        str = Replace( str, "&", "&amp;" )
		str = Replace( str, "'", "&apos;" )
        str = Replace( str, "&quot;", "\""" ) 
		str = Replace( str, "&amp;apos;", "'" )
		str = Replace( str, chr(226) & chr(128) & chr(156), "" )    'replaces left smart quote
		str = Replace( str, chr(226) & chr(128) & chr(157), "" )    'replaces right smart quote
		str = Replace( str, chr(226) & chr(128) & chr(153), "" )    'replaces left smart apostrophe
		str = Replace( str, chr(226) & chr(128) & chr(152), "" )    'replaces right smart apostrophe
    end if                
    clean = str
end function

Open in new window


With the code above the " are removed instead of replaced for their unicode characters. So it dissapears from the page.
Big MontyWeb Ninja at large

Commented:
str = Replace( str, """", "&quot;" )
Aleks

Author

Commented:
I'll test when I get back. Thx for the help
Brad Featherstone

Commented:
If you are trying to ensure that a string will always display correctly in HTML, what about trying the equivalent of HttpServerUtility.HtmlEncode?
Aleks

Author

Commented:
The last note from bigmonty didn't work it replaced the " with  &quot;   on the page.

This is what I got 

Birth certificate quote ' & &quot; dbl quote

Open in new window


Brad: Not sure how to do that
Big MontyWeb Ninja at large

Commented:
You can use Server.HTMLEncode but then you would have to decode the data,  and there isn't any built in function to do that.

Why do you need to remove the quotes? Are you displaying the data in a text field? If so try this :

str = Replace( str, """", """""" )
Aleks

Author

Commented:
I tried that but didn't work  :(  it still saves one " in the DB and anything after that doesn't show.

In the DB it shows as:   

Birth certificate  dbl "" here

Open in new window

Aleks

Author

Commented:
Any ideas on how to replace the quotes with the UTF-8 character ?  I still have an issue when a  user types quotes in a field and then wants to update it instead of showing the text between the quotes the field shows the data up to before the quotes and nothing after.
Big MontyWeb Ninja at large

Commented:
do you want to just be able to display quotes in a text field? if so, then I would save the data AS IS in the database, then when you go to display it, simply double up the quotes, like so:

<%
str = rs("fieldFromDbThatContainsQuotes")
%>
<input type="text" name="fld1" id="fld1 value="<%=Replace( str, """", """""" )%>" />

this'll allow you to display the data with quotes, and not have to worry about changing the actual data
Aleks

Author

Commented:
I'll give that. Try. Why is data not displayed after quotes ?
Big MontyWeb Ninja at large

Commented:
it's being display because it's considered invalid html, as the first quote that appears in the VALUE of the input field tells the browser that it's done parsing that attribute.

For example, let's say this is your text input field:

<input type="text" name="fld1" id="fld1 value="" />

now, you want to add the value, which is:

Aleks is a "web ninja"!

ASP would parse it out to be:

<input type="text" name="fld1" id="fld1 value="Aleks is a "web ninja"!" />

I bolded out the part that would be considered valid, which is right up to the 2nd quote. Doubling up the quote tells the browser that the quotes surrounding "web ninja" are part of the actual VALUE, and not part of the html markup.

Does that make sense?
Aleks

Author

Commented:
It does. So how do all websites approach this issue. Since users can type quotes on any field ?
Big MontyWeb Ninja at large

Commented:
most sites use some form of the solution I provided above. Others will use built in encoding functions supported by the server side language (in ASP, it would be Server.HTMLEncode). Another method would be to replace each quote with "&quot;" (without the quotes). Server.HTMLEncode may work here for you, I usually stay away from it as it will encode other characters in ways that you may not mean to.
Aleks

Author

Commented:
I tried to replace with &quot;  but it didn't work. it did save  &quot;  in the DB but the it displayed &quot; on the ASP page instead of the quotes. Did I miss doing something else ?  I can send the whole page code if needed.
Big MontyWeb Ninja at large

Commented:
building on my solution above, let's try this:

str = rs("fieldFromDbThatContainsQuotes")
str = Replace( Replace( str, """", """""" ), "&quot;", """""" )

Also, do not save your data in your database in UTF-8 format, you'll may run into problems down the line
Aleks

Author

Commented:
Some of my pages do have this code at the top of the page in order to work properly. 

<%
Response.ContentType = "text/html"
Response.AddHeader "Content-Type", "text/html;charset=UTF-8"
Response.CodePage = 65001
Response.CharSet = "UTF-8"
%>

Open in new window


Ill try the code above, but that means I would have to add that code to a lot of pages. Since users can add quotes pretty much anywhere on any field.
Big MontyWeb Ninja at large

Commented:
for testing purposes, try the code I just sent. If it works, add it to your Clean*( function and that should resolve the issue
Aleks

Author

Commented:
I have this as the function now: 

function clean( str )
    if str <> "" and not isNull( str ) then
		str = Replace( Replace( str, """", """""" ), "&quot;", """""" )
        str = Replace( str, "<", "" )
        str = Replace( str, ">", "" )
        str = Replace( str, "&", "&amp;" )
		str = Replace( str, "'", "&apos;" )
        str = Replace( str, "&quot;", "\""" ) 
		str = Replace( str, "&amp;apos;", "'" )
		str = Replace( str, chr(226) & chr(128) & chr(156), "" )    'replaces left smart quote
		str = Replace( str, chr(226) & chr(128) & chr(157), "" )    'replaces right smart quote
		str = Replace( str, chr(226) & chr(128) & chr(153), "" )    'replaces left smart apostrophe
		str = Replace( str, chr(226) & chr(128) & chr(152), "" )    'replaces right smart apostrophe
    end if                
    clean = str
end function

Open in new window


Where do I put this piece of code ? 

str = rs_document("Docname")

Open in new window

Big MontyWeb Ninja at large

Commented:
put it at the end, right after line 13
Aleks

Author

Commented:
Ok. but ... that line only applies to this one page. Not the entire system so ... here is the relevant code for this page, perhaps this helps.
included is the file to the script we currently have to clean the data.
I added the line at the bottom as recommended.

function clean( str )
    if str <> "" and not isNull( str ) then
        str = Replace( str, "<", "" )
        str = Replace( str, ">", "" )
        str = Replace( str, "&", "&amp;" )
		str = Replace( str, "'", "&apos;" )
        str = Replace( str, "&quot;", "\""" ) 
		str = Replace( str, "&amp;apos;", "'" )
		str = Replace( str, chr(226) & chr(128) & chr(156), "" )    'replaces left smart quote
		str = Replace( str, chr(226) & chr(128) & chr(157), "" )    'replaces right smart quote
		str = Replace( str, chr(226) & chr(128) & chr(153), "" )    'replaces left smart apostrophe
		str = Replace( str, chr(226) & chr(128) & chr(152), "" )    'replaces right smart apostrophe
		str = Replace( Replace( str, """", """""" ), "&quot;", """""" )
    end if                
    clean = str
end function

Open in new window

code-utf8.txt
Big MontyWeb Ninja at large

Commented:
it shouldnt matter, as you would only need to call the clean() function when you're loading up form fields, otherwise you would use the value in the database
Aleks

Author

Commented:
I am confused :$  ... The clean function is now updated. How to display the code and clean it on the page now ?  (Code for the page is attached).
Big MontyWeb Ninja at large

Commented:
where exactly are you trying to display the data? Inside a field or just on the page, like in a DIV or other html element?
Aleks

Author

Commented:
a form field. 
<input name="Docname" type="text" class="form-control" id="Docname" value="<%=(rs_document.Fields.Item("Docname").Value)%>" maxlength="150">

Open in new window

there.
Big MontyWeb Ninja at large

Commented:
well, unless you're using some kind of specialized editor, like TinyMCE or one of its equivalents, you're not going to be able to stylize text in a standard text field....
Aleks

Author

Commented:
Right. So what happens if the user enters something like:  

Documents for "Alien" contact

and hits 'save' ?   the text will save as such in the database, then the page reloads and it reads:

Documents for
Big MontyWeb Ninja at large

Commented:
so it's saving correctly, you just need to double up the quotes when writing the value of that field into the VALUE attribute for the field.
Aleks

Author

Commented:
Is there a way we can build that into a function ?  :)   I think this should be separate from the 'clean' function don't you think ?  this way we apply the new function to any field that would need this done.
Big MontyWeb Ninja at large

Commented:
let's first make sure that's what it is. Test it out on a couple of fields, verify it works, then we'll make a more global function
Aleks

Author

Commented:
I am ok with that. I am just a little lost. I added that one line to the clean function. Should i remove it and if so where do I add the code in the asp file ?
Can we start this from scratch ?  ... :)
Big MontyWeb Ninja at large

Commented:
let's do the following as a test. create your text input like so:

<input name="Docname" type="text" class="form-control" id="Docname" value="<%=(Replace( rs_document.Fields.Item("Docname").Value, """", """""" ))%>" maxlength="150">

the quotes should now appear
Aleks

Author

Commented:
I tried but the text still gets cut off.

So I added this to the clean function:   str = Replace( str, """", "''" )
it replaces the quotes with two single quotes  :$  looks close to the quotes
Web Ninja at large
Commented:
i just tested the following and it worked...

<input name="Docname" type="text" class="form-control" id="Docname" value="<%=(Replace( rs_document.Fields.Item("Docname").Value, """", "&quot;" ))%>" maxlength="150">
Aleks

Author

Commented:
Ummm .. it did work  :)  thanks. that's the solution!
Aleks

Author

Commented:
Sorry for the back and forth ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial