VBScript storing html encode in SQL Server 2008

I'm building a site using VBScript (Classic ASP), something i have always struggled with is storing user data correctly in the database, what I mean is where users enter data that contain @£$%^&*() etc, now i know to use Server.htmlencode when sending the data to be stored in the database, which means that & becomes & the issue I have is that users can edit data, and I don't want & to become & !

So my question, is, what is the correct way to handle this..

Thanks
garethtnashAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
When displaying the data back to the user, you need to decode the data before displaying it. Unfortunately classic asp doesn't have a built in function to do this, so you'll have to code it yourself. Time following function should do what you want:

Function HTMLDecode(sText)
    Dim regEx
    Dim matches
    Dim match
    sText = Replace(sText, """, Chr(34))
    sText = Replace(sText, "<"  , Chr(60))
    sText = Replace(sText, ">"  , Chr(62))
    sText = Replace(sText, "&" , Chr(38))
    sText = Replace(sText, " ", Chr(32))


    Set regEx= New RegExp

    With regEx
     .Pattern = "&#(\d+);" 'Match html unicode escapes
     .Global = True
    End With

Open in new window

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
If you use an html editor like  Tiny MCE orCK Editor you can save html encoded and when you display using <textarea><%=rs("content")%></textarea> the entities will be converted as you expect.
garethtnashAuthor Commented:
Hi,

This is for standard input form items, not text areas. Quick question, is there a way to HTMLDecode using JQuery?

Thanks
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Big MontyWeb Ninja at largeCommented:
the control doesn't matter. it can be either a text box or a textarea

If you wanted to do this client side (which I don't recommend unless you're also validating on the server as well, you could use the unescape function()

you could also try something like this for jquery:

var decoded = $("<input>").html( '<%=encodedData%>' ).text();

Personally, I'd stick with the server side solution
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
There shouldn't be a need to decode.  

If you encoded, it should still display correctly.  

If you you input "0 < 5" and encode it then display what is in the browser <input value="<%=rs("data")%>"> it should be fine.  

Try <input value="0 &lt; 5"> http://jsbin.com/diponalicu/1/edit
garethtnashAuthor Commented:
The reason I ask, is that I'm dynamically populating the form using query like

            $('#Edit_Job_Title').val($(this).data('jobtitle'));

Open in new window


Which loads the value stored in the link job title data attribute into the form input Edit_Job_Title, the value could contain a " if html decoded before it is loaded into the form input..

Thank you
Big MontyWeb Ninja at largeCommented:
did you try using the unescape() function?
garethtnashAuthor Commented:
So,

Doing this server side, presumably, I decode first and then encode?

Sorry, if I'm not making sense here but previously I server.htmlencode on first input, which stored &amp; and then I server.htmlencode on update, that ended up storing &amp;amp;

Presumably now i need to do something like

<%=Server.HTMLencode(HTMLDecode("Value"))%>

Appreciate all your thoughts.

Thank you
Big MontyWeb Ninja at largeCommented:
when you save the data to the databse, you encode it. if you're not working with html code, you should be able to get away with storing the data as is. When you're displaying the data, that's when you would decode it.

give it a shot and see what happens!
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
You only need to encode when saving.   Once you do that, it will show up visually as what you expect.  

dim test
test = Server.HTMLEncode("0 < 5")
response.write test &" <br><pre>"&test&"</pre><br><form method='post' action=''><input name='test' value='"&test&"'><input name='test2' value='0 < 5'><button type='submit'>Submit</button></form>"
if request.form<>"" then
   response.write request.form
end if

Open in new window


See what happens when you run t his then the response from the form.    You should visually see the same thing in the two inputs event hough the contain different data.   That's way you probably don't need a decode function.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garethtnashAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.