escaping special operators, ', etc.

[Guest]

Hi;

In a select for Sql Server, the characters '_%[ are all special and have to
be escaped to be used as that character. A couple of questions:
1) Are there any others?
2) Is doubling ' to '' (that is two ' chars, not a single ") standard for
all vendors (Oracle, etc)?
3) I tried using \_ and \\_ and it didn't work. [_] does work but isn't \
also supposed to be an escape char? And if \ is an escape char - how is it
used as a regular char?
4) Does anyone know what the special chars and the way to escape them is for
Oracle (MS and Oracle .NET drivers), OleDbClient, MySqlClient, and DB2Client?

 

[Guest]

If this is to prevent sql injections, you might want to use sql parameters in
..net. Makes the whole thing much easier :

check this article about injection in asp.net (you can use all the
techniques in windows forms apps too...) :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000002.asp

 

[Guest]

Hi;

Actually it's a little different than that. In some cases "A%" is all
records where that column's value starts with A and in other cases it's all
rows where that column is the literal value A%. Parameters can't help here.

thanks - dave

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

If this is to prevent sql injections, you might want to use sql parameters in
.net. Makes the whole thing much easier :

check this article about injection in asp.net (you can use all the
techniques in windows forms apps too...) :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000002.asp

Hi;

In a select for Sql Server, the characters '_%[ are all special and have to
be escaped to be used as that character. A couple of questions:
1) Are there any others?
2) Is doubling ' to '' (that is two ' chars, not a single ") standard for
all vendors (Oracle, etc)?
3) I tried using \_ and \\_ and it didn't work. [_] does work but isn't \
also supposed to be an escape char? And if \ is an escape char - how is it
used as a regular char?
4) Does anyone know what the special chars and the way to escape them is for
Oracle (MS and Oracle .NET drivers), OleDbClient, MySqlClient, and DB2Client?

 

[Guest]

Dave,

I saw this short article today and thought it might be of some use to you:

http://www.devx.com/tips/Tip/31135

Kerry Moorman

 

[Kevin Yu [MSFT]]

Hi dave,

If you use Parameters, the 'A%' string will all be translated to literal
values.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

Hi;

I thought of that. However, there is no client independent way to set
parameters (JDBC does have that - ADO.NET does not).

And, we have cases where the person calling us does not want it to be a
literal value - so then we have to change the select string directly (yes I
know - SQL injection attacks are bad).

So I think we have to do this ourselves which brings me back to the original
questions.

 

[Kevin Yu [MSFT]]

Yes, dave, I agree with you that there is no independent way and you have
to do it yourself.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

So...

In a select for Sql Server, the characters '_%[ are all special and have to
be escaped to be used as that character. A couple of questions:
1) Are there any others?
2) Is doubling ' to '' (that is two ' chars, not a single ") standard for
all vendors (Oracle, etc)?
3) I tried using \_ and \\_ and it didn't work. [_] does work but isn't \
also supposed to be an escape char? And if \ is an escape char - how is it
used as a regular char?
4) Does anyone know what the special chars and the way to escape them is for
Oracle (MS and Oracle .NET drivers), OleDbClient, MySqlClient, and DB2Client?

 

[Kevin Yu [MSFT]]

Hi Dave,

1. There is no other special chars.
2. I'm not quite sure about whether other vendors do the same as TSQL.
3. \ is not an escape char.
4. The escape chars are independent for database engines, not for providers.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."