0

How can I escape a value for usage in a query to MSSQL Server? I know how it would be possible using JDBC but since I'm using SAP Business One DI API there is no way (I'm aware of) of using prepared statements.

It would be sufficient to know how to escape string values while additionally knowing how to escape identifiers would be nice, too.

Update: After reading New SQL Truncation Attacks And How To Avoid Them it seems sufficient to escape identifiers using [ (and doubling each occurrence of ]) and values using ' (again doubling every occurence of '). A hint to a library whose concern is correct escaping of MSSQL would be nice nevertheless.

ooxi
  • 3,159
  • 2
  • 28
  • 41
  • If I'm understanding you correctly, I think using the `sp_executesql` stored procedure would be helpful: http://stackoverflow.com/a/5139825/926713 – Lilshieste May 05 '14 at 18:06
  • @Lilshieste could you elaborate? To use `sq_executesql` it would be necessary to escape `AAA` in that example since `AAA'` would cause a syntax error, wouldn't it? – ooxi May 06 '14 at 03:40
  • In that example, you don't have to think about escaping anything, because the string `AAA` doesn't contain any quotes/etc. The `sp_executesql` handles the logic necessary to surround the string value with the necessary quotes. Now, if the string contained characters that needed escaping, then you'd need to escape them (e.g., `'AA''A'`). But this makes it a lot easier to think about what needs to be escaped in that case. – Lilshieste May 06 '14 at 04:24
  • But my problem exactly is that I need to escape a string like `AAA'` and don't know how without using JDBC. I have googled a bit and found some hints but to do it right seams quite hard. Therefore I wondered whether or not there is a library. – ooxi May 06 '14 at 04:44
  • A library would be overkill for something like this. Using the example from the other answer, here is how it would look if you wanted to use the string containing a single quote: `EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'''` – Lilshieste May 06 '14 at 12:23

0 Answers0