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" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/19/2002 12:10 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
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
[EMAIL PROTECTED] 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
