posix script: extracting binary data from oracle

[Scott Williams_5]

I have a posix script that extracts an encrypted ftp password from an oracle table via "sqlplus -s". I pass this variable to a decryption program within the script, and assign the decrypted output to another variable, which is then used for ftp purposes.

I have found, on rare occasions, that an encrypted password that should unencrypt to say, "FoobaR" actually gets unencrypted to "Foobar".

I suspect the binary data is somehow occasionally getting translated -- a hex 00 to a hex 20, for instance.

Anyone done something similar to this or have any ideas? I know it would probably be easier to write a c program or something, but we don't have ProC installed on this box.

Thanks

[A. Clay Stephenson]

I suppose the first thing to nail down is when "FoobaR" becomes "Foobar" does the same plaintext crypt/decrypt consistantly or does the same plaintext give different results on different runs?

You really need to tell us out the Oracle output and a simple printf("%s",s) won't do it because as soon as the first NUL is encountered the sting is terminated. It's just not possible to help much without know some details --- and you can't reveal many detials. I assume your Oracle is using some combination of decode, translate, hextoraw, rawtohex.

At this point, your task is to isolate the problem to Oracle or to your encrypt/decrypt program.

[Scott Williams_5]

Thanks A.Clay,

We have a cobol program that calls proprietary 'c' libraries (part of our ERP system) to retrieve the encrypted information from Oracle. I have stepped thru the code in debug to watch the decryption program (it uses some variant of RC4 algorithm) and actually saw the translation of each byte.

It's pretty clear that what the script is passing to the decryption program is different than what the cobol program is passing, even though it's the same table element. The database field is defined as a simple alpha field.

I have attached a text file that shows
the hex values of the data when the encrypt/de-encrypt program is called both ways (with a script, and with a cobol program).

I hope I didn't confuse the issue.

Scott

[A. Clay Stephenson]

The first thing I notice is that the 26th character in ${my_var} is a 3Dh in the original but appears as 28h in the same position just below. Also 0x00 (NUL) gets translated to 0x20 (space) in the example just below.

Are you using the shell to pass these values into the encrypt/decrypt program? You would probably be better served to capture the output in a file and cat the file to provide input.

[Scott Williams_5]

A.Clay,

I have spooled the select of the encrypted value to a file, instead of assigning it to a variable. When I cat the file and pipe it thru "hd", it gives exactly the same results as the "print -n ${my_var}" example in the attachment sent earlier.

I believe I have isolated the issue to the manner in which I am retrieving the data. I need to capture the raw, binary data in that field. I've looked at hextoraw and rawtohex, but it seems like what I need is simply a "retrieve raw value" function.

I'll keep at it. Thanks for your input.

Scott