pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStoring encrypted data?
8+ messages / 3 participants
[nested] [flat]
* Storing encrypted data?
@ 2006-07-17 01:11 John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: John Tregea @ 2006-07-17 01:11 UTC (permalink / raw)
To: pgsql-sql
Hi,
Does anyone have experience with storing encrypted data into pgSQL? I
have a pgSQL database which uses UTF8 encoding. I am encrypting plain
text in my (GUI) application and sending it to a field (with data type
'text') in my database.
I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"
I am using blowfish at 128bit to encrypt the string.
The encrypted string comes out looking like this
Salted__ë±9 r^(TM)LZPÃOG¤u--¨ékTz~6Ëpi^(TM)|O +LÏ°
îX@ûñÙÁl£ðOE...¢53P9 ×ð"á6kUÆ¢÷Íì MNÉA.---ä![^j"Ø|¨\X--ÛÜu?ªÅÜ-ÎáBæºñȦbF >\Z(v/ÞÎ.&ó«[=bný?Kr°óË2U×ZI©âVc°sn=¤Õg«^q0Ã8ý<Êh?; "¨"?[S(ç+u[£
--è---='±ú3³?ì¯,Za@LU'T¸oeÖgoeË?PÛ<...H---8ÃbÙÉZ(NÙ í>ÇL]í¶²--âOEÿ/Ínz¶å
Õ?Îcµ^$í¿W-^(TM)óà¢ey÷àÞ¬oe¤CðÏjX"ÃGÄ"·º?ë0L'Ù++Æz(<&^{ªìëá/Ç}k9?ÄX1â<²T½Hõ5Ôgÿ§yÈy..._---ú$lA~©î1úëùññðÑÝS(z(gÕþÊ/«¦...8?¯þ
>ü,fEUR--aÈÉ"iG0/00}¹²#À
I enclose that in single quotes and send it in an insert statement to
pgSQL.
The statement has been working, until I got to a string that generated
the above encrypted data, then it broke.
Alternatively, can anyone suggest an encryption algorithm that will only
return characters that are valid UTF8 encoded?
Thanks in advance
John T
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
@ 2006-07-17 03:07 ` Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-07-17 03:07 UTC (permalink / raw)
To: John Tregea <john@debraneys.com>; +Cc: pgsql-sql
On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
> Does anyone have experience with storing encrypted data into pgSQL? I
> have a pgSQL database which uses UTF8 encoding. I am encrypting plain
> text in my (GUI) application and sending it to a field (with data type
> 'text') in my database.
>
> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"
Use bytea instead of text for binary data; alternatively, base64
encode ("armor") the encrypted data and store it as text. If you
insert binary data then you'll need to escape non-printable characters
or use an API that can pass binary data to the backend (e.g., libpq's
PQexecParams() or some language's interface to that function). Some
APIs might automatically escape data if you use placeholders; check
your API's documentation.
http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
@ 2006-07-17 06:05 ` John Tregea <john@debraneys.com>
2006-07-17 12:16 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
2006-07-17 15:55 ` Re: Storing encrypted data? Aaron Bono <postgresql@aranya.com>
0 siblings, 2 replies; 8+ messages in thread
From: John Tregea @ 2006-07-17 06:05 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Hi Michael,
Thanks for your advice. I was looking at the bytea data type in the
PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
was concerned that if I have to escape certain characters like the
single quote or the backslash, how do I guarantee that the escaped
version does not already appear in the encrypted string?
Should I use the octal value to escape the single quote (\047) and
backslash (\\134)?
Those character sequences are extremely unlikely to occur in an
encrypted string.
Is the the right approach ?
Also... I note that I cannot change the data type of my field from text
to bytea (I am using PGADMIN III). Do you know why?
Regards
John T
Michael Fuhr wrote:
> On Mon, Jul 17, 2006 at 09:11:12AM +0800, John Tregea wrote:
>
>> Does anyone have experience with storing encrypted data into pgSQL? I
>> have a pgSQL database which uses UTF8 encoding. I am encrypting plain
>> text in my (GUI) application and sending it to a field (with data type
>> 'text') in my database.
>>
>> I get an error back saying "invalid byte sequence for encoding UTF8; oxd733"
>>
>
> Use bytea instead of text for binary data; alternatively, base64
> encode ("armor") the encrypted data and store it as text. If you
> insert binary data then you'll need to escape non-printable characters
> or use an API that can pass binary data to the backend (e.g., libpq's
> PQexecParams() or some language's interface to that function). Some
> APIs might automatically escape data if you use placeholders; check
> your API's documentation.
>
> http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html
> http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
@ 2006-07-17 12:16 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2006-07-17 12:16 UTC (permalink / raw)
To: John Tregea <john@debraneys.com>; +Cc: pgsql-sql
On Mon, Jul 17, 2006 at 02:05:32PM +0800, John Tregea wrote:
> Thanks for your advice. I was looking at the bytea data type in the
> PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
> was concerned that if I have to escape certain characters like the
> single quote or the backslash, how do I guarantee that the escaped
> version does not already appear in the encrypted string?
>
> Should I use the octal value to escape the single quote (\047) and
> backslash (\\134)?
Yes, you'll need to escape certain characters like backslashes.
Your client API might provide an escaping function so you don't
have to worry about the details, or, if you're using placeholders,
the API might automatically escape binary data or transfer it to
the backend as binary. What client interface will you be using to
insert this data?
> Those character sequences are extremely unlikely to occur in an
> encrypted string.
They're as likely to occur as any other. A desirable property of
a cipher is that its output should be indistinguishable from random
data, so any character or sequence of characters is equally likely
to occur.
> Also... I note that I cannot change the data type of my field from text
> to bytea (I am using PGADMIN III). Do you know why?
I don't use pgAdmin so I don't know what it can or can't do. Have
you tried it in psql? What version of PostgreSQL are you using?
The ability to alter a column's type was added in 8.0; in previous
versions you can use the method shown in the FAQ:
http://www.postgresql.org/docs/faqs.FAQ.html#item4.3
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
@ 2006-07-17 15:55 ` Aaron Bono <postgresql@aranya.com>
2006-07-19 08:41 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
1 sibling, 1 reply; 8+ messages in thread
From: Aaron Bono @ 2006-07-17 15:55 UTC (permalink / raw)
To: John Tregea <john@debraneys.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On 7/17/06, John Tregea <john@debraneys.com> wrote:
>
> Hi Michael,
>
> Thanks for your advice. I was looking at the bytea data type in the
> PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
> was concerned that if I have to escape certain characters like the
> single quote or the backslash, how do I guarantee that the escaped
> version does not already appear in the encrypted string?
>
> Should I use the octal value to escape the single quote (\047) and
> backslash (\\134)?
>
> Those character sequences are extremely unlikely to occur in an
> encrypted string.
>
> Is the the right approach ?
>
> Also... I note that I cannot change the data type of my field from text
> to bytea (I am using PGADMIN III). Do you know why?
When I have data like this, I do a Base64 encoding. The string ends up
longer but I no longer have to worry about special characters mucking things
up.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 15:55 ` Re: Storing encrypted data? Aaron Bono <postgresql@aranya.com>
@ 2006-07-19 08:41 ` John Tregea <john@debraneys.com>
2006-07-19 14:34 ` Re: Storing encrypted data? Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 8+ messages in thread
From: John Tregea @ 2006-07-19 08:41 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
Hi Aaron,
I found that your suggestion worked well. For some reason the IDE I use
(Revolution) put a return character every 73rd character when it did the
base64encode, but I strip those out and there no further problems. I
don't even have to put them back later to decode it.
Thanks
John T
Aaron Bono wrote:
> On 7/17/06, *John Tregea* <john@debraneys.com
> <mailto:john@debraneys.com>> wrote:
>
> Hi Michael,
>
> Thanks for your advice. I was looking at the bytea data type in the
> PostgreSQL book I bought (Korry and Susan Douglas, second edition). I
> was concerned that if I have to escape certain characters like the
> single quote or the backslash, how do I guarantee that the escaped
> version does not already appear in the encrypted string?
>
> Should I use the octal value to escape the single quote (\047) and
> backslash (\\134)?
>
> Those character sequences are extremely unlikely to occur in an
> encrypted string.
>
> Is the the right approach ?
>
> Also... I note that I cannot change the data type of my field from
> text
> to bytea (I am using PGADMIN III). Do you know why?
>
>
>
> When I have data like this, I do a Base64 encoding. The string ends
> up longer but I no longer have to worry about special characters
> mucking things up.
>
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> ==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 15:55 ` Re: Storing encrypted data? Aaron Bono <postgresql@aranya.com>
2006-07-19 08:41 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
@ 2006-07-19 14:34 ` Aaron Bono <postgresql@aranya.com>
2006-07-20 11:52 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
0 siblings, 1 reply; 8+ messages in thread
From: Aaron Bono @ 2006-07-19 14:34 UTC (permalink / raw)
To: John Tregea <john@debraneys.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On 7/19/06, John Tregea <john@debraneys.com> wrote:
>
> Hi Aaron,
>
> I found that your suggestion worked well. For some reason the IDE I use
> (Revolution) put a return character every 73rd character when it did the
> base64encode, but I strip those out and there no further problems. I
> don't even have to put them back later to decode it.
I usually leave the return characters where they are.
I am curious, why did you decide to remove the return characters?
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Storing encrypted data?
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Re: Storing encrypted data? Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 15:55 ` Re: Storing encrypted data? Aaron Bono <postgresql@aranya.com>
2006-07-19 08:41 ` Re: Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-19 14:34 ` Re: Storing encrypted data? Aaron Bono <postgresql@aranya.com>
@ 2006-07-20 11:52 ` John Tregea <john@debraneys.com>
0 siblings, 0 replies; 8+ messages in thread
From: John Tregea @ 2006-07-20 11:52 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
Hi Aaron,
I removed them because when I bring the data back into my GUI, the
returns are treated as a record break by the software and I cannot
decrypt them or display the rest of the record that is not encrypted.
Regards
John T
Aaron Bono wrote:
> On 7/19/06, *John Tregea* <john@debraneys.com
> <mailto:john@debraneys.com>> wrote:
>
> Hi Aaron,
>
> I found that your suggestion worked well. For some reason the IDE
> I use
> (Revolution) put a return character every 73rd character when it
> did the
> base64encode, but I strip those out and there no further problems. I
> don't even have to put them back later to decode it.
>
>
>
> I usually leave the return characters where they are.
>
> I am curious, why did you decide to remove the return characters?
>
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> ==================================================================
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-07-20 11:52 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-17 01:11 Storing encrypted data? John Tregea <john@debraneys.com>
2006-07-17 03:07 ` Michael Fuhr <mike@fuhr.org>
2006-07-17 06:05 ` John Tregea <john@debraneys.com>
2006-07-17 12:16 ` Michael Fuhr <mike@fuhr.org>
2006-07-17 15:55 ` Aaron Bono <postgresql@aranya.com>
2006-07-19 08:41 ` John Tregea <john@debraneys.com>
2006-07-19 14:34 ` Aaron Bono <postgresql@aranya.com>
2006-07-20 11:52 ` John Tregea <john@debraneys.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox