Re: Oracle characterset confusion when storing encoded passwords

On Feb 4, 10:13 am, Noons <wizofo..._at_yahoo.com.au> wrote:
> On Feb 4, 8:44 pm, 151 <ga..._at_my-deja.com> wrote:
>
> > So somewhere along the way what I send to the DB (at user registration
> > time) to store in the VARCHAR2 as the encoded password is being
> > changed slightly.
>
> VARCHAR2 assumes character data that matches
> whatever encoding you've set in your database
> for character data.
>
> > The Oracle DB seems to be set to the WE8ISO8859P15 characterset and I
> > am sure this is where the problem lies.
>
> Bingo.
>
>
>
> > my question then is: if I insert data into a VARCHAR2 field into the
> > DB that was encoded as UTF8 originally - if the database is using
> > WE8ISO8859P15 - would this result in the data being "altered"?
>
> if you insist on forcing Oracle to treat that data
> as a valid WE8ISO8859P15 character string, yes indeed.
>
> What you need to do is store that encrypted data as a
> BLOB. In which case Oracle treats it as a binary string,
> no translation. Which is what you want if you're doing
> the encryption yourself.

Hi again. Many thanks for the replies!

Sybrand, I did play about with using an NVARCHAR2 type instead but after some further experiementation and conversation with a Colleague we came to the thought of using the Oracle function ASCIISTR at the DB side before encoding the password (with the same/similar thing hapening at the client side during login)

I was slightly wrong in my description in that the password is sent in the clear (!) to the DB at "user registration time" and then the DB does thes XORing before storing the result in a VARCHAR2. At login time, this is fetched back from the DB and compared with the XOR of the users input password. Yes crazy I know, but I have been informed that this is all over a VPN....hmmmm.

Anyway, if ASCIISTR is used to before XORing at the DB side for storing the password at registration AND the users input password at login is also ASCIISTR'd (in Java or whatever) this seems to work.

This seems to work and does not cause problems with "normal" characters - it only has an effect on the "extended" ones. Received on Tue Feb 05 2008 - 04:02:07 CST