asked on

How to handle special characters like "@" and "&" submitted on forms to SQL Server 2005

Hi. We are using ASP forms to let users sign up for a service and create their own usernames and passwords. Some record submits are bombing apparently from users entering characters like "@" and "&" in text fields before they press submit. Should I be handling these characters a certain way? The submit process works fine except for when users create a username that is an email address or put a company name that contains "&". We know this because the users call my client's customer support when they have trouble signing up on my client's website.

Thanks for your help.
Alexis

Jerry_Pang

You should handle the problem in ASP not sql server.

you should send the parameter as a value and seperate it from the SQL command, this should prevent SQL injection.

what's sort of the problems/errors are you having?

Mark Franz

If you are doing an INSERT or UPDATE to a record you can use HTMLEncode() on the string variable before passing it to the SQL. Like this;

varFirstName = Server.HTMLEncode(Request("firstname"))

...

rs("FirstName") = varFirstName

This will encode the special characters to char data, just make sure you use HTMLDecode() on the recordset field before posting the results.

sybe

What is wrong with "@" and "&" characters in passwords? What is the problem with storing these in the database just like any other character?

alexisbr

ASKER

Thanks for your responses. Sybe and Jerry_Panq, the problem is that users are entering "@" and "&" and possibly other special characers in text fields but my ASP pages are not handling those characters correctly. So the records created in SQL server are not correct and the users cannot log in and access their accounts.

mqfranz: Are you saying I should use Server.HTMLEncode on the user input data before I send it to SQL Server each time and then use HTMLDecode whenever I take the data from SQL Server and display it or use it to test a field such as username? How is HTMLDecode used? I tried getting some examples on EE but the ones I found have custom functions written for HTMLDecode that work on specific special characters. I want to handle any special characters, just in case.

Thanks,
Alexis

ASKER CERTIFIED SOLUTION

sybe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Franz

Just reverse the flow;

varFirstName = Server.HTMLDecode(RS("firstname"))

Tomeryos

hi,
I don't entirely understand the problem,
run this little snippet of code to tell if this is what you are looking for

declare @temp nvarchar(128)
set @temp='abc'''
select QUOTENAME(@temp, '''')

alexisbr

ASKER

Sybe,
You were correct. There was nothing wrong with passing those characters to SQL Server. I looked deeper into the issue and discovered one of the values was being inserted into a table in another application we are linked to and that app had a 25 character limit on the field, which I did not check on the form. I have now made sure no users enter more than 25 characters and that solved that problem. The other problem had to do with passing values from one page to the other and the "&" was a problem because the page used "&" to separate values and pass them. I did a replace on "&" to "%26" before going to the next page and then put back the "&" before doing the insert to SQL Server and that solved that problem.

Thanks to all for your suggestions and to sybe for making me realize it was not a sql insert issue.

Alexis

How to handle special characters like &quot;@&quot; and &quot;&amp;&quot; submitted on forms to SQL Server 2005

How to handle special characters like "@" and "&" submitted on forms to SQL Server 2005