• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

replace quotes with UTF-8 character

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

0
Aleks
Asked:
Aleks
  • 20
  • 17
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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

0
 
AleksAuthor 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

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if str <> "" and not isNull( str ) then
        str = Replace( str, """", "" )
        str = Replace( str, "<", "" )
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
AleksAuthor 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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
str = Replace( str, """", "&quot;" )
0
 
AleksAuthor Commented:
I'll test when I get back. Thx for the help
0
 
Brad FeatherstoneCommented:
If you are trying to ensure that a string will always display correctly in HTML, what about trying the equivalent of HttpServerUtility.HtmlEncode?
0
 
AleksAuthor 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
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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, """", """""" )
0
 
AleksAuthor 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

0
 
AleksAuthor 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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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
0
 
AleksAuthor Commented:
I'll give that. Try. Why is data not displayed after quotes ?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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?
0
 
AleksAuthor Commented:
It does. So how do all websites approach this issue. Since users can type quotes on any field ?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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.
0
 
AleksAuthor 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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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
0
 
AleksAuthor 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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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
0
 
AleksAuthor 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

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
put it at the end, right after line 13
0
 
AleksAuthor 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
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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
0
 
AleksAuthor 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).
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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?
0
 
AleksAuthor 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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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....
0
 
AleksAuthor 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
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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.
0
 
AleksAuthor 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.
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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
0
 
AleksAuthor 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 ?  ... :)
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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
0
 
AleksAuthor 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
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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">
0
 
AleksAuthor Commented:
Ummm .. it did work  :)  thanks. that's the solution!
0
 
AleksAuthor Commented:
Sorry for the back and forth ...
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 20
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now