Using Special Character Strings With SQL And C#

Trying to insert some records with my ASP.NET app and noticed that I can't insert special characters.   An example would be if I inserted "President" into the <TABLE>.Title column it works..............but if I type in "President's" its fails because of the " ' "

I know in VB.NET I used to do a

              strSQLInsert = strSQLInsert & "'" & Trim(txtTitle.Text) & "',"

and thing I remember it working correctly.  Any insight with C# is greatly appreciated.
strSQLInsert = "INSERT INTO <TABLE>(Title,Address,City,State,Description,Type,UserID,Approved) values (";
            strSQLInsert = strSQLInsert + "'" + txtTitle.Text + "',";
            strSQLInsert = strSQLInsert + "'" + txtAddress.Text + "',";
            strSQLInsert = strSQLInsert + "'" + txtCity.Text + "',";
            strSQLInsert = strSQLInsert + "'" + cmbState.SelectedValue + "',";
            strSQLInsert = strSQLInsert + "'" + txtDescription.Text + "',";
            strSQLInsert = strSQLInsert + "'" + cmbDestinationType0.SelectedValue + "',";
            strSQLInsert = strSQLInsert + "'" + siteUser.UserId + "',";
            strSQLInsert = strSQLInsert + "'" + false + "')";

Open in new window

LVL 2
chrisryhalAsked:
Who is Participating?
 
Todd GerbertConnect With a Mentor IT ConsultantCommented:
Ideally, you should use parameters to prevent things like this as well as SQL injection.  For example, if I'm a malicious user and I type BadBoy');DROP <TABLE> into a text box, then the SQL statement you'll end up sending to the SQL server would be something like INSERT INTO <TABLE> (Title,Address,City) values ('BadBoy'); DROP <TABLE> - which is actually two statements - one malformed insert and one that totally deletes your table.
using (SqlConnection cn = new SqlConnection("Connection String")
{
  cn.Open();
  using (SqlCommand cmd = cn.CreateCommand())
  {
    cmd.CommandText = "INSERT INTO <Table> (Title, Address, City) VALUES (@Title, @Address, @City)";
    cmd.Parameters.AddWithValue("@Title", TitleTextBox.Text).DbType = DbType.String;
    cmd.Parameters.AddWithValue("@Address", AddressTextBox.Text).DbType = DbType.String;
    cmd.Parameters.AddWithValue("@City", CityTextBox.Text).DbType = DbType.String;
    cmd.ExecuteNonQuery();
  }
  cn.Close();
}

Open in new window

0
 
Daniel Van Der WerkenConnect With a Mentor Independent ConsultantCommented:
The single-quote is simply two of them together.  so, to insert "Presendent's" use "President''s" with two single quotes.

0
 
cyberkiwiConnect With a Mentor Commented:
strSQLInsert = strSQLInsert + "'" + txtTitle.Text.Replace("'", "''") + "',";
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
cyberkiwiConnect With a Mentor Commented:
Hi there,

Basically, you replace all single quotes ' with two single quotes '' in the user input.
You are prone to SQL injection though, so be warned!
Better to use parameters.

Do the same for all your inputs
0
 
chrisryhalAuthor Commented:
I converted my code to accomadate the Params since you guys put the fear of god into me and I appreciate the input and suggestion.   Thanks so much for all your help.
0
 
chrisryhalAuthor Commented:
tgerbert:  

Thought you might shed some light on this one if possible

http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_26091280.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.