Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
10.3.22
-
Ubuntu 20.04
Description
I try to use the following Perl script to insert an encrypted text into my table, however error message "Incorrect string value: '\xF5\xE5\x83\xFF\x8A\xE8...' for column `mydb`.`test`.`encrypted_text` at row 1" is returned.
It seems that my fully UTF-8 supported database and table cannot store encrypted string characters. However, it is worked flawlessly on older version of MariaDB, such as MariaDB version 10.3.17 on CentOS 8.
#/usr/bin/perl
|
|
|
use strict; |
use DBI; |
use Crypt::CBC; |
|
|
my $dsn; |
my $dbh; |
my $sth; |
my $sql; |
|
|
sub _encrypt_str { |
my ($plaintext, $key) = @_; |
my ($ok, $cipher, $encrypted); |
|
|
$encrypted = ''; |
$ok = 0; |
|
|
$cipher = Crypt::CBC->new(-key => $key, -cipher => 'Rijndael'); |
if ($cipher) { |
$encrypted = $cipher->encrypt($plaintext); |
$ok = 1; |
}
|
|
|
return ($ok, $encrypted); |
}
|
|
|
|
|
sub _generateRandomStr { |
my ($option, $max_len) = @_; |
my ($max_ascii_value, $char, $stop_run, $cnt, $result, @ascii_list); |
|
|
$option = ($option eq '')? 'A' : uc($option); |
$max_len = ($max_len + 0 <= 0)? 10 : $max_len; |
|
|
if ($option eq 'N') { |
for (my $i = 48; $i <= 57; $i++) { |
push @ascii_list, $i; |
}
|
|
|
$max_ascii_value = 57; |
}
|
elsif ($option eq 'S') { |
for (my $i = 65; $i <= 90; $i++) { |
push @ascii_list, $i; |
}
|
|
|
for (my $i = 97; $i <= 122; $i++) { |
push @ascii_list, $i; |
}
|
|
|
$max_ascii_value = 122; |
}
|
else { |
for (my $i = 48; $i <= 57; $i++) { |
push @ascii_list, $i; |
}
|
|
|
for (my $i = 65; $i <= 90; $i++) { |
push @ascii_list, $i; |
}
|
|
|
for (my $i = 97; $i <= 122; $i++) { |
push @ascii_list, $i; |
}
|
|
|
$max_ascii_value = 122; |
}
|
|
|
$result = ''; |
$stop_run = $cnt = 0; |
while (!$stop_run) { |
my $this_ascii = sprintf("%.0f", rand($max_ascii_value)); |
my $valid_value = 0; |
|
|
foreach my $ascii (@ascii_list) { |
if ($ascii == $this_ascii) { |
$valid_value = 1; |
}
|
last if ($valid_value); |
}
|
|
|
if ($valid_value) { |
$char = chr($this_ascii); |
$result .= $char; |
}
|
|
|
if (length($result) >= $max_len) { |
$stop_run = 1; |
}
|
|
|
if ($cnt >= 90000) { |
$stop_run = 1; |
}
|
else { |
$cnt++; |
}
|
}
|
|
|
return $result; |
}
|
|
|
#***************************************************************
|
# Note: The table is created as follows:
|
#
|
# create table `test` (encrypted_text longtext) engine=InnoDB default charset=utf8mb4;
|
#***************************************************************
|
|
|
$dsn = "DBI:mysql:database=mydb;host=localhost;"; |
$dbh = DBI->connect($dsn, 'myusername', 'mypassword', {AutoCommit => 1, PrintError => 0, RaiseError => 0}); |
$sth = $dbh->prepare("use mydb"); |
$sth->execute(); |
$sth->finish(); |
|
|
my $seed = _generateRandomStr('A', 32); |
my ($ok, $encrypted_text) = _encrypt_str('It is my secret message', $seed); |
|
|
$sql = <<__SQL; |
INSERT INTO test
|
(encrypted_text)
|
VALUES
|
( ? )
|
__SQL
|
|
|
$sth = $dbh->prepare($sql); |
if ($sth->execute($encrypted_text)) { |
print "Save successfully\n"; |
}
|
else { |
print "Error: " . $sth->errstr; |
}
|
$sth->finish(); |
$dbh->disconnect(); |
Here is my table definition:
+-------+--------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------+
|
| test | CREATE TABLE `test` (
|
`encrypted_text` longtext DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
|
+-------+--------------------------------------------------------------------------------------------------------+
|