Thread: Error Writing/Reading Encrypted Values
Hello! I am trying to store encrypted password in a varchar (and/or text) column but it is very unstable in my environment. The plain password string is encrypted by a C routine running in Windoz. The encrypted password is then sent from Windoz via psqlODBC to postgresql. Problem happens when the decryption routine (running in Windoz) tries to decrypt the encrypted password retrieved from postgresql - it occasionally succeeds but hangs most of the time. "SELECT length(MyEncryptedPasswordColumn) FROM MyTable" in psql prompt does not much help debugging as the encrypted password may contains low value(s). I also found in Windoz that when my encryption routine aborts, the retrieved encrypted string is actually corrupted - its string length is 1 byte longer than it is supposed to be. Having conducted several testings (by looping encryption->decryption and feeding various plain strings to encryption routine within memory without transmitting data through ODBC to postgresql) on the encryption/decryption routine, I have the confidence that the encryption/decryption routine is stable. My question are: (1) Is it approriate to store encrypted string which may contain low values in text or varchar columns? (2) Is it possible to confuse psqlODBC when writing to/reading from text column for encrypted string? (3) Are there any other better ways to debug? I would much appreciate any help. CN -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com
cnliou@eurosport.com wrote: > > > Problem happens when the decryption routine (running > in Windoz) tries to decrypt the encrypted password > retrieved from postgresql - it occasionally succeeds > but hangs most of the time. I would never use decrypt of password... One should always compare if encrypt(attempt) = encrypt(password) ...then you do not need to decrypt - better security -- Keith Gray Technical Development Manager Heart Consulting Services P/L mailto:keith@heart.com.au
Greetings! Hiroshi, [ begin of quote ] > > I also found in Windoz that when my encryption > > routine aborts, the retrieved encrypted string is > > actually corrupted - its string length is 1 byte > > longer than it is supposed to be. > > There could be the following case. > > Windows client *nix server > > '\n' ---> '\n' > (not preceded by 'r') > \r\n <--- '\n' > > [ end of quote ] > > I will check this out. > My C routine does not add the *unwanted* '\r'. I > won't think delphi does that either. Does pgsqlODBC > do this unwanted conversion? Yes. Do people want a new option ? [ end of quote ] Please first pardon my ignorance of too many things including ODBC. I presume all people want is a "raw" data extracted from database without any translation except for multi-byte characters. As in my case shows: problem arises when char/varchar/text fields are used to store "strange" (encrypted) characters and ODBC "automatically" adds '\r' to these values before returnning them to Windoz applications. Is it reasonable and possible in technical point of view to at least add a checkbox in ODBC indicating enable/disable '\r' translation? Or is it true that char/varchar/text columns were not designed to store encrypted characters, which may contain non ascii characters and low values, in the first place? If so, where am I supposed to save the encrypted characters? Regards, CN -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com
cnliou@eurosport.com wrote: > > Greetings! Hiroshi, > > [ begin of quote ] > > > I also found in Windoz that when my encryption > > > routine aborts, the retrieved encrypted string is > > > actually corrupted - its string length is 1 byte > > > longer than it is supposed to be. > > > > There could be the following case. > > > > Windows client *nix server > > > > '\n' ---> '\n' > > (not preceded by 'r') > > \r\n <--- '\n' > > > > [ end of quote ] > > > > I will check this out. > > My C routine does not add the *unwanted* '\r'. I > > won't think delphi does that either. Does > pgsqlODBC > > do this unwanted conversion? > > Yes. Do people want a new option ? > [ end of quote ] > > Please first pardon my ignorance of too many things > including ODBC. > > I presume all people want is a "raw" data extracted > from database without any translation except for > multi-byte characters. I don't know the history but psqlodbc driver seems to have been unchanged about this point since long. Probably someone wanted this conversion long ago for e.g. editing sentences with new lines and it's pretty useful for Windows' users. However it doesn't seem preferable for *nix clients which don't need \r(Carriage Return)s for new lines. I have seen the request to add an option to enable /disable new lines conversion only once pretty long ago. regards, Hiroshi Inoue
Hiroshi, I really appreciate your lightening fast helps/explanations! I will wait and see any further enhancement to pgsqlODBC. I will also monitor the evolution of Borland's native postgresql driver for Linux. Regards, CN [ begin of quote ] > I presume all people want is a "raw" data extracted > from database without any translation except for > multi-byte characters. I don't know the history but psqlodbc driver seems to have been unchanged about this point since long. Probably someone wanted this conversion long ago for e.g. editing sentences with new lines and it's pretty useful for Windows' users. However it doesn't seem preferable for *nix clients which don't need \r(Carriage Return)s for new lines. I have seen the request to add an option to enable /disable new lines conversion only once pretty long ago. [ end of quote ] -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com
I think bytea is intended for storing binary data. Not used it myself but I think it needs 0's parsing (to \000 I believe). - Stuart -----Original Message----- From: cnliou@eurosport.com [mailto:cnliou@eurosport.com] Sent: 15 January 2002 06:06 To: pgsql-odbc@postgresql.org Subject: Re: Error Writing/Reading Encrypted Values Greetings! Hiroshi, [ begin of quote ] > > I also found in Windoz that when my encryption > > routine aborts, the retrieved encrypted string is > > actually corrupted - its string length is 1 byte > > longer than it is supposed to be. > > There could be the following case. > > Windows client *nix server > > '\n' ---> '\n' > (not preceded by 'r') > \r\n <--- '\n' > > [ end of quote ] > > I will check this out. > My C routine does not add the *unwanted* '\r'. I > won't think delphi does that either. Does pgsqlODBC > do this unwanted conversion? Yes. Do people want a new option ? [ end of quote ] Please first pardon my ignorance of too many things including ODBC. I presume all people want is a "raw" data extracted from database without any translation except for multi-byte characters. As in my case shows: problem arises when char/varchar/text fields are used to store "strange" (encrypted) characters and ODBC "automatically" adds '\r' to these values before returnning them to Windoz applications. Is it reasonable and possible in technical point of view to at least add a checkbox in ODBC indicating enable/disable '\r' translation? Or is it true that char/varchar/text columns were not designed to store encrypted characters, which may contain non ascii characters and low values, in the first place? If so, where am I supposed to save the encrypted characters? Regards, CN -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com
cnliou@eurosport.com wrote: > > Hello! > > I am trying to store encrypted password in a varchar > (and/or text) column but it is very unstable in my > environment. > The plain password string is encrypted by a C routine > running in Windoz. The encrypted password is then > sent from Windoz via psqlODBC to postgresql. > > Problem happens when the decryption routine (running > in Windoz) tries to decrypt the encrypted password > retrieved from postgresql - it occasionally succeeds > but hangs most of the time. > > "SELECT length(MyEncryptedPasswordColumn) FROM > MyTable" in psql prompt does not much help debugging > as the encrypted password may contains low value(s). > > I also found in Windoz that when my encryption > routine aborts, the retrieved encrypted string is > actually corrupted - its string length is 1 byte > longer than it is supposed to be. There could be the following case. Windows client *nix server '\n' ---> '\n' (not preceded by 'r') \r\n <--- '\n' regards, Hiroshi Inoue