I need a complete list of characters that should be escaped in sql string parameters to prevent exceptions. I assume that I need to replace all the offending characters with the escaped version before I pass it to my ObjectDataSource filter parameter.
+5
A:
No, the ObjectDataSource will handle all the escaping for you. Any parametrized query will also require no escaping.
Andrew Hare
2009-07-09 17:52:03
This is not a yes or no question bro
Slim
2009-07-09 17:57:49
It is to a certain extent. You said "I assume that I need to replace all the offending characters" and this is not true. If you don't need to replace offending characters then you don't need a list of them either.
Andrew Hare
2009-07-09 17:59:31
So you are saying that it is not my assumption?
Slim
2009-07-09 18:01:10
Well it is not for me to make a ruling on *your* assumption as it is yours alone. What I *am* saying however is that your assumption that you clearly stated is incorrect.
Andrew Hare
2009-07-09 18:12:15
Can you or anyone who up voted your answer show me how to do a parameterized filter with a ObjectDataSource?
Slim
2009-07-10 16:38:58
A:
SQL Books online: Search for String Literals:
String Literals
A string literal consists of zero or more characters surrounded by quotation marks. If a string contains quotation marks, these must be escaped in order for the expression to parse. Any two-byte character except \x0000 is permitted in a string, because the \x0000 character is the null terminator of a string.
Strings can include other characters that require an escape sequence. The following table lists escape sequences for string literals.
\a Alert
\b Backspace
\f Form feed
\n New line
\r Carriage return
\t Horizontal tab
\v Vertical tab
\" Quotation mark
\ Backslash
\xhhhh Unicode character in hexadecimal notation
feihtthief
2009-07-09 18:10:55
I am looking for a list of characters that cause a SQL string to malfunction. These all seem to be SQL string friendly.
Slim
2009-07-10 16:53:30
In reference to my last comment. Some of the characters are used for SQL injection attacks but since it is questionable whether or not you can actually achieve one through a ObjectDataSource FilterExpression I'm not worried about them.
Slim
2009-07-12 04:06:53
+1
A:
As others have pointed out, in 99% of the cases where someone thinks they need to ask this question, they are doing it wrong. Parameterization is the way to go. If you really need to escape yourself, try to find out if your DB access library offers a function for this (for example, MySQL has mysql_real_escape_string).
Ringding
2009-07-09 18:38:50
In this case the others are wrong. I am trying to filter not select. It never touches the database so there is no threat of injection attack. Also a ObjectDataSource doesn't offer parameterized filter expressions. It only offers placeholders.
Slim
2009-07-10 16:48:13