Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Handling Special Characters in ASP.NET

Posted on 2004-08-11
8
Medium Priority
?
1,606 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 400 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 1600 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

609 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