A simple SQL problem: Making an SQL insert statement with text string that contain ' characters

I follow the syntax of

INSERT INTO Table1
VALUES (value1, val开发者_如何学运维ue2, value3…)

This has worked fine so far. But now I have some values that contain normal English text like "I'm going home". The ' character ruins the SQL command in C#. I have written the following:

command.CommandText = "INSERT INTO Bio VALUES ('" + name + "','"I'm going home" + "');

evaluates to

INSERT INTO Bio VALUES ('Peter','I'm going home')

which obviously will not work. How do I make sure special character will not ruin the SQL statements?

Use SqlParameter for heaven's sake. Otherwise your program will be vulnerable to SQL Injection. It will also solve your problem with the special characters.

Learn about parameterized queries for your provider. They exists for Odbc, OleDb, Sql, etc.

command.CommandText = "INSERT INTO Bio Values (@name, @text)";
command.Parameters.Add(/* appropriate param type for your provider */); // add for @name, @text, etc.
// execute query

Use two single quotes whenever there is a single quote you want to escape

Also instead of building your queries like this, you should use parameterized queries in a language of your choice. Escaping the characters yourself opens the door for SQL Injections.

Usually you can escape a single quote by screening with another one. For example the following is a valid statement

INSERT INTO myTable (Column1) VALUES ('Hello I''m Jack');

However I suggest you using parameters.

command.CommandText = "INSERT INTO Bio VALUES (@Name, @OtherValue)";
command.Parameters.AddWithValue("Name", name);
command.Parameters.AddWithValue("OtherValue", "I'm going home");

One addition point in favor of using parameters is that you are free from burden of formatting and other stuff. I mean date values, uniqueidentifiers, etc.

I do use

HttpUtility.HtmlEncode(text)

It makes all that SQL injection stuff disappear, and it seems easier than to use parameters. Don't forget to use

HttpUtility.HtmlDecode(text)

to get your input back in the form you received it