Solved

Handling Special Characters in ASP.NET

Posted on 2004-08-11
8
1,597 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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