Saving String with Double and Single Quotes From EF to SQL

Question

The focus of the question is the string text format, everything else is just there for context. The actual string is about 10 pages long, if that matters.

string text = @"
    "" My string ''stringy'' with lots of ''quoties'' "" said Jimmy ''The Jimminator'' Smith.
";

API.Models.Table seedTable = new API.Models.Table()
{
    Created = new DateTimeOffset(DateTime.Now),
    TableText = text
};

db.Table.AddOrUpdate(seedTable);
db.SaveChanges();

Is this the correct way to handle saving a string with single and double quotes from EF to SQL? If not, what's the proper way to do it? db is just our dbContext.

Edit: This might not have been clear from the question. My concern is that when I issue a query in SQL Server or do a SQL command from C#, I cannot enter a string with anything in single quotes without doubling them up. However, my question is whether EF is somehow smart enough to save a string with single quotes or needs to double them up.

Answer 1

Getting from .NET to SQL is EF's problem to worry about. The rules of just what characters are special and how to escape them vary from database to database, but in each case EF has code to handle that.

So you don't need to worry about ' being special in SQL at all.

All you need to worry about therefore is how to write a valid string in .NET. If you use @ before a string to have a verbatim string literal, then all characters are treated as-is, with the exception of " being escaped as "".

Without, you aren't allowed newlines or quote marks, but can escape them using the following escapes:

1. \u followed by four hexadecimal digits: The character with that code-point
2. \U followed by eight hexadecimal digits: The character with that code-point.
3. \x followed by one to four hexadecimal digits: The character with that code-point.
4. \a same as \u0007 (bell)
5. \b same as \u0008 (backspace)
6. \f same as \u000C (form feed)
7. \n same as \u000A (newline)
8. \r same as \u000D (carriage return)
9. \t same as \u0009 (tab)
10. \v same as \u000B (vertical tab)
11. \' same as \u0027 (apostrophe)
12. \" same as \u0022 (quotation mark)
13. \\ same as \u005C ()
14. \0 same as \u0000 (null character)

Not all of these are necessary in strings, so you can use ' instead of \' but they are allowed either as hard to type, hard to distinguish (how to tell a tab from some spaces?) or not allowed in other contexts (you need \' in character literals).

Your example:

string text = @"
    "" My string ''stringy'' with lots of ''quoties'' "" said Jimmy ''The Jimminator'' Smith.
";

Is the same as:

string text="\n    \" My string ''stringy'' with lots of ''quoties'' \" said Jimmy ''The Jimminator'' Smith.\n";

Or perhaps as:

string text="\n\t\" My string ''stringy'' with lots of ''quoties'' \" said Jimmy ''The Jimminator'' Smith.\n";

As it's not clear with SO's markup whether you wanted spaces or tabs after the first new-line.

Either of those are exactly the same, however if the reason you have '' is to escape for SQL, then you shouldn't, leave it to EF to worry about. With that whether you have:

string text = @"
    "" My string 'stringy' with lots of 'quoties' "" said Jimmy 'The Jimminator' Smith.
";

Or:

string text="\n    \" My string 'stringy' with lots of 'quoties' \" said Jimmy 'The Jimminator' Smith.\n";

Is purely a matter of which you find easier to write and read.

Generally, I'd recommend you use the latter form most of the time, but the verbatim (@) form in cases where either there are a lot of new lines in the text, or where there are a lot of \ characters (regular expressions and Windows file paths, for example).

Answer 2

Not having to worry about SQL syntax is one of the major benefits of using EF or any ORM for that matter. If you have a string:

string myString = "Don't worry about single quotes";

When you store that string using EF to the database, it will go in just like that "Don't worry about single quotes".

No other syntax matters either (i.e. <>,%,etc.)