Обсуждение: MD5 in pg and php

Hi, I'm completely new to hashing, so please bear with me.

I want to store passwords in a pg db, MD5 seems like a good way to
enhance security (no more plaintext passwords over the line etc.) I
had a look at MD5, in PHP 4.3.4 there is a function md5() which seems
to work nicely:

  echo md5('apple');

returns ae6d32585ecc4d33cb8cd68a047d8434 every time. Apart from the
PHP frontend, I also want to be able to use the same algorithm in
native pg. I found that postgresql-contrib-7.3.4 has a function
crypt() which I expected to work the same. However:

  select crypt('apple', gen_salt('md5'));

gives values different from the PHP md5() function, and moreover,
they're different each time. Can anyone please tell me what I should
do to get the same result in native pg as in PHP?

Thanks!

--
08:59-09:07
Fedora Core release 1 (Yarrow) Linux 2.4.22-1.2188.nptl

Joolz wrote:
> Hi, I'm completely new to hashing, so please bear with me.
>
> I want to store passwords in a pg db, MD5 seems like a good way to
> enhance security (no more plaintext passwords over the line etc.) I
> had a look at MD5, in PHP 4.3.4 there is a function md5() which seems
> to work nicely:
>
>   echo md5('apple');
>
> returns ae6d32585ecc4d33cb8cd68a047d8434 every time. Apart from the
> PHP frontend, I also want to be able to use the same algorithm in
> native pg. I found that postgresql-contrib-7.3.4 has a function
> crypt() which I expected to work the same. However:
>
>   select crypt('apple', gen_salt('md5'));
>
> gives values different from the PHP md5() function, and moreover,
> they're different each time. Can anyone please tell me what I should
> do to get the same result in native pg as in PHP?
>
> Thanks!
>

I whould suggest to update PG to version 7.4, where there is built-in
MD5 function.

> [Marek Lewczuk schreef op 29-04-2004 09:31 +0100]
>
> Joolz wrote:
> >Hi, I'm completely new to hashing, so please bear with me.
> >
> >I want to store passwords in a pg db, MD5 seems like a good way to
> >enhance security (no more plaintext passwords over the line etc.) I
> >had a look at MD5, in PHP 4.3.4 there is a function md5() which seems
> >to work nicely:
> >
> >  echo md5('apple');
> >
> >returns ae6d32585ecc4d33cb8cd68a047d8434 every time. Apart from the
> >PHP frontend, I also want to be able to use the same algorithm in
> >native pg. I found that postgresql-contrib-7.3.4 has a function
> >crypt() which I expected to work the same. However:
> >
> >  select crypt('apple', gen_salt('md5'));
> >
> >gives values different from the PHP md5() function, and moreover,
> >they're different each time. Can anyone please tell me what I should
> >do to get the same result in native pg as in PHP?
> >
> >Thanks!
> >
>
> I whould suggest to update PG to version 7.4, where there is built-in
> MD5 function.

Thanks, I already found some fc1 rpm's (still in development). But can
anyone tell me if the 7.4 internal md5() function will do the same as
the PHP md5() function? Or do a quick select md5('apple') and let me
know if the result is 'ae6d32585ecc4d33cb8cd68a047d8434'?

I really wouldn't like to upgrade from my current stable situation,
only to find out that it didn't solve my original problem.

TIA!

--
10:14-10:15
Fedora Core release 1 (Yarrow) Linux 2.4.22-1.2188.nptl

> Thanks, I already found some fc1 rpm's (still in development). But can
> anyone tell me if the 7.4 internal md5() function will do the same as
> the PHP md5() function? Or do a quick select md5('apple') and let me
> know if the result is 'ae6d32585ecc4d33cb8cd68a047d8434'?
>
> I really wouldn't like to upgrade from my current stable situation,
> only to find out that it didn't solve my original problem.

Postgres 7.4:

usa=> select md5('apple');
                md5
----------------------------------
  1f3870be274f6c49b3e31a0c6728957f
(1 row)


And my PHP:

bash-2.05a$ php -q
<?php echo md5('apple') ?>
1f3870be274f6c49b3e31a0c6728957f

So mine are the same, but yours is completely different to anything.

Chris

Hello,

> I really wouldn't like to upgrade from my current stable situation,
> only to find out that it didn't solve my original problem.

I did a "select md5('apple');". PostgreSQL 7.4.2
For me, the result is: 1f3870be274f6c49b3e31a0c6728957f

In php I did a "echo md5('apple');". php 4.3.6
The result in php is also: 1f3870be274f6c49b3e31a0c6728957f

I don't now, how you get your answer...

Greetings,
Stefan Sturm

