Menu
I was looking for a list of special characters that must be escaped in ms sql server but could not find one and most of answers I saw for the similar questions advised to use Parameterised queries.. which I am already doing but the framework I am using does not does any escaping for me.
Thus I thought I will give a bunch of those a try and see which one fails.... and I tried a simple query
select * from x where value = '<characters>'
in such query I tried almost all the characters I could find on my keyboard and all of them seem to work... besides the Singe Quote.. that one fails.
Thus I want to know the list of characters that are invalid and must be escaped in ms sql server - tsql and do not want to take the risk of just escaping the single quote and leave the rest that could cause trouble
Appreciate your help
801
The escape character (\) needs to be escaped as (\\). The single quote (') needs to be escaped as (\') or ('') in single-quote quoted strings. The double quote (") needs to be escaped as (\") or ("") in double-quote quoted strings. The wild card character for a single character (_) needs to be escaped as (\_).
Use braces to escape a string of characters or symbols. Everything within a set of braces in considered part of the escape sequence. When you use braces to escape a single character, the escaped character becomes a separate token in the query. Use the backslash character to escape a single character or symbol.
You shouldn't be escaping strings in arguments to LIKE predicates - you should be passing them in as proper parameters. Then you avoid any need to escape and you also tend to avoid SQL injection vulnerabilities.
Escape sequences are used within an SQL statement to tell the driver that the escaped part of the SQL string should be handled differently. When the JDBC driver processes the escaped part of an SQL string, it translates that part of the string into SQL code that SQL Server understands.
The only character that needs escaping in a string is a single quote (which is done with two single quotes together). Otherwise, it's a string and t-sql will fuss with it no further.
If you're using a LIKE statement, see this SO topic Escape a string in SQL Server so that it is safe to use in LIKE expression
As an aside, any framework that doesn't let me use parameters, that doesn't properly escape stuff for me, is a hard stop. Trying to sanitize string input manually is like relying on the pull out method; eventually it's gonna get you.
80
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
