Re: Storing/retrieving DES3 data w/Perl

From: <Jared.Still_at_radisys.com>
Date: Tue, 20 Aug 2002 10:27:18 -0800
Message-ID: <F001.004BA318.20020820102718@fatcity.com>

Well, here's one way to do it.

This converts the data to hex before storing in Oracle.

Jared

#!/usr/bin/perl

use DBI qw(:sql_types);
use Crypt::TripleDES;

my ($DBname, $Uname, $Pword ) = ('MYDB','MYUSER','MYPASS'); my $DESpass = "abcdefgh";

$dbh = DBI->connect(

        "dbi:Oracle:dv01","scott","tiger",
        {
                RaiseError => 1,
                AutoCommit => 0
        }

);

my $DES3 = new Crypt::TripleDES;
my $Cryptpass = $DES3->encrypt3 ( $Pword, $DESpass );
my $hexPassword = unpack("H*",$Cryptpass);

print "Clear  : $Pword\n";

print "Decrypt: ", $DES3->decrypt3($Cryptpass, $DESpass),"\n"; print "Hex : $hexPassword\n";

$dbh->do('truncate table valid_connection');

$sth = $dbh->prepare(qq(

                INSERT INTO valid_connection(
                        db_alias, db_username, db_password, clear_password
                )
                VALUES(?,?,?,?)
        )

);

$sth->execute($DBname, $Uname, $hexPassword, $Pword);

$sth = $dbh->prepare(qq

        {
                SELECT
                        db_alias, db_username, db_password, clear_password
                FROM valid_connection
        }

);

$sth->execute;

while ( my $hr = $sth->fetchrow_hashref) {

                 my $clearPassword = $DES3->decrypt3(pack("H*", 


$hr->{DB_PASSWORD}), $DESpass);

                 print "$hr->{DB_ALIAS} $hr->{DB_USERNAME} 

$clearPassword\n";

}

$dbh->disconnect;

"Jesse, Rich" <Rich.Jesse_at_qtiworld.com>
Sent by: root_at_fatcity.com
08/19/2002 12:10 PM
Please respond to ORACLE-L  

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Storing/retrieving DES3 data w/Perl

Hi all (especially Jared!),

I'm trying to store a password in an Oracle 8.0.5 table using Perl and DES3.
I've tried making the attached Perl, but I can never get the password to return correctly. My guess is that there is some casting going on that is hosing up the raw data.

I've got the "valid_connection" table defined as:

CREATE TABLE VALID_CONNECTION (

  DB_ALIAS     VARCHAR2 (32)  NOT NULL, 
  DB_USERNAME  VARCHAR2 (32)  NOT NULL, 
  DB_PASSWORD  RAW (256)

..but I can change it to be whatever. I've tried several incarnations of using "utl_raw" and not using it, along with "SQL_BINARY" datatype on the bind, but I'm not having any luck.

I seem to learn best by example. Does anyone have one?

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI 

USA p.s. No DBMS_OBFUSCATION_TOOLKIT, since this is only 8.0...

#!/usr/bin/perl

use DBI qw(:sql_types);
use Crypt::TripleDES;

my ($DBname, $Uname, $Pword, $Cryptpass, $DES3, $DESpass);

$Rdbh = DBI->connect("dbi:Oracle:THISDB","SOMEUSER","SOMEPASS",

                 { RaiseError => 1, AutoCommit => 0 });

$DBname = "MYDB";
$Uname = "MYUSER";
$Pword = "MYPASS";

$DES3 = new Crypt::TripleDES;
$DESpass = "abcdefgh";
$Cryptpass = $DES3->encrypt3 ( $Pword, $DESpass );

$Rsth = $Rdbh->prepare(qq(INSERT INTO valid_connection (db_alias,
db_username, db_password) VALUES(:b1,:b2,utl_raw.cast_to_raw(:b3))));
$Rsth->bind_param(":b1",$DBname);
$Rsth->bind_param(":b2",$Uname);
$Rsth->bind_param(":b3",$Cryptpass, SQL_BINARY);
$Rsth->execute;
$Rsth->finish;

$Rsth = $Rdbh->prepare(qq{SELECT db_alias, db_username,
utl_raw.cast_to_varchar2(db_password) FROM valid_connection});
$Rsth->execute;
$Rsth->bind_columns(\($DBname, $Uname, $Cryptpass));
while ($Rsth->fetch)
{

                 $Pword = $DES3->decrypt3 ( $Cryptpass, $DESpass );
                 print "$DBname $Uname $Pword\n";

}
$Rsth->finish;

$Rdbh->disconnect;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Aug 20 2002 - 13:27:18 CDT