Joolz wrote:

>>[Marek Lewczuk schreef op 29-04-2004 09:31 +0100]
>>
>>Joolz wrote:
>>
>>
>>>Hi, I'm completely new to hashing, so please bear with me.
>>>
>>>I want to store passwords in a pg db, MD5 seems like a good way to
>>>enhance security (no more plaintext passwords over the line etc.) I
>>>had a look at MD5, in PHP 4.3.4 there is a function md5() which seems
>>>to work nicely:
>>>
>>> echo md5('apple');
>>>
>>>returns ae6d32585ecc4d33cb8cd68a047d8434 every time. Apart from the
>>>PHP frontend, I also want to be able to use the same algorithm in
>>>native pg. I found that postgresql-contrib-7.3.4 has a function
>>>crypt() which I expected to work the same. However:
>>>
>>> select crypt('apple', gen_salt('md5'));
>>>
>>>gives values different from the PHP md5() function, and moreover,
>>>they're different each time. Can anyone please tell me what I should
>>>do to get the same result in native pg as in PHP?
>>>
>>>Thanks!
>>>
>>>
>>>
>>I whould suggest to update PG to version 7.4, where there is built-in
>>MD5 function.
>>
>>
>
>Thanks, I already found some fc1 rpm's (still in development). But can
>anyone tell me if the 7.4 internal md5() function will do the same as
>the PHP md5() function? Or do a quick select md5('apple') and let me
>know if the result is 'ae6d32585ecc4d33cb8cd68a047d8434'?
>
>I really wouldn't like to upgrade from my current stable situation,
>only to find out that it didn't solve my original problem.
>
>TIA!
>
>
>
OK, PHP

---------------------------------------------------
<?

echo md5('apple');

?>
---------------------------------------------------

produces

---------------------------------------------------
1f3870be274f6c49b3e31a0c6728957f
---------------------------------------------------

and postgres:

---------------------------------------------------
im_perf=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
20031022 (Red Hat Linux 3.3.2-1)
(1 row)

im_perf=# select md5('apple');
               md5
----------------------------------
 1f3870be274f6c49b3e31a0c6728957f
(1 row)
---------------------------------------------------

I have no idea why these two results dont agree with yours, but at least
they agree with each other. It is probasbly different library versions.


Nick

> [Marek Lewczuk schreef op 29-04-2004 10:49 +0100]
>
> Joolz wrote:
> >>[Marek Lewczuk schreef op 29-04-2004 09:31 +0100]
> >>
> >Thanks, I already found some fc1 rpm's (still in development). But can
> >anyone tell me if the 7.4 internal md5() function will do the same as
> >the PHP md5() function? Or do a quick select md5('apple') and let me
> >know if the result is 'ae6d32585ecc4d33cb8cd68a047d8434'?
> >
> >I really wouldn't like to upgrade from my current stable situation,
> >only to find out that it didn't solve my original problem.
> >
> >TIA!
> >
>
> I have 7.4.1 version and "select md5('apple')" returns:
> 1f3870be274f6c49b3e31a0c6728957f
>
> But I have checked in my PHP 4.3 what will return md5('apple') and:
> 1f3870be274f6c49b3e31a0c6728957f
>
> So as you see there are the same - but they are different from your result.

Thanks for testing this!

