Thread: MD5 use in PL/Perl

I'd like to be able to only store the database of usernames and passwrods
here locally as a md5 hash.  (in case the black hats come to visit....I'd
like to make life hard for them)  Using AuthPG, I should be able to create
a SQL call to postgres....but there is no native md5 hashing function.

In my ideal blue-sky world....the SQL call would like this:

SELECT name FROM Sample_table WHERE ( (userid='12345') AND
(userhashed=md5out('abc')) )

With the sample table looks like this:
Sample_table:
name    userid    userhashed
fred    12345     900150983cd24fb0d6963f7d28e17f72

I'd get the string 'fred' in name from Sample_table.


Idea 1)  A call to a shell script.    A question was asked back in 1999 if
there was a way to use a shell script in an SQL call.....that person had
no public responses.  Moved onto
Idea 2) use PL/Perl to take in the text to be hashed, and output the
hash.  Read the docs, looked on the list for more examples......


This perl code works as I'm expecting.
use MD5;
my $mdval = new MD5;
my $result ;
my $out;
$mdval->add('abc');
$result = $mdval->digest();
$out= unpack("H*" , $result );
print $out;

Attempting to xlate to PL/Perl

settle=# create function md5out3(varchar) returns varchar(32) as '
settle'# use MD5;
settle'# my $mdval = new MD5;
settle'# my $result ;
settle'# my $out;
settle'# $mdval->add($_[0]);
settle'# $result = $mdval->digest();
settle'# $out= unpack("H*" , $result );
settle'# return $out;'
settle-#  LANGUAGE 'plperl';
CREATE
settle=# select md5out3('fred');
ERROR:  creation of function failed : require trapped by operation mask at
(eval 6) line 2.


So.......

What did I do wrong WRT PL/Perl? (Let me guess....having perl call perl
modules causes breakage)  Should I be trying something different
to get to my desired end goal?

On Thu, 28 Dec 2000, Marc Rassbach wrote:

> Idea 1)  A call to a shell script.    A question was asked back in 1999 if
> there was a way to use a shell script in an SQL call.....that person had
> no public responses.  Moved onto
> Idea 2) use PL/Perl to take in the text to be hashed, and output the
> hash.  Read the docs, looked on the list for more examples......
Nice try :) Good idea, however, you should take into account two things:
a) your functions run under "use Safe" and very restricted as far as what
they could do
b) your function is _not_ a package, it is only a sub, and hence cannot
'use' anything.

A thing to try for you is:
a) change plperl, and where it does 'require Safe;' do 'use MD5; require
Safe;', recompile, etc.

b) change plperl and change permit_only(':default') to
permit_only(':default','require')

It MIGHT work. You might have to add more ops that MD5 code uses though...

Good luck :)

Actually, a thing to consider would be to have a 'untrusted' PL/perl
language in postgres with use Safe disabled, along with a 'trusted' one.
(pluntrustedperl? plunsafeperl?) Same could be done for pltcl...

Comments?

Alex Pilosov <alex@pilosoft.com> writes:
> Actually, a thing to consider would be to have a 'untrusted' PL/perl
> language in postgres with use Safe disabled, along with a 'trusted' one.
> (pluntrustedperl? plunsafeperl?) Same could be done for pltcl...

Jan Wieck has already created an 'unsafe' pltcl variant for Pg 7.1.
I see no objection to making an unsafe plperl as well; who wants to
step up to the plate and do the work?

            regards, tom lane