storing binary data
I want to store some binary data in Postgres. The data is an
MD5 checksum of the user's password, in binary. It will be
exactly 16 bytes (since it is a one-way hash).
Can I store this safely in a CHAR column? Can the data be
treated normally (i.e. compare a binary checksum of the
password the user entered with the binary value stored
in the DB, etc).
Are there any issues I'll need to watch out for when
storing/manipulating binary data?
(I was going to RTFM and search the mailing list archives,
but they don't seem to be working.)
TIA,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Secrecy is the beginning of tyranny.
-- Heinlein
Neil Conway <nconway@klamath.dyndns.org> writes:
I want to store some binary data in Postgres. The data is an
MD5 checksum of the user's password, in binary. It will be
exactly 16 bytes (since it is a one-way hash).
Can I store this safely in a CHAR column?
No. CHAR and friends assume there are no null (zero) bytes.
In MULTIBYTE setups there are probably additional constraints.
You could use bytea, but I would recommend converting the checksum
to a hex digit string and then storing that in a char-type field.
Hex is the usual textual representation for MD5 values, no?
regards, tom lane
On Mon, Oct 16, 2000 at 11:22:40PM -0400, Tom Lane wrote:
Neil Conway <nconway@klamath.dyndns.org> writes:
I want to store some binary data in Postgres. The data is an
MD5 checksum of the user's password, in binary. It will be
exactly 16 bytes (since it is a one-way hash).Can I store this safely in a CHAR column?
No. CHAR and friends assume there are no null (zero) bytes.
In MULTIBYTE setups there are probably additional constraints.You could use bytea, but I would recommend converting the checksum
to a hex digit string and then storing that in a char-type field.
Hex is the usual textual representation for MD5 values, no?
It is, but (IMHO) it's a big waste of space. The actual MD5 digest is
128 bits. If stored in binary form, it's 16 bytes. If stored in hex
form (as ASCII), it's 32 characters @ 1 byte per character = 32 bytes.
In Unicode, that's 64 bytes (correct me if I'm wrong).
It's not a huge deal, but it would be nice to store this efficiently.
Is this possible?
TIA,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Being able to break into computers doesn't make you a hacker any more
than being able to hotwire cars makes you an automotive engineer.
* Neil Conway <nconway@klamath.dyndns.org> [001016 20:41] wrote:
On Mon, Oct 16, 2000 at 11:22:40PM -0400, Tom Lane wrote:
Neil Conway <nconway@klamath.dyndns.org> writes:
I want to store some binary data in Postgres. The data is an
MD5 checksum of the user's password, in binary. It will be
exactly 16 bytes (since it is a one-way hash).Can I store this safely in a CHAR column?
No. CHAR and friends assume there are no null (zero) bytes.
In MULTIBYTE setups there are probably additional constraints.You could use bytea, but I would recommend converting the checksum
to a hex digit string and then storing that in a char-type field.
Hex is the usual textual representation for MD5 values, no?It is, but (IMHO) it's a big waste of space. The actual MD5 digest is
128 bits. If stored in binary form, it's 16 bytes. If stored in hex
form (as ASCII), it's 32 characters @ 1 byte per character = 32 bytes.
In Unicode, that's 64 bytes (correct me if I'm wrong).It's not a huge deal, but it would be nice to store this efficiently.
Is this possible?
Why not use base64? It's pretty gross but might work for you.
-Alfred
Neil Conway <nconway@klamath.dyndns.org> writes:
You could use bytea, but I would recommend converting the checksum
to a hex digit string and then storing that in a char-type field.
Hex is the usual textual representation for MD5 values, no?
It is, but (IMHO) it's a big waste of space. The actual MD5 digest is
128 bits. If stored in binary form, it's 16 bytes. If stored in hex
form (as ASCII), it's 32 characters @ 1 byte per character =3D 32 bytes.
You're worried about 16 bytes per pg_shadow entry? Get real. I'd
have recommended bytea if the amount of storage involved were actually
significant, but for this application readability seems more important.
regards, tom lane
On Mon, Oct 16, 2000 at 11:57:36PM -0400, Tom Lane wrote:
Neil Conway <nconway@klamath.dyndns.org> writes:
It is, but (IMHO) it's a big waste of space. The actual MD5 digest is
128 bits. If stored in binary form, it's 16 bytes. If stored in hex
form (as ASCII), it's 32 characters @ 1 byte per character =3D 32 bytes.You're worried about 16 bytes per pg_shadow entry? Get real. I'd
have recommended bytea if the amount of storage involved were actually
significant, but for this application readability seems more important.
To clarify, these are 'application users', not Postgres users. So
the info is stored in one of my own tables, not pg_shadow. Although
I agree, this isn't a big deal either way.
Alfred: thanks for the tip. Looks like base64 will solve my problems!
Thanks to everyone who responded,
Neil
--
Neil Conway <neilconway@home.com>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
Violence is to dictatorship as propaganda is to democracy.
-- Noam Chomsky