CODEWITHSUNDEEP
c#sql-server-ce
touyets
touyets

Reputation: 1325

Entering data with a ' (apostrophy) into SQL Server CE table

How could I go about entering data such as "John's shoe" into a SQL Server CE table (version 3.5) as the insert statements and so forth do not accept the use of the ' apostrophe in a statement such as the following statement:

INSERT INTO ShowsDB(Item) VALUES('+ "John's shoes" + "')

Upvotes: 0

Views: 158

Answers (2)

Billy Mailman
Billy Mailman

Reputation: 272

Standard SQL escaping is that quotes should be doubled, so your query becomes

INSERT INTO ShowsDB(Item) VALUES('+ "John''s shoes" + "')

Which is perfectly fine for things like running SELECT statements against a copy of the DB to diagnose problems, but should NEVER be used in actual production systems. Parameterized queries are your friend.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500675

You must avoid putting your data directly into your SQL, basically. Use parameterized SQL instead:

// I'm assuming you're opening the connection already
string sql = "INSERT INTO ShowDB(ITEM) VALUES (@Name)";
using (var command = new SqlCommand(sql, connection))
{
    command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = "John's shoes";
    command.ExecuteNonQuery();
}

Using parameterized SQL has three benefits:

  • Avoids SQL injection attacks
  • Avoids unnecessary and dangerous string conversions (particularly for date/time types)
  • Separates the "code" (SQL) from the data (parameters) which makes it easier to understand

Upvotes: 7

Related Questions