#/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();

+-------+--------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                           |

+-------+--------------------------------------------------------------------------------------------------------+

| test  | CREATE TABLE `test` (

  `encrypted_text` longtext DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

+-------+--------------------------------------------------------------------------------------------------------+