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

Question

I follow the syntax of

INSERT INTO Table1
VALUES (value1, value2, 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?

Petar Minchev · Accepted Answer · 2011-09-10 19:08:49Z

8

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.

answered Sep 10, 2011 at 19:08

Petar Minchev

47.5k12 gold badges107 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rafał Dowgird Over a year ago

In addition to that, parameterized queries are more likely to benefit from optimization - precompiling, server side caching, etc.

Kasper Hansen Over a year ago

Thank you. This is the obvious answer.

Anthony Pegram · Accepted Answer · 2011-09-10 19:09:34Z

4

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

answered Sep 10, 2011 at 19:09

Anthony Pegram

128k28 gold badges229 silver badges252 bronze badges

1 Comment

Kasper Hansen Over a year ago

I have used them before. But now I understand their purpose.

Manual5355 · Accepted Answer · 2011-09-10 19:08:24Z

1

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.

answered Sep 10, 2011 at 19:08

Manual5355

98210 silver badges27 bronze badges

Comments

Oybek · Accepted Answer · 2011-09-10 19:18:31Z

1

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.

answered Sep 10, 2011 at 19:18

Oybek

7,2945 gold badges32 silver badges50 bronze badges

Comments

Arsen Zahray · Accepted Answer · 2011-09-10 19:15:48Z

-1

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

answered Sep 10, 2011 at 19:15

Arsen Zahray

25.5k51 gold badges143 silver badges234 bronze badges

2 Comments

TrueWill Over a year ago

See Give me parameterized SQL, or give me death. Parameters are not difficult - look at @Anthony Pegram's example. If you find ADO.NET verbose, consider using an ORM.

Oybek Over a year ago

You destroy the purity of the data. Data must be independent from the application that is using it. First name O'Connel must be O'Connel, not O'Connel. It further may be used by desktop application, queried via WSDL/REST API, aggregated and analyzedt, etc.

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related