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;

[download]

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#

[download]

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

[download]

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.

In reply to MySQL AES Encryption with CBC mode by JayBee

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.