0

I've been having an issue with C# and SQL queries. I want to update a database entry on an active directory database table, like "domain\user.Account". However, every time I run

sqlCmd.CommandText = "UPDATE " + @"domain\user.Account" + " SET AccountStatus_id=3 WHERE Account_id=" + a.AccountId;"

It comes up with the error of "invalid syntax near '\'". I've tried escaping it through multiple ways, but it always displays the same error.

What can I do here?

Thank you in advance!

Tiago Vieira
  • 43
  • 3
  • 4
    Put `[]` around it. Also read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection for account ID. – mjwills Jul 26 '21 at 11:37
  • 3
    [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/q/7505808/2029983) It's 2021; you should have stopped injecting your parameters decades ago. – Thom A Jul 26 '21 at 11:38
  • 2
    Use [parameterized queries](https://www.dbdelta.com/why-parameters-are-a-best-practice/) so you don't need to escape characters, and other benefits. – Dan Guzman Jul 26 '21 at 11:39
  • To be clear, the suggestions about parameters will help _only_ with the account ID - **not** the table name. – mjwills Jul 26 '21 at 11:45
  • 2
    I must admit, having a table/view called `domain\user.Account` is a very poor choice of an object name. – Thom A Jul 26 '21 at 11:48
  • @Larnu I know, but that's the way that the database is constructed, as it has Active Directory attached for permissions and such. This is a company database, not a personal one – Tiago Vieira Jul 27 '21 at 12:05

2 Answers2

2

Identifiers (such as schema names) with reserved characters need to be marked with [/] in SQL Server, so if this is the Account table in the domain\user schema, then:

UPDATE [domain\user].Account SET ...

However, if this is the domain\user.Account table in the default schema, then:

UPDATE [domain\user.Account] SET ...

In either case, you should absolutely use parameters for the values, i.e.

SET AccountStatus_id=3 WHERE Account_id=@accountId;

Tools like Dapper can make that painless for you, for example:

connection.Execute(@"
    UPDATE /* your choice here! */
    SET AccountStatus_id=3
    WHERE Account_id=@accountId;",
    new { accountId = a.AccountId });
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Yes, I'm aware of parametrization. After so many attempts at this, I was getting paranoid and trying to decompose all this stuff. Nevertheless, your suggestion worked perfectly. Thank you so much! – Tiago Vieira Jul 26 '21 at 11:53
1

If table name contains characters except letters, numbers, and underscores then you need to add [] in SQL Server. If it is mysql, mariadb etc, then need to add ``

In this case table name is domain\user.Account which contains \. Instead of that use [domain\user.Account]

Gaurav P
  • 1,097
  • 1
  • 14
  • 19