2

I'm rewriting a .NET application which utilizes Oracle queries, and changing them into SQL Server queries. One of the queries I am rewriting is as follows:

INSERT INTO MY_TABLE (ID, NAME, DESCRIPTION) VALUES (3, q'§Testing some" test' data'"' §', q'§More'"'test data"'''"§')

My guess is that the aim with using the quote operator is to prevent SQL injection, as the values in this query are actually going to be from the client. However, in looking for something in SQL Server, I cannot find anything that would do the job as nicely.

I did find this article here: https://chartio.com/learn/sql-tips/single-double-quote-and-backticks-in-mysql-queries/. However, it seems like the only solution per the article for both single quotes and double quotes is to use the backslash. Looking for other solutions online, I'm getting the same answers.

Is there anything equivalent in SQL Server where I can set up a similar query without having to modify the content of the values being inserted (i.e. putting in backslashes)?

nightmare637
  • 635
  • 5
  • 19
  • 2
    In either DBMS you should not try to escape user input yourself. You should instead use parameterized queries. – Sean Lange Jan 27 '22 at 16:20
  • 2
    Does this answer your question? [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements) – Charlieface Jan 28 '22 at 12:31

1 Answers1

1

The short answer is NO.

The workaround is to use parameterised inputs or to use double single quote '' to escape quotes manually. If you have existing scripts to convert a simple regex could be used to clean them up. In SQL the resuling SQL would be similar to this:

INSERT INTO MY_TABLE (ID, NAME, DESCRIPTION) VALUES (3, 'Testing some" test'' data''"'' ', 'More''"''test data"''''''"')

Which would result in this output:

ID NAME DESCRIPTION
3 Testing some" test' data'"' More'"'test data"'''"

Using parameterised queries negates the need for this type of escaping, but double single quotes are just as much effort than this oracle style of escaping when you construct your scripts, especially if you have automated the scripts.

Chris Schaller
  • 13,704
  • 3
  • 43
  • 81