Solved

Handling Special Characters in ASP.NET

Posted on 2004-08-11
8
1,599 Views
Last Modified: 2012-06-27
Hi Experts,
I have built a web app that accepts user input and displays it in datagrids etc.  However, if there are any special characters included in the user input the SQL statements will not work. I have read about using a Replace method or Server.htmlEncode but I have no idea how to implement either of those. Can someone please provide me with a good example for INSERT and UPDATE statements in VB.NET that can handle all special characters?  Or is there a better way to do this besides changing the INSERT and UPDATES?

Here is an example from my project:
High points because this is urgent... Thanks :)



Dim SQLCommandText As String
            SQLCommandText = "INSERT into users (Fname, Lname, Email, Username, Userpass) Values ('" + txtFname.Text + "','" + txtLname.Text + "','" + txtEmail.Text + "','" + txtUsername.Text + "','" + txtUserpass.Text + "')"
            dcRegister.CommandText = SQLCommandText
            conRegister.Open()
            dcRegister.ExecuteNonQuery()
            conRegister.Close()
0
Comment
Question by:jcraun
  • 4
  • 3
8 Comments
 
LVL 24

Expert Comment

by:Justin_W
ID: 11778761
To HtmlEncode something, simply use:
   Dim s1 As String = System.Web.HttpUtility.HtmlEncode("abc")
   Dim s2 As String = System.Web.HttpUtility.HtmlDecode("abc")

You might have to import the System.Web namespace.

Note that HTML encoding/decoding doesn't affect some characters that are considered "special" by SQL, though.

This PAQ shows how to handle special characters within your SQL statement (e.g. to prevent SQL injection attacks):
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21052708.html
0
 

Author Comment

by:jcraun
ID: 11778988
Okay.. so sorry if this is a dumb question but in what instance do you use the htmlEncode/decode? Does it solve the problem of a db accepting a last name like O'conner from a SQL insert?

I looked at the link provided above and read the articles and I saw this line:

 ColName LIKE ('%'+Replace(Replace(Replace(Replace(@searchString, '\', '\\'), '[', '\['), '_', '\_'), '%', '\%')+'%') ESCAPE '\')

which I think takes care of most of the trouble-makers but how do you attach the above line to the SQL statement I have in my example?

Or is it better just to write a function that passes the user input before inserting the field into the db that check the strings? And if so what should the function look like?

My final concern is when you use a SELECT statement to populate a datagrid will strings that have been "replaced" appear in the correct format in the datagrid?

Apologies but I need a lot of help on this one I think :)
0
 
LVL 24

Expert Comment

by:Justin_W
ID: 11779016
> in what instance do you use the htmlEncode/decode?
If you want the string "<b>abc</b>" to actually display as code instead of as a bold "abc", you would HtmlEncode the string before using it in a web page's response content.  It has nothing to do with SQL or DB security.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 24

Expert Comment

by:Justin_W
ID: 11779036
For INSERT statements, replacing each apostrophe with 2 apostrophes should work.  When dealing with WHERE clauses, you have to also worry about the _ and % etc.
0
 

Author Comment

by:jcraun
ID: 11779201
Thanks for the quick responses... my concern is (and maybe I don't understand)  if you replace all apostrophes w/ 2 apostrophes when you view the replaced string  in a datagrid will O'conner now equal O''conner?  Do you have any examples of this working?  I don't think I know how to correctly write out the SQL statements adding these conditions of checking for the special characters so that the rest of the application isn't going to break...
0
 
LVL 24

Assisted Solution

by:Justin_W
Justin_W earned 100 total points
ID: 11779265
> if you replace all apostrophes w/ 2 apostrophes when you view the replaced string  in a datagrid will O'conner now equal O''conner?
No.  2 apostrophes is like 2 quotes in a VB string literal or \" in a C# string literal.  It is just the way you have to escape the apostrophe for SQL to understand that it means apostrophe and not "end of string".

E.g.:
  myString = myString.Replace("'", "''")
0
 
LVL 3

Accepted Solution

by:
topless_stang earned 400 total points
ID: 11779343
The easiest and least prone to attack is to use Parameters, such as:
        Dim SQLCommandText As String
        SQLCommandText = "INSERT into users (Fname, Lname, Email, Username, Userpass) Values (@FName, @LName, @Email, @Username, @Userpass')"
        dcRegister.Parameters.Add("@FName", txtFname.Text)
        dcRegister.Parameters.Add("@LName", txtLname.Text)
        dcRegister.Parameters.Add("@EMail", txtEmail.Text)
        dcRegister.Parameters.Add("@Username", txtUsername.Text)
        dcRegister.Parameters.Add("@Userpass", txtUserpass.Text)
        dcRegister.CommandText = SQLCommandText
        conRegister.Open()
        dcRegister.ExecuteNonQuery()
        conRegister.Close()
No replaces necessary since you are not using the user value to build your SQL string.
This also prevents the user from putting in something like FName = "Jim';DROP TABLE tblImportant;" or whatever else they may creatively try...

Displaying the information is another issue, I prefer <code> to make sure the browser displays all the characters.
0
 

Author Comment

by:jcraun
ID: 11786145
Justin W thanks for answering my various questions
topless stang thanks for the example.. that is what I was looking for :)
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

17 Experts available now in Live!

Get 1:1 Help Now