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

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")
  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;

Open in new window

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.

cyberkiwiConnect With a Mentor Commented:
strSQLInsert = strSQLInsert + "'" + txtTitle.Text.Replace("'", "''") + "',";
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
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.
chrisryhalAuthor Commented:

Thought you might shed some light on this one if possible
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.