Thread: Re: Inserting data of two other tables [Now deleting ...]

Well, at last I've been able (with some help ;-D) to do it.

The query has been:

SELECT tarneto FROM detalltrajectes d WHERE d.journey = (SELECT c.pkey
FROM CAPTRAJECTES WHERE c.pkey = d.journey AND fecha = 20030423);

With this one I've been able to "see" what was I going to remove, and
with this one:

DELETE FROM DETALLTRAJECTES WHERE journey = (SELECT pkey FROM
CAPTRAJECTES WHERE pkey = journey AND fecha = 20030423);

I have removed the rows. I have to mention that I first tried to "clone"
the query, changing SELECT tarneto for DELETE obviously, but it didn't
work. The parser said:

    ERROR:  parser: parse error at or near "d"

So what I did was to remove all the alias and it worked.... is it normal??

Thanks to all for your help, and specially to Nabil.

Josep Sànchez
   [papapep]

Am Mit, 2003-05-28 um 18.59 schrieb papapep:

> So what I did was to remove all the alias and it worked.... is it normal??

Yes, it has some problems with aliases.

> Thanks to all for your help, and specially to Nabil.

np
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

How do I send an MD5 password to pgsql?  (I'm programming my own front-end)

Pgsql provides a 4-byte 'salt', that you must somehow use with your
password for MD5.  The trouble is, I don't know how.

I have been trying sending the MD5 digest from (password + salt), or
from (salt + password), but it doesn't work.  How exactly does the
salt fit in the picture?

Thanks,

Marc

"M. Bastin" <marcbastin@mindspring.com> writes:
> How do I send an MD5 password to pgsql?  (I'm programming my own front-end)
> Pgsql provides a 4-byte 'salt', that you must somehow use with your
> password for MD5.  The trouble is, I don't know how.

Step 1: compute 32-byte MD5 checksum of cleartext password concatenated
with username.  (BTW this checksum, with "md5" on the front, is what is
actually stored in pg_shadow.)

Step 2: compute 32-byte MD5 checksum of the 32-byte result of step 1
concatenated with the 4-byte salt from the server.  Stick "md5" on the
front and send it to the server.

            regards, tom lane

Thanks Tom, You're my hero!

However I must be doing something wrong.  This is what I do:

"md5" + MD5( MD5(Password + UserName) + Salt)

Is this a correct interpretation of your explanation?  (To this I
still need to add the zero byte for termination, isn't it?  That's
what I'm doing now anyway.)


>"M. Bastin" <marcbastin@mindspring.com> writes:
>>  How do I send an MD5 password to pgsql?  (I'm programming my own front-end)
>>  Pgsql provides a 4-byte 'salt', that you must somehow use with your
>>  password for MD5.  The trouble is, I don't know how.
>
>Step 1: compute 32-byte MD5 checksum of cleartext password concatenated
>with username.  (BTW this checksum, with "md5" on the front, is what is
>actually stored in pg_shadow.)
>
>Step 2: compute 32-byte MD5 checksum of the 32-byte result of step 1
>concatenated with the 4-byte salt from the server.  Stick "md5" on the
>front and send it to the server.
>
>            regards, tom lane

"M. Bastin" <marcbastin@mindspring.com> writes:
> However I must be doing something wrong.  This is what I do:
> "md5" + MD5( MD5(Password + UserName) + Salt)
> Is this a correct interpretation of your explanation?

Looks right to me.  Do you have the MD5 algorithm correct?

> (To this I
> still need to add the zero byte for termination, isn't it?

Yeah, IIRC the contents of the Password message are a zero-terminated
string.  Check the protocol document.

You might try testing with plain-text password auth method to make sure
you have the basic Password-message mechanics down, before you go on
with MD5.

            regards, tom lane

At 11:06 AM -0400 5/29/03, Tom Lane wrote:
>"M. Bastin" <marcbastin@mindspring.com> writes:
>>  However I must be doing something wrong.  This is what I do:
>>  "md5" + MD5( MD5(Password + UserName) + Salt)
>>  Is this a correct interpretation of your explanation?
>
>Looks right to me.  Do you have the MD5 algorithm correct?

I'm using the one provided with my development tool.  Is there some
way I could calculate a MD5 digest with a known good tool and compare
it with my result?

>You might try testing with plain-text password auth method to make sure
>you have the basic Password-message mechanics down, before you go on
>with MD5.

Exactly, that's what I did and it works, so I'm pretty sure there
must be something wrong with the MD5 algorithm I use, (or else I
don't extract the salt properly out of the data stream but I'm quite
sure I've got that covered).

...

Mmmm...  I've just done some testing and my MD5 function gives me 16
bytes instead of 32.  I'll look into this.

"M. Bastin" <marcbastin@mindspring.com> writes:
>> Looks right to me.  Do you have the MD5 algorithm correct?

> I'm using the one provided with my development tool.  Is there some
> way I could calculate a MD5 digest with a known good tool and compare
> it with my result?

Well, you could compute just MD5(Password + User) and compare that to
what's stored in pg_shadow.  Another possibility is to add some
debugging printouts to libpq and see what it computes (look at
pg_password_sendauth() in src/interfaces/libpq/fe-auth.c).

It could be something silly like including trailing nulls into what's
processed by MD5 --- I'm pretty sure you should *not* do that, for
either password or user name.

            regards, tom lane

Tom Lane wrote:
> "M. Bastin" <marcbastin@mindspring.com> writes:
>>I'm using the one provided with my development tool.  Is there some
>>way I could calculate a MD5 digest with a known good tool and compare
>>it with my result?
>
> Well, you could compute just MD5(Password + User) and compare that to
> what's stored in pg_shadow.  Another possibility is to add some
> debugging printouts to libpq and see what it computes (look at
> pg_password_sendauth() in src/interfaces/libpq/fe-auth.c).
>

You can find test vectors for MD5 here:
ftp://ftp.rfc-editor.org/in-notes/rfc1321.txt

See near the bottom, A.5 Test suite. For convenience, here is that section:

A.5 Test suite

    The MD5 test suite (driver option "-x") should print the following
    results:

MD5 test suite:
MD5 ("") = d41d8cd98f00b204e9800998ecf8427e
MD5 ("a") = 0cc175b9c0f1b6a831c399e269772661
MD5 ("abc") = 900150983cd24fb0d6963f7d28e17f72
MD5 ("message digest") = f96b697d7cb7938d525a2f31aaf161d0
MD5 ("abcdefghijklmnopqrstuvwxyz") = c3fcd3d76192e4007dfb496cca67e13b
MD5 ("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") =
d174ab98d277d9f5a5611c2c9f419d9f
MD5 ("123456789012345678901234567890123456789012345678901234567890123456
78901234567890") = 57edf4a22be3c955ac49da2e2107b67a

Note that there is no '\n' in that last line-wrapped example. If you can
produce hashes matching these, you should be OK.

Joe

M. Bastin wrote:
> Are there different MD5 standards?
>
> The rfc (http://www.faqs.org/rfcs/rfc1321.html) says:
>
> The algorithm takes as input a message of arbitrary length and produces
> as output a 128-bit [= 16 bytes] "fingerprint" or "message digest" of
> the input.
>
> My IDE's MD5 function produces 16 bytes.  Yet for pgsql MD5 should
> produce 32 bytes.

16 binary bytes == 32 bytes in hex

You need to convert IDE's MD5 function output to hex.

Joe

>You need to convert IDE's MD5 function output to hex.

Thank you for this epiphany!

I had a few other minor hurdles to take (padding leading zeros and
converting to lowercase, while converting to hex), but now I can log
in using MD5 too.

You guys made my day!  Thanks again for your support,

Marc

On Thu, 29 May 2003, M. Bastin wrote:

> >You need to convert IDE's MD5 function output to hex.
>
> Thank you for this epiphany!
>
> I had a few other minor hurdles to take (padding leading zeros and
> converting to lowercase, while converting to hex), but now I can log
> in using MD5 too.
>
> You guys made my day!  Thanks again for your support,

Not that I need it, but perhaps you could write up what you
did, with sample code, and submit it to be included in the
docs?

Gord