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?  

Marc Rassbach writes:

> 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.

You can't use external modules ("use", "require") for security reasons.

FWIW, if I were to write an MD5 function then I'd take one of the
implementations floating around (mhash, Kerberos, OpenSSL, RFC) and make a
C function wrapper around it.

Incidentally, someone has already done this for the upcoming 7.1 release,
but since the function call interface has changed the back port won't be
trivial.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/