Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Handling Special Characters in ASP.NET

Posted on 2004-08-11
8
Medium Priority
?
1,611 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
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.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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 …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

564 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