escaping special operators, ', etc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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



/dev/null said:
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


David Thielen said:
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?
Click to expand...
Click to expand...
 
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."
 
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.
 
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."
 
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?
 
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."
 
Back
Top