escaping special operators, ', etc.

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?

--
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/d...paght000002.asp


"David Thielen" wrote:

> 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?
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>

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" wrote:

> 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/d...paght000002.asp
>
>
> "David Thielen" wrote:
>
> > 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?
> >
> > --
> > thanks - dave
> > david_at_windward_dot_net
> > http://www.windwardreports.com
> >

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


"David Thielen" wrote:

> 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?
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>

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.

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



"Kevin Yu [MSFT]" wrote:

> 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."
>
>

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?

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



"Kevin Yu [MSFT]" wrote:

> 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."
>
>

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."