Solved

replace quotes with UTF-8 character

Posted on 2016-09-09
38
68 Views
Last Modified: 2016-09-12
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
Comment
Question by:amucinobluedot
  • 20
  • 17
38 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 41791862
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
 

Author Comment

by:amucinobluedot
ID: 41791866
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41791870
if str <> "" and not isNull( str ) then
        str = Replace( str, """", "" )
        str = Replace( str, "<", "" )
0
 

Author Comment

by:amucinobluedot
ID: 41791877
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41791881
str = Replace( str, """", "&quot;" )
0
 

Author Comment

by:amucinobluedot
ID: 41791894
I'll test when I get back. Thx for the help
0
 
LVL 1

Expert Comment

by:Brad Featherstone
ID: 41791919
If you are trying to ensure that a string will always display correctly in HTML, what about trying the equivalent of HttpServerUtility.HtmlEncode?
0
 

Author Comment

by:amucinobluedot
ID: 41792153
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41792170
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
 

Author Comment

by:amucinobluedot
ID: 41792175
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
 

Author Comment

by:amucinobluedot
ID: 41794120
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794160
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
 

Author Comment

by:amucinobluedot
ID: 41794164
I'll give that. Try. Why is data not displayed after quotes ?
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794177
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
 

Author Comment

by:amucinobluedot
ID: 41794180
It does. So how do all websites approach this issue. Since users can type quotes on any field ?
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794192
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
 

Author Comment

by:amucinobluedot
ID: 41794289
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794320
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
 

Author Comment

by:amucinobluedot
ID: 41794343
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:Big Monty
ID: 41794358
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
 

Author Comment

by:amucinobluedot
ID: 41794365
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794380
put it at the end, right after line 13
0
 

Author Comment

by:amucinobluedot
ID: 41794398
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794431
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
 

Author Comment

by:amucinobluedot
ID: 41794440
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794468
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
 

Author Comment

by:amucinobluedot
ID: 41794471
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794475
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
 

Author Comment

by:amucinobluedot
ID: 41794485
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794514
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
 

Author Comment

by:amucinobluedot
ID: 41794522
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794555
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
 

Author Comment

by:amucinobluedot
ID: 41794583
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 41794705
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
 

Author Comment

by:amucinobluedot
ID: 41795001
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
 
LVL 32

Accepted Solution

by:
Big Monty earned 500 total points
ID: 41795090
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
 

Author Comment

by:amucinobluedot
ID: 41795132
Ummm .. it did work  :)  thanks. that's the solution!
0
 

Author Closing Comment

by:amucinobluedot
ID: 41795133
Sorry for the back and forth ...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now