string format and single quote

Question

I am using a string format with multiple lines of text and one single quotes is a part of that text. The code is like

     string _query = @"{ts '{0}-{1}-{2} 00:00:00'}
                                 ";
              _query = string.Format(_query, DateTime.Now.Year, DateTime.Now.Month, 25);

But the string format return exception as

  Input string was not in a correct format.

How can we add single quote in a multiline text along with string format

Use parameterized SQL queries. Don't build queries like the above. Parameterized SQL queries = no SQL injection, and no problems with quotes in your strings. — ProgrammingLlama, Jan 28 '19 at 06:08
And for the love of Pete, please don't use implicit joins. They are outdated for over 25 years now. — Zohar Peled, Jan 28 '19 at 06:13
Agreed that the sql might not be optimal. But please help to find out what's wrong with string format — Sebastian, Jan 28 '19 at 06:16
Possible duplicate of [How to escape braces (curly brackets) in a format string in .NET](https://stackoverflow.com/questions/91362/how-to-escape-braces-curly-brackets-in-a-format-string-in-net) — Wai Ha Lee, Jan 28 '19 at 06:24
@JibinMathew It's not that "the sql might not be optimal" - it's that the sql is dangerously bad. The risk of SQL injection is a really big risk and the solution is really simple - far too simple to not use. btw, your string.format might is wrong in another way - it will return a single digit for a month 9 out of 12 months. You should use `{0}-{1:00}-{2}` as a format string. — Zohar Peled, Jan 28 '19 at 06:32

score 2 · Accepted Answer · answered Jan 28 '19 at 06:19

As mentioned in comments, please avoid building query using string, instead use Parameterized SQL queries.

Regarding your comment on why string Format fails, its because of presence of curly braces in beginning and end of string.You need to escape it. To escape curly braces use "{{" and "}}".

string _query = @"{{ts '{0}-{1}-{2} 00:00:00'}}";

string format and single quote

1 Answers1