Nonprintable / encrypted characters is not found

[Anita]

Hi All

I have a table with one column :

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

The column a is filled with encrypted data
(contains control and extended characters).
On my test, the select statement for one row
in table test2 does not always work sucessfully.

Below is my unsucessful statement for the second
row followed by 9 rows data. Each row is displayed
in 2 versions, text and VB ascii code.

select * from test2
where a = '\0<[\
|^[\]}{;\'

----------
Row-1
%;>.[
,)]/\-,=/
37;59;62;46;91; 10;44;41;93;47; 92;45;44;61;47;
Row-2
\0<[\
|^[\]}{;\
92;48;60;91;92; 10;124;94;91;92 ;93;125;123;59; 92;
Row-3
\0<[\,)?{=\?
92;48;60;91;92; 11;44;41;63;3;1 23;127;61;92;63 ;
Row-4
\0<[\\%:_`-]_
92;48;60;91;92; 11;92;37;58;95; 96;45;5;93;95;
Row-5
\0<[\\^:&}{;\
92;48;60;91;92; 11;92;94;58;38; 7;125;123;59;92 ;
Row-6
\0[*_1>\_\}{@+
92;48;91;42;95; 49;62;92;95;92; 7;125;123;64;43 ;
Row-7
].>^/.]-=}<)^&
93;46;62;94;47; 46;8;93;45;61;1 25;60;41;94;38;
Row-8
]0_^{
}^~_|~{^_
93;48;95;94;123 ;10;125;94;126; 95;124;126;123; 94;95;
Row-9
{
}{31{2|02~||
123;10;125;123; 127;51;49;123;5 0;124;48;50;126 ;124;124;
-------------

I have 3 questions for all of you :
- Why SQL2000 can not find the second row (certain row)?
- Can SQL2000 handle character 0 to 255 ?
- Does my encrypted method produce bad data for SQL2000?

Thanks in advance

Anita Hery





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

Re: Nonprintable / encrypted characters is not found


"Anita" <anonymous@devd ex.com> wrote in message
news:4029728d$0 $196$75868355@n ews.frii.net...[color=blue]
> Hi All
>
> I have a table with one column :
>
> CREATE TABLE test2
> ( a char(15)
> primary KEY CLUSTERED )
>
> The column a is filled with encrypted data
> (contains control and extended characters).
> On my test, the select statement for one row
> in table test2 does not always work sucessfully.
>
> Below is my unsucessful statement for the second
> row followed by 9 rows data. Each row is displayed
> in 2 versions, text and VB ascii code.
>
> select * from test2
> where a = '\0<[\
> |^[\]}{;\'
>
> ----------
> Row-1
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91; 10;44;41;93;47; 92;45;44;61;47;
> Row-2
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92; 10;124;94;91;92 ;93;125;123;59; 92;
> Row-3
> \0<[\ ,)?{=\?
> 92;48;60;91;92; 11;44;41;63;3;1 23;127;61;92;63 ;
> Row-4
> \0<[\ \%:_`-]_
> 92;48;60;91;92; 11;92;37;58;95; 96;45;5;93;95;
> Row-5
> \0<[\ \^:&}{;\
> 92;48;60;91;92; 11;92;94;58;38; 7;125;123;59;92 ;
> Row-6
> \0[*_1>\_\}{@+
> 92;48;91;42;95; 49;62;92;95;92; 7;125;123;64;43 ;
> Row-7
> ].>^/.]-=}<)^&
> 93;46;62;94;47; 46;8;93;45;61;1 25;60;41;94;38;
> Row-8
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123 ;10;125;94;126; 95;124;126;123; 94;95;
> Row-9
> {
> }{31{2|02~||
> 123;10;125;123; 127;51;49;123;5 0;124;48;50;126 ;124;124;
> -------------
>
> I have 3 questions for all of you :
> - Why SQL2000 can not find the second row (certain row)?
> - Can SQL2000 handle character 0 to 255 ?
> - Does my encrypted method produce bad data for SQL2000?
>
> Thanks in advance
>
> Anita Hery
>
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

1. I don't know, but the most obvious reason is that your search string
doesn't exactly match the encrypted string. Did you enter the SELECT
statement exactly as above? If so, then the issue could be that SQL ignores
whitespace, so you would need something like this to handle the newline:

select * from test2
where a = '\0<[\' + char(10) + '|^[\]}{;\'

2. Yes

3. As long as the encrypted string is in a character set supported by the
server (and you could use Unicode if necessary), then there shouldn't be any
specific issues - it's up to your application to do the
encrpytion/decryption.

Simon

[Simon Hayes]

Re: Nonprintable / encrypted characters is not found


"Simon Hayes" <sql@hayes.ch > wrote in message
news:402a7e11$1 _3@news.bluewin .ch...[color=blue]
>
> "Anita" <anonymous@devd ex.com> wrote in message
> news:4029728d$0 $196$75868355@n ews.frii.net...[color=green]
> > Hi All
> >
> > I have a table with one column :
> >
> > CREATE TABLE test2
> > ( a char(15)
> > primary KEY CLUSTERED )
> >
> > The column a is filled with encrypted data
> > (contains control and extended characters).
> > On my test, the select statement for one row
> > in table test2 does not always work sucessfully.
> >
> > Below is my unsucessful statement for the second
> > row followed by 9 rows data. Each row is displayed
> > in 2 versions, text and VB ascii code.
> >
> > select * from test2
> > where a = '\0<[\
> > |^[\]}{;\'
> >
> > ----------
> > Row-1
> > %;>.[
> > ,)]/\-,=/
> > 37;59;62;46;91; 10;44;41;93;47; 92;45;44;61;47;
> > Row-2
> > \0<[\
> > |^[\]}{;\
> > 92;48;60;91;92; 10;124;94;91;92 ;93;125;123;59; 92;
> > Row-3
> > \0<[\ ,)?{=\?
> > 92;48;60;91;92; 11;44;41;63;3;1 23;127;61;92;63 ;
> > Row-4
> > \0<[\ \%:_`-]_
> > 92;48;60;91;92; 11;92;37;58;95; 96;45;5;93;95;
> > Row-5
> > \0<[\ \^:&}{;\
> > 92;48;60;91;92; 11;92;94;58;38; 7;125;123;59;92 ;
> > Row-6
> > \0[*_1>\_\}{@+
> > 92;48;91;42;95; 49;62;92;95;92; 7;125;123;64;43 ;
> > Row-7
> > ].>^/.]-=}<)^&
> > 93;46;62;94;47; 46;8;93;45;61;1 25;60;41;94;38;
> > Row-8
> > ]0_^{
> > }^~_|~{^_
> > 93;48;95;94;123 ;10;125;94;126; 95;124;126;123; 94;95;
> > Row-9
> > {
> > }{31{2|02~||
> > 123;10;125;123; 127;51;49;123;5 0;124;48;50;126 ;124;124;
> > -------------
> >
> > I have 3 questions for all of you :
> > - Why SQL2000 can not find the second row (certain row)?
> > - Can SQL2000 handle character 0 to 255 ?
> > - Does my encrypted method produce bad data for SQL2000?
> >
> > Thanks in advance
> >
> > Anita Hery
> >
> >
> >
> >
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it![/color]
>
> 1. I don't know, but the most obvious reason is that your search string
> doesn't exactly match the encrypted string. Did you enter the SELECT
> statement exactly as above? If so, then the issue could be that SQL[/color]
ignores[color=blue]
> whitespace, so you would need something like this to handle the newline:
>
> select * from test2
> where a = '\0<[\' + char(10) + '|^[\]}{;\'
>
> 2. Yes
>
> 3. As long as the encrypted string is in a character set supported by the
> server (and you could use Unicode if necessary), then there shouldn't be[/color]
any[color=blue]
> specific issues - it's up to your application to do the
> encrpytion/decryption.
>
> Simon
>
>[/color]

Oops - it's not at all correct to say that SQL ignores whitespace; I was
thinking of trailing spaces there for some reason. What may have happened is
that Query Analyzer interpreted your newline as ASCII 13 (carriage return):

select ascii('
')

This gives 13, but you need 10, according to what you posted, so the select
query I suggested above should hopefully be what you need.

Simon

[Anita]

Re: Nonprintable / encrypted characters is not found

Simon,

Thanks for your reply.

I show the exact data in ascii code. I am sure the source
of my problem is not about mistyping. I first found the
problem in my VB application. The following is a part of it.

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.OpenRecordse t(s, dbOpenDynaset)
For i = 1 To rs.RecordCount
Debug.Print rs!a
X = ""
For j = 1 To Len(rs!a)
X = X & Asc(Mid(rs!a, j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" & rs!a & "'"
Set rs1 = cn.OpenRecordse t(s, dbOpenDynaset)
If rs1.RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.movenext
Next
End Sub

Anita Hery




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

Re: Nonprintable / encrypted characters is not found

Anita,

I ran the following SQL Script and VBScript and it returned both rows. Does
this work in your environment? In any case, you might consider storing the
encrypted value as binary rather than character data.

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

DECLARE @Value1 char(15)
SET @Value1 =
CAST(0x253B3E2E 5B0A2C295D2F5C2 D2C3D2F AS char(15))
DECLARE @Value2 char(15)
SET @Value2 =
CAST(0x5C303C5B 5C0A7C5E5B5C5D7 D7B3B5C AS char(15))

INSERT INTO test2 VALUES(@Value1)
INSERT INTO test2 VALUES(@Value2)

SELECT
CAST(a AS binary(15)),
a FROM test2
WHERE a IN(@Value1, @Value2)


' test VBScript
Set cn = CreateObject("A DODB.Connection ")
cn.Open "Provider=SQLOL EDB.1;" _ &
"Data Source=MyServer ;" _ &
"Initial Catalog=MyDatab ase;" _ &
"Integrated Security=SSPI"

dotest()
WScript.Echo "Done"

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
Do While rs.EOF = false
WScript.Echo rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields(" a"))
X = X & Asc(Mid(rs.Fiel ds("a"), j, 1)) & ";"
Next
WScript.Echo X

s = "select * from test2 where a = '" & rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.RecordCount = 0 Then
WScript.Echo "==not found=="
End If
rs.movenext
Loop
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@devd ex.com> wrote in message
news:402aaa48$0 $199$75868355@n ews.frii.net...[color=blue]
> Simon,
>
> Thanks for your reply.
>
> I show the exact data in ascii code. I am sure the source
> of my problem is not about mistyping. I first found the
> problem in my VB application. The following is a part of it.
>
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.OpenRecordse t(s, dbOpenDynaset)
> For i = 1 To rs.RecordCount
> Debug.Print rs!a
> X = ""
> For j = 1 To Len(rs!a)
> X = X & Asc(Mid(rs!a, j, 1)) & ";"
> Next
> Debug.Print X
>
> s = "select * from test2 where a = '" & rs!a & "'"
> Set rs1 = cn.OpenRecordse t(s, dbOpenDynaset)
> If rs1.RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.movenext
> Next
> End Sub
>
> Anita Hery
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Anita]

Re: Nonprintable / encrypted characters is not found

Dan Guzman,

Thanks for your reply.

Currently I am working with DAO library / ODBC.
I am not familiar with VB script. So, I tried
using ADO library 2.7 and made a small modification
like this:

Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim qd As New ADODB.Command
Dim s As String

Private Sub Form_Load()
cn.ConnectionSt ring = "PROVIDER=SQLOL EDB;
SERVER=srv2003; UID=sqlreg;PWD= cas;DATABASE=ca s1"
cn.Open
dotest
End Sub

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
i = 0
Do While rs.EOF = False
i = i + 1
Debug.Print "Row " & i & " -----------"
Debug.Print rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields(" a"))
X = X & Asc(Mid(rs.Fiel ds("a"), j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" &
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.EOF Then ' .RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.MoveNext
Loop
End Sub

If I use my original data, the above code will
produce the same problem on the same row (row 2).
Below is the output list:

=============== =====
Row 1 -----------
%;>.[
,)]/\-,=/
37;59;62;46;91; 10;44;41;93;47; 92;45;44;61;47;
Row 2 -----------
\0<[\
|^[\]}{;\
92;48;60;91;92; 10;124;94;91;92 ;93;125;123;59; 92;
==not found==
Row 3 -----------
\0<[\,)?{=\?
92;48;60;91;92; 11;44;41;63;3;1 23;127;61;92;63 ;
Row 4 -----------
\0<[\\%:_`-]_
92;48;60;91;92; 11;92;37;58;95; 96;45;5;93;95;
Row 5 -----------
\0<[\\^:&}{;\
92;48;60;91;92; 11;92;94;58;38; 7;125;123;59;92 ;
Row 6 -----------
\0[*_1>\_\}{@+
92;48;91;42;95; 49;62;92;95;92; 7;125;123;64;43 ;
Row 7 -----------
].>^/.]-=}<)^&
93;46;62;94;47; 46;8;93;45;61;1 25;60;41;94;38;
Row 8 -----------
]0_^{
}^~_|~{^_
93;48;95;94;123 ;10;125;94;126; 95;124;126;123; 94;95;
Row 9 -----------
{
}{31{2|02~||
123;10;125;123; 127;51;49;123;5 0;124;48;50;126 ;124;124;
=============== ========

The row 2 can be accessed by the query like this:

select * from test2
where a =
char(92)+char(4 8)+char(60)+cha r(91)+char(92)+ char(10)+
char(124)+char( 94)+char(91)+ch ar(92)+char(93) +char(125)+
char(123)+char( 59)+char(92)

But, I will not use this way.

Anita

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

Re: Nonprintable / encrypted characters is not found

Hi, Anita.

I was able to recreate your problem and narrowed it down to the
backslash/newline sequence in the character string. As a workaround, I used
a parameter rather than an embedded literal value. This technique also
eliminates the need to escape other special characters like single quotes.
Example below.

Dim cmd As New ADODB.Command
Dim parm1 As ADODB.parameter
cmd.CommandText = "SELECT * FROM test2 WHERE a = ?"
cmd.ActiveConne ction = cn
Set parm1 = cmd.CreateParam eter("@Parm1", adChar, 1, 15, s)
cmd.Parameters. Append parm1
cmd.Execute

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@devd ex.com> wrote in message
news:402b81e0$0 $202$75868355@n ews.frii.net...[color=blue]
> Dan Guzman,
>
> Thanks for your reply.
>
> Currently I am working with DAO library / ODBC.
> I am not familiar with VB script. So, I tried
> using ADO library 2.7 and made a small modification
> like this:
>
> Dim cn As New ADODB.Connectio n
> Dim rs As New ADODB.Recordset
> Dim qd As New ADODB.Command
> Dim s As String
>
> Private Sub Form_Load()
> cn.ConnectionSt ring = "PROVIDER=SQLOL EDB;
> SERVER=srv2003; UID=sqlreg;PWD= cas;DATABASE=ca s1"
> cn.Open
> dotest
> End Sub
>
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.Execute(s)
> i = 0
> Do While rs.EOF = False
> i = i + 1
> Debug.Print "Row " & i & " -----------"
> Debug.Print rs.Fields("a")
> X = ""
> For j = 1 To Len(rs.Fields(" a"))
> X = X & Asc(Mid(rs.Fiel ds("a"), j, 1)) & ";"
> Next
> Debug.Print X
>
> s = "select * from test2 where a = '" &
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> If rs1.EOF Then ' .RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.MoveNext
> Loop
> End Sub
>
> If I use my original data, the above code will
> produce the same problem on the same row (row 2).
> Below is the output list:
>
> =============== =====
> Row 1 -----------
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91; 10;44;41;93;47; 92;45;44;61;47;
> Row 2 -----------
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92; 10;124;94;91;92 ;93;125;123;59; 92;
> ==not found==
> Row 3 -----------
> \0<[\ ,)?{=\?
> 92;48;60;91;92; 11;44;41;63;3;1 23;127;61;92;63 ;
> Row 4 -----------
> \0<[\ \%:_`-]_
> 92;48;60;91;92; 11;92;37;58;95; 96;45;5;93;95;
> Row 5 -----------
> \0<[\ \^:&}{;\
> 92;48;60;91;92; 11;92;94;58;38; 7;125;123;59;92 ;
> Row 6 -----------
> \0[*_1>\_\}{@+
> 92;48;91;42;95; 49;62;92;95;92; 7;125;123;64;43 ;
> Row 7 -----------
> ].>^/.]-=}<)^&
> 93;46;62;94;47; 46;8;93;45;61;1 25;60;41;94;38;
> Row 8 -----------
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123 ;10;125;94;126; 95;124;126;123; 94;95;
> Row 9 -----------
> {
> }{31{2|02~||
> 123;10;125;123; 127;51;49;123;5 0;124;48;50;126 ;124;124;
> =============== ========
>
> The row 2 can be accessed by the query like this:
>
> select * from test2
> where a =
> char(92)+char(4 8)+char(60)+cha r(91)+char(92)+ char(10)+
> char(124)+char( 94)+char(91)+ch ar(92)+char(93) +char(125)+
> char(123)+char( 59)+char(92)
>
> But, I will not use this way.
>
> Anita
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Anita]

Re: Nonprintable / encrypted characters is not found

Dan,

You are absolutely right.

I think my problem is about query translation
on client side (VB). Perhaps, it is a bug
from Microsoft.

If the query is sent using direct method, like :
s = "select * from test2 where a = '" & _
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
then it is not guaranteed to work.

I made QA trial to prove that my problem was not
caused by SQL2000 :

CREATE TABLE test3
( a char(15)
primary KEY CLUSTERED,
b char(1) )

INSERT INTO test3
SELECT a, '2' as b FROM test2

--set b='1' on the second row
UPDATE test3 SET b = '1'
WHERE a =
char(92)+char(4 8)+char(60)+cha r(91)+char(92)+ char(10)+
char(124)+char( 94)+char(91)+ch ar(92)+char(93) +char(125)+
char(123)+char( 59)+char(92)

DECLARE @Value1 char(15)

SELECT @value1 = a
FROM test3 where b = '1'

--successful second row access
SELECT * FROM test3 where a = @Value1

Thanks for your reply

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

Re: Nonprintable / encrypted characters is not found

SQL Server MVP Steve Kass pointed out to me that this behavior is described
in MSKB 164291
<http://support.microso ft.com/default.aspx?sc id=kb;en-us;164291>.
Basically, SQL Server interprets a backslash followed a newline as a literal
continuation escape sequence so these characters are ignored in the literal
string. You can repro this in Query Analyzer with the following script:

SELECT 'Continu\
ed string'
GO

Although this behavior is also described in the Books Online in the Embedded
SQL for C section <esqlforc.chm ::/ec_6_epr_02_101 f.htm>, it is not mentioned
elsewhere as it probably should. A doc bug has been filed so the
documentation issue should be addressed in the future.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@devd ex.com> wrote in message
news:403007ad$0 $195$75868355@n ews.frii.net...[color=blue]
> Dan,
>
> You are absolutely right.
>
> I think my problem is about query translation
> on client side (VB). Perhaps, it is a bug
> from Microsoft.
>
> If the query is sent using direct method, like :
> s = "select * from test2 where a = '" & _
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> then it is not guaranteed to work.
>
> I made QA trial to prove that my problem was not
> caused by SQL2000 :
>
> CREATE TABLE test3
> ( a char(15)
> primary KEY CLUSTERED,
> b char(1) )
>
> INSERT INTO test3
> SELECT a, '2' as b FROM test2
>
> --set b='1' on the second row
> UPDATE test3 SET b = '1'
> WHERE a =
> char(92)+char(4 8)+char(60)+cha r(91)+char(92)+ char(10)+
> char(124)+char( 94)+char(91)+ch ar(92)+char(93) +char(125)+
> char(123)+char( 59)+char(92)
>
> DECLARE @Value1 char(15)
>
> SELECT @value1 = a
> FROM test3 where b = '1'
>
> --successful second row access
> SELECT * FROM test3 where a = @Value1
>
> Thanks for your reply
>
> Anita Hery
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]