Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

MySQL AES Encryption with CBC mode

by JayBee (Scribe)
on May 16, 2017 at 03:04 UTC ( #1190356=perlquestion: print w/replies, xml ) Need Help??
JayBee has asked for the wisdom of the Perl Monks concerning the following question:

Trying to connect to DB to convert passwords to encrypt with AES in CBC mode (vs ECB) I've learned that I have to SET the SESSION before executing any SQL commands. So these two work when I attempt to do it with phpMyAdmin:

SET @@session.block_encryption_mode = 'aes-128-cbc'; SELECT ID, HEX(AES_ENCRYPT(Password,'$key',RANDOM_BYTES(16))) FROM Use +r WHERE 1; ## OR SET SESSION block_encryption_mode = 'aes-128-cbc'; SELECT ID, HEX(AES_ENCRYPT(Password,'$key',RANDOM_BYTES(16))) FROM Use +r WHERE 1;

but none will work when I attempt it though my script. Here's the basics:

use strict; use CGI ':standard'; use DBI; use DBD::mysql; our ($sth,$dbh,%Set,@Out); &DBCredentials; # assigns %Set; print header,start_html('test'); DBRun("SET SESSION block_encryption_mode='aes-128-cbc'; SELECT ID, HEX(AES_ENCRYPT(Password,'$Set{AESKey}',RANDOM_BYTES(16))) FROM User WHERE 1"); while (my @ar=$sth->fetchrow_array){ my $len=length($ar[1]); push @Out,"$ar[0]: L=$len -- $ar[1]<br />\n"; } &DBEnd; print shift(@Out)."\n" while @Out; sub DBConnect { my $er; my $dsn='DBI:mysql:database='.$Set{DBName}; $dbh=DBI->connect($dsn, $Set{DBUser}, $Set{DBPass}) || ($er=1); if ($er){ myErr('DB Start Error'); } } ##DBConnect## sub DBRun { my $er; &DBConnect; $sth=$dbh->prepare($_[0]) || ($er=1); $sth->execute || ($er=1) if !$er; if ($er){ myErr('DB Execute Error', $_[0]); } } ##DBRun## sub DBDo { my $er; &DBConnect; $dbh->do($_[0]) || ($er=1); if ($DBI::err || $er){ myErr('DB Do Error', $_[0], $DBI::errstr); } $dbh->disconnect(); } ##DBDo## sub DBEnd {$sth->finish; $dbh->disconnect; } ##DBEnd#

Not sure what this is exactly, but I've also tried adding Callbacks to the DBConnect portion, but that didn't work either:

sub DBConnect { my $er; my $DBCall={ 'connect_cached.connected' => sub { shift->do("SET SESSION block_encryption_mode='aes-128-cbc'") +; } }; my $dsn='DBI:mysql:database='.$Set{DBName}; $dbh=DBI->connect($dsn, $Set{DBUser}, $Set{DBPass}, { Callbacks => $DB +Call }) || ($er=1); if ($er){ myErr('DB Start Error'); } } ##DBConnect##

It all leads to an error being generated. My next solution would be to encrypt & decrypt in perl module (prior to sending to mySQL), not sure that's such a great idea just yet.

Thanks in advance for your guidance.

Replies are listed 'Best First'.
Re: MySQL AES Encryption with CBC mode
by haukex (Abbot) on May 16, 2017 at 08:35 UTC
    It all leads to an error being generated.

    What error is that exactly? I unfortunately don't have a MySQL instance handy here, but I do see that in DBRun("SET SESSION block_encryption_mode='aes-128-cbc'; SELECT ID, ... you are trying to run two SQL statements at once, which isn't supported by default, you should try splitting that into two DB calls.

    In general, your code structure could be cleaned up a bit. For example, you use the older style calling of functions &DBCredentials, instead DBCredentials() is recommended nowadays. Declaring all your variables at the top of the script doesn't help much in achieving proper scoping - e.g. instead of having sub DBConnect set the $dbh variable that is outside its scope, you could have it return the new handle instead. Also, you unconditionally call &DBConnect; at the beginning of sub DBRun and DBDo, when it would make more sense to connect only once at the beginning of the script. Lastly but probably most importantly, instead of interpolation ("...(Password,'$Set{AESKey}', ...") you should use placeholders - see Bobby Tables.

    I don't know what your reasons are for storing passwords encrypted - why are you not hashing them instead?

      Yes, running two commands as one is apparently the problem, but I'm not certain running them separate terminates the initial assigned "session" and reverts to defaults with the second command.

      I'm not 100% sure the difference between "encrypted" and "hashed". My best guess: hashed is one way; and encrypted can be decrypted... so my answer: I currently need the option to decrypt for additional software access (which is not to be tinkered with).

        running them separate terminates the initial assigned "session" and reverts to defaults with the second command.

        That's part of what I meant when I was talking about calling DBConnect every time DBRun or DBDo gets called: This will establish a new connection to the database for every command you run, so that will certainly cause the session to be ended and a new one begun. Have you tried to see what happens when you connect only once at the beginning of the script and then run your commands separately?

Re: MySQL AES Encryption with CBC mode
by thanos1983 (Vicar) on May 16, 2017 at 12:29 UTC

    Hello JayBee,

    I have never used MySQL for this kind of purposes, but from you description I think you are looking for this module (Crypt::Rijndael ).

    From the documentation (blocksize):

    The blocksize for Rijndael is 16 bytes (128 bits), although the algori +thm actually supports any blocksize that is any multiple of our bytes +. 128 bits, is however, the AES-specified block size, so this is all +we support.

    $cipher = Crypt::Rijndael->new( $key [, $mode] )

    Create a new Crypt::Rijndael cipher object with the given key (which m +ust be 128, 192 or 256 bits long). The additional $mode argument is t +he encryption mode, either MODE_ECB (electronic codebook mode, the de +fault), MODE_CBC (cipher block chaining, the same that Crypt::CBC doe +s), MODE_CFB (128-bit cipher feedback), MODE_OFB (128-bit output feed +back), or MODE_CTR (counter mode).

    Update: Hello again, I wrote a script to replicate the error of your script. I think you are missing the backslash on \ on the @ mysql commands. See example bellow.

    #!/usr/bin/perl use DBI; use strict; use warnings; use Data::Dumper; use Config::Simple; $|=1; #flush every time the program my $path = 'conf.ini'; my %config = (); my $checkExist = ""; sub mysql { Config::Simple->import_from("".$path."", \%config) or die Config::Simple->error(); my $dbh = DBI->connect("dbi:mysql::".$config{'MySQL.host'}.":".$co +nfig{'MySQL.port'}."", "".$config{'MySQL.user'}."", "".$config{'MySQL.pass'}."", { 'PrintError' => 1, 'RaiseError' => 1 , 'AutoInactiveD +estroy' => 1 } ) or die "Could not connect to ". $config{'MySQL.host'} .": ". $DB +I::errstr ."\n"; my $databases = $dbh->do("SHOW DATABASES LIKE '".$config{'MySQL.db +'}."'") or die "Error: " .dbh->errstr. "\n"; if ($databases eq 1) { printf "Database: ". $config{'MySQL.db'} ." exists not creating: " +. $config{'MySQL.db'} ."\n"; } else { printf "Database: ". $config{'MySQL.db'} ." does not exist creatin +g: ". $config{'MySQL.db'} ."\n"; $checkExist = $dbh->do("CREATE DATABASE IF NOT EXISTS `".$config{' +MySQL.db'}."`") or die "Could not create the: ".$config{'MySQL.db'}." error: " +. $dbh->errstr ."\n"; } # End of else $dbh->do("USE ".$config{'MySQL.db'}."") or die "Error: " .dbh->errstr. "\n"; my $tables = $dbh->do("SHOW TABLES FROM `".$config{'MySQL.db'}."` WHERE Tables_in_".$config{'MySQL.db'}." LIKE '".$config{'MySQL.table'}."'") or die "Error: ".dbh->errstr. "\n"; if ($tables eq 1) { printf "Table: ".$config{'MySQL.table'}." exists not creating: ".$ +config{'MySQL.table'}."\n"; } else { printf "Table: ".$config{'MySQL.table'}." does not exist creating: + ".$config{'MySQL.table'}."\n"; $checkExist = $dbh->prepare("CREATE TABLE IF NOT EXISTS `".$config +{'MySQL.table'}."` ( `id` int(11) NOT NULL AUTO_INCREMENT, `UnixTime` int(11) NOT NULL, `losses` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREM +ENT=1 ;"); if (!$checkExist->execute()) { die "Error: ". $checkExist->errstr ."\n"; } } # End of else $checkExist = $dbh->prepare("SELECT \@\@session.block_encryption_m +ode = 'aes-128-ecb';"); if (!$checkExist->execute()) { die "Error: ". $checkExist->errstr ."\n"; } my $range = 50; my $minimum = 100; my $random_number = int(rand($range)) + $minimum; my $time = time(); my $losses = $time . ' ' . $random_number; $checkExist = $dbh->prepare("INSERT IGNORE INTO `".$config{'MySQL. +table'}. "` (`UnixTime`, `losses`) VALUES ('".$time."','".$random_number."') "); if (!$checkExist->execute()) { die "Error: ". $checkExist->errstr ."\n"; } my $statement = "SELECT * FROM `".$config{'MySQL.table'}."` WHERE +1"; my $hash_ref = $dbh->selectall_hashref($statement, 'id'); $checkExist->finish(); $dbh->disconnect() or warn "Error disconnecting: $DBI::errstr\n"; return $hash_ref; } # End of mysql sub my $output_ref = mysql(); print Dumper($output_ref); __DATA__ $ perl mysql.pl Database: PerlMonks does not exist creating: PerlMonks Table: Data does not exist creating: Data $VAR1 = { '1' => { 'losses' => 128, 'UnixTime' => 1494975837, 'id' => 1 } };

    Update2: Sample of conf.ini for replication purposes:

    [MySQL] user=user pass=password host=localhost port=3306 db=PerlMonks table=Data

    Hope this helps.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
      Yes, Crypt::Rijndael is my next plan. If no further help on this, might as well get started...

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1190356]
Approved by Athanasius
Front-paged by Corion
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others pondering the Monastery: (8)
As of 2018-06-19 15:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?



    Results (114 votes). Check out past polls.

    Notices?