I'm not sure if I'm happy with the outcome... Seems as if the result
of both md5() functions depends on something outside of PHP and
postgresql... Which could mean that if I backup my db and restore it
on another server (which of course should be possible) all my
passwords will be broken :-(

I guess I'll have to take a better look at how md5 works...

Thanks again!

--
10:50-10:54
Fedora Core release 1 (Yarrow) Linux 2.4.22-1.2188.nptl

Joolz wrote:
>>[Marek Lewczuk schreef op 29-04-2004 09:31 +0100]
>>
> Thanks, I already found some fc1 rpm's (still in development). But can
> anyone tell me if the 7.4 internal md5() function will do the same as
> the PHP md5() function? Or do a quick select md5('apple') and let me
> know if the result is 'ae6d32585ecc4d33cb8cd68a047d8434'?
>
> I really wouldn't like to upgrade from my current stable situation,
> only to find out that it didn't solve my original problem.
>
> TIA!
>

I have 7.4.1 version and "select md5('apple')" returns:
1f3870be274f6c49b3e31a0c6728957f

But I have checked in my PHP 4.3 what will return md5('apple') and:
1f3870be274f6c49b3e31a0c6728957f

So as you see there are the same - but they are different from your result.

> [Joolz schreef op 29-04-2004 10:54 +0200]

...

> I guess I'll have to take a better look at how md5 works...

(foot in brain)

I had a typo on my testcode, now I get
1f3870be274f6c49b3e31a0c6728957f too.

Thanks everyone, I will have a look at what's changed between 7.3.4
and 7.4.2 and then (probably) upgrade.

--
11:23-11:26
Fedora Core release 1 (Yarrow) Linux 2.4.22-1.2188.nptl

On Thu, Apr 29, 2004 at 09:07:16 +0200,
  Joolz <joolz@xs4all.nl> wrote:
>
> I want to store passwords in a pg db, MD5 seems like a good way to
> enhance security (no more plaintext passwords over the line etc.) I
> had a look at MD5, in PHP 4.3.4 there is a function md5() which seems
> to work nicely:

This may not help as much as you think. If you are using an ssl connection,
the passwords won't be sent in the clear. If you aren't using an ssl
connection the hashes will be in clear. I don't believe the postgres
uses a random challenge so that you won't really raise the bar much.
Plus if snooping passwords is a threat, most likely snooping data will
be as well (though not always) and forcing connections to use ssl may be
your best bet. I believe as typically used this is vulnerable to man in
the middle attacks, but these are going to be significantly harder to do than
simple snooping.

> [Bruno Wolff III schreef op 29-04-2004 05:11 -0500]
>
> On Thu, Apr 29, 2004 at 09:07:16 +0200,
>   Joolz <joolz@xs4all.nl> wrote:
> >
> > I want to store passwords in a pg db, MD5 seems like a good way to
> > enhance security (no more plaintext passwords over the line etc.)
> > I had a look at MD5, in PHP 4.3.4 there is a function md5() which
> > seems to work nicely:
>
> This may not help as much as you think. If you are using an ssl
> connection, the passwords won't be sent in the clear. If you aren't
> using an ssl connection the hashes will be in clear. I don't believe
> the postgres uses a random challenge so that you won't really raise
> the bar much.  Plus if snooping passwords is a threat, most likely
> snooping data will be as well (though not always) and forcing
> connections to use ssl may be your best bet. I believe as typically
> used this is vulnerable to man in the middle attacks, but these are
> going to be significantly harder to do than simple snooping.

Thanks for the tip. I already have things like:

  if (ini_get('register_globals') === 1) {
    say it's not safe, don't enter the application }

and

  if (!isset($_SERVER['HTTPS']) || $_SERVER['HTTPS'] !== 'on') {
    idem }

in my login screen. Still, I would feel more assured if passwords go
over the line md5()'d, even if that line is encrypted.

I have other things to worry about next. Right now I let the user
enter the pg username and password, store these values in session
variables and use them every time a database connection is needed.

Someone told me this is very insecure, as the session variables are
stored in either /tmp on the server (that's where I have them put), in
a local cookie or in the URL. Neither would be safe, a better approach
would be to authenticicate once (by looking up the user + password in
a table), and give access to certain tables, views etc. based on the
result of the first lookup; the result being different roles in the
organisation who have access to certain views etc. or a simple 'false'
if the password is wrong.

I have to think about it long and hard :-), the extra "layer" makes
things more secure, I would only have to register username (for
auditing purposes) plus the "role" in session variables, which could
be md5()'d. Still, the extra layer is from a logical point of view
redundant and thus errorprone.

Still, it might be a good idea. If anyone cares to share their opinion
on the matter (or discuss how you handled this), TIA!

--
18:38-18:57
Fedora Core release 1 (Yarrow) Linux 2.4.22-1.2188.nptl

On Thu, 2004-04-29 at 18:57 +0200, Joolz wrote:
>
> I have to think about it long and hard :-), the extra "layer" makes
> things more secure, I would only have to register username (for
> auditing purposes) plus the "role" in session variables, which could
> be md5()'d. Still, the extra layer is from a logical point of view
> redundant and thus errorprone.
>
> Still, it might be a good idea. If anyone cares to share their opinion
> on the matter (or discuss how you handled this), TIA!

For myself, I handle security roughly as follows:

Password is stored as a salted md5, which means that two users with the
same password have different on-disk records of it.

Logged in user gets a session cookie which is an md5 of random data plus
a session id.  The session id is looked up, and the random data (which
is only known server-side) is validated.  Of course the session id is
predictable, but the random data is not.

I use a general user-id to access the database, rather than database
users and permissions.  I generally find that the database permissions
model is not a good fit to the permissions I want for my website.

Hope this is of interest,
                    Andrew McMillan.
-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053,  Manners St,  Wellington
WEB: http://catalyst.net.nz/             PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201       MOB: +64(21)635-694      OFFICE: +64(4)499-2267

-------------------------------------------------------------------------