-1

The username in the aspnet_users table is BlackR\johnBurns

I'm trying to write a query to get this record where username = but it is flagging

string username1 = @"BlackR\" + johnBurns;
var s = DbContext.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName = " + username1);

this fails saying Additional information: Incorrect syntax near '\'.

Does the @ not cancel this out? Is it because of its a SQL query?

whackamadoodle3000
  • 6,684
  • 4
  • 27
  • 44
John
  • 3,965
  • 21
  • 77
  • 163
  • Learn to use parameters rather than munging query strings. – Gordon Linoff Sep 08 '17 at 02:23
  • an example please? – John Sep 08 '17 at 02:24
  • You forgot using single quotes to pass string variable `username1`, it should be `("Select UserId FROM aspnet_Users where UserName = '" + username1 + "'")`. Again, such hassle may be prevented by using parameterized query. – Tetsuya Yamamoto Sep 08 '17 at 02:26
  • @TetsuyaYamamoto you sir are too kind...have you perhaps seen my Original Q https://stackoverflow.com/questions/46107078/query-returning-wrong-value – John Sep 08 '17 at 02:30
  • @TetsuyaYamamoto No NO **NO!** That is crazy-vulnerable to sql injection attacks! – Joel Coehoorn Sep 08 '17 at 03:49
  • @JoelCoehoorn I know that it's vulnerable to SQL injection, so I recommends parameterized query instead. I just point out how OP mistakenly using query string value concatenation without using single quotes. – Tetsuya Yamamoto Sep 08 '17 at 03:51
  • It should be `"Select UserId FROM aspnet_Users where UserName = @UserName"` (All in the same string constant), and then also have a line like this: `var p = new SqlParamter("@UserName", @"BlackR\" + johnBurns);` that you can pass as an additional argument to `ExecuteStoreQuery()`. – Joel Coehoorn Sep 08 '17 at 03:51

1 Answers1

-1

Assume variable johnBurns store value johnBurns, then your code is similar to this:

string username1 = "BlackR\\johnBurns";
var s = DbContext.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName = BlackR\johnBurns");

The first line, @ will lets you specify a string without having to escape any characters (see this post)

The second line, compiler will try to find the escape character. In this example, it will looking to \j, which is not a valid escape character.

You may find the escape character list here: MSDN

And finally, you may need to add the single quotes for string parameter of SQL, and better to use parameterized query as Tetsuya Yamamoto suggested.
With parameterized query, you don't need to escape the special character, system will help you do that.

Prisoner
  • 1,839
  • 2
  • 22
  • 38
  • How `string username1 = @"BlackR\" + johnBurns;` will turn into `string username1 = "BlackR\\johnBurns";` is very confusing... And how it `\` disappears from second string is even stranger... (Obviously "use parametrized queries" is the answer - so you may want to delete first half of the post or fix it up). – Alexei Levenkov Sep 08 '17 at 03:29
  • @AlexeiLevenkov The `\ ` will be escaped to `\\ `, is that equal to `@"BlackR\"`? And of coz, I'm assume the `johnBurns` store the value `johnBurns`. – Prisoner Sep 08 '17 at 03:34
  • @AlexeiLevenkov I have edit the answer. Meanwhile, I don't think the question is duplicate of the one you marked. It can use `MySqlCommand`, or just continue with `ExecuteStoreQuery` with parameter ([Reference MSDN](https://msdn.microsoft.com/en-us/library/ee358769(v=vs.100).aspx)) – Prisoner Sep 08 '17 at 03:42