Storing/retrieving DES3 data w/Perl

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Mon, 19 Aug 2002 11:10:01 -0800
Message-ID: <F001.004B939D.20020819111001@fatcity.com>

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

Received on Mon Aug 19 2002 - 14:10:01 CDT