0

I'm trying to insert strings into a table on my SQL server.

However when inserting a string such as : "I like 'bats'" OR "He is 'cool'" , I get an error that says "incorrect syntax near {bats OR cool}".

How could I insert strings with double single-quotes?

string a = "I am 'coding'";
string UpdateTable = string.Format(@"UPDATE TestTable
                                     SET Column = '{0}'
                                     WHERE Rows = 'Monday'", a);

SqlCommand command = new SqlCommand(UpdateTable, connection);
command.ExecuteReader();

I know I can hardcode the strings like this: "I like ''bats'' " , but I was wondering if there was a programmatic way to solve this.

Any help is appreciated.

EDIT :: I've Parameterized the query. It now looks something like this:

string a = "I am 'coding'";
string UpdateTable = @"UPDATE TestTable
                       SET Column = @a
                       WHERE Rows = 'Monday'";

SqlCommand command = new SqlCommand(UpdateTable, connection);
command.Parameters.AddWithValue("@a", a);
command.ExecuteNonQuery();

this approach works for me. Thank you for the help.

Should I be using a ExecuteNonQuery or a ExecuteReader in this situation, and why?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
leeblee01
  • 1
  • 2
  • 3
    Why not parametrise your query? Then the problem disappears. – Thom A Jul 25 '22 at 14:25
  • 2
    Does this answer your question? [Why do we always prefer using parameters in SQL statements?](//stackoverflow.com/q/7505808) – Thom A Jul 25 '22 at 14:26
  • 1
    Yeah, you want to parameterize your inputs. It will solve this, and make your `INSERT` more secure. It's the strongly recommended way to run sql statements – Jonesopolis Jul 25 '22 at 14:26
  • 4
    That's how SQL injection bugs happen. Imagine what would happen if someone entered `';--` or `'; DROP TABLE USERS;--`. Don't construct SQL queries by concatenating or formatting strings in the first place. Use parameterized queries instead. – Panagiotis Kanavos Jul 25 '22 at 14:27
  • `'I like ''bats'' and I like ''coding'''` will probably working - it gets a little confusing with quotes at the beginning or end, but I think triple quotes in those cases. See [how-do-i-escape-a-single-quote-in-sql-server](https://stackoverflow.com/questions/1586560/how-do-i-escape-a-single-quote-in-sql-server) – topsail Jul 25 '22 at 14:28
  • I might add that Dapper is your friend. Not only does it make parameterizing statements easier, it will also remove much of the boilerplate that's going to follow that `ExecuteReader` call (for queries that actually get results, that is -- this `UPDATE` should be using `.ExecuteNonQuery()`). – Jeroen Mostert Jul 25 '22 at 14:29
  • 4
    @topsail that's bad advice. No amount of quoting is going to fix this fundamental problem. That's a problem in all languages and databases and can't be handled by quoting or sanitizing. The *correct* solution is actually a lot easier to write. – Panagiotis Kanavos Jul 25 '22 at 14:33
  • @Larnu the link you provided does help. Thank you! – leeblee01 Jul 25 '22 at 14:36
  • @leeblee01 you can reduce the code to a single line if you use a library like [Dapper](https://github.com/DapperLib/Dapper): `connection.Execute(sql,new{ newValue="a",filter="Monday"});`. The library will construct *and cache* a command with parameters matching the passed attribute names. It will also manage opening and closing the connection to avoid connection leaks – Panagiotis Kanavos Jul 25 '22 at 14:42
  • `WHERE Rows = 'Monday'` - That should also be turned into a parameter. The reason is that this might change in the future (what if you want Sunday or Tuesday?) By changing it into a parameter sql server could then use a single execution plan for the query instead of 1 per value. It also allows your code to be more flexible (pass in the day of the week as an argument). – Igor Jul 25 '22 at 14:51
  • 1
    You should be using `.ExecuteNonQuery()` because `.ExecuteReader()` is for retrieving the results of a query. Since an `UPDATE` doesn't return anything, constructing a reader is pointless overhead (any attempt to actually use the reader would result in an error, since there are no rows, but the reader must still be disposed). This is exactly why `.ExecuteNonQuery()` exists. – Jeroen Mostert Jul 25 '22 at 14:52
  • Also dispose your connection, command and reader with `using`. – Charlieface Jul 25 '22 at 14:54

0 Answers0