Basic Perl Encryption for DBAs

One of my specialties and passions is scripting. I play a major role in writing or maintaining just about every DB2 maintenance, DB2 build, and DB2 data pruning script that my company writes/uses.

One thing I’ve found a need to do is to encrypt and decrypt a single value. In my case, I have the script store a value in a separate file, and then decrypt and use the value when the script needs it. This eliminates the security risk of storing in plain text, though it is obviously not the most secure practice, as one script has all the information needed to both encrypt and decrypt. And we’re not talking 256-bit encryption or anything crazy here.

But there are purposes for this. Do you keep certain connection info confidential? Maybe even a table name or something that is then a decryption key for another process.

In any case, it took me a while to figure it out, so I thought I’d share the solution with my readers.

This is actually the only script I use that requires modules. I wasn’t about to write encryption and decryption modules myself, even if I have some clients where the modules cannot be installed. This solution only works on Linux – I was unable to get it working on AIX and the one time I’ve needed it there, I came up with another solution. I have not yet tested it on Windows.

Installing the modules

There are really two options when installing Perl modules. You can install them along with the root Perl installation, or you can add them locally as a single user. Whenever possible, I recommend just asking your System Administrator for them, and letting him figure it out.

But different clients have different guidelines about this sort of thing. I actually know of a client where I wouldn’t even be allowed to install them locally. Most clients wouldn’t care, and some clients wouldn’t care as long as it was only locally. You simply have to judge based on the level of security and red tape at your company.

You can get the files for the modules from CPAN: http://www.cpan.org/
The modules you need are Crypt::Blowfish and Crypt::CBC
Once you have the files, here are the instructions that I follow:

  1. mkdir $HOME/tmp
  2. Copy the files Crypt-Blowfish-2.10.tar.gz and Crypt-CBC-2.30.tar.gz into $HOME/tmp
  3. mkdir $HOME/lib
  4. cd $HOME/tmp
  5. gunzip Crypt-Blowfish-2.10.tar.gz
  6. tar –xvf Crypt-Blowfish-2.10.tar
  7. gunzip Crypt-CBC-2.30.tar.gz
  8. tar –xvf Crypt-CBC-2.30.tar
  9. cd $HOME/Crypt-Blowfish-2.10
  10. perl ./Makefile.PL PREFIX=$HOME/lib LIB=$HOME/lib
  11. make
  12. make test
  13. make install
  14. cd Crypt-CBC-2.30
  15. perl ./Makefile.PL PREFIX=$HOME/lib LIB=$HOME/pruning/lib
  16. make
  17. make test
  18. make install

Encrypting the value

In order to encrypt the value, you’ll need to source the lib directory where you just installed the Perl modules. This line near the top of your perl script should accomplish that:

# add ~/lib dir to @INC
use lib "./lib/";

In my case, my lib filesystem is relative to the directory where I’m executing the script from – you could use an absolute path or whatever makes sense for you. My scripts exist across a number of clients with different filesystem requirements and layouts, so I try to keep it as simple as possible.

You’ll also need to select an encryption key. This needs to be a hex number that is 58 characters long. It’s easiest if you set it in a variable like this:

$key = "124b79493968222d3e7c3e245098204d4b09812244423e554a2576774a";

The same key must be specified on encryption and decryption or else it won’t work.

In this example, I’m using the encrypted value across different executions of the script, so I’m storing it in a separate file. You could choose to store it in a variable instead if you were using it within the same execution of the script.

First, you need whatever data you’re going to encrypt. In my case, I prompt the user for an input of the text:

        print OUTPUTFILE "printing encrypted string to string file and exiting\n";
        print "Enter string for $db_user: ";
        system('stty','-echo');
        my $str_to_encrypt = ;
        chomp $str_to_encrypt;
        system('stty','echo'); 
        print "\n";
    open STRFILE, "> $str_file"; 
    print STRFILE &encrypt_string($str_to_encrypt);
        close STRFILE;
        chmod 0600, $str_file;

Most of that is actually pretty straight forward – notice however the ‘-echo’ followed by the ‘echo’ options on stty before and after the user enters text. The first one, with ‘-echo’, turns off showing typed characters – this makes it so no one looking over your shoulder could see what you’re typing. The second turns echo back on, because, believe me, it’s confusing if you accidentally leave it off.

Here’s the code I use to encrypt the string – the string is passed into the function in plain text. You could either use the code above to get user input, or grab it from wherever makes sense – any string variable in plain text could be passed in.

#----------------------------------------------------------------------#
# Subroutine    : encrypt_str
#  Purpose      : To return an encrypted string that may be stored in 
#         the cfg file
#  Inputs       : Clear-text string
#  Output       : Encrypted string
#----------------------------------------------------------------------#
sub encrypt_str {
    my $str = $_[0];
    my $key = pack ("H16", $key);
    my $cipher = Crypt::CBC->new( -key    => $key,
                -cipher => 'Blowfish',
                            );
    my $cipher_str = $cipher->encrypt($str);
    return $cipher_str;

}

Decrypting the value

Of course decrypting requires that the same lib directory be sourced and a value for $key that matches the value used to encrypt the string. Here’s what my decrypting function looks like:

#----------------------------------------------------------------------#
# Subroutine    : decrypt_str
#  Purpose      : To return an plain-text string that may be used 
#          given an encrypted string
#  Inputs       : file name containing encrypted string
#  Output       : Clear-text string
#----------------------------------------------------------------------#
sub decrypt_str {
    my $str_file = $_[0];
    unless ( -e $str_file) {
    die "$str_file does not exist. Run $0 with the --encrypt_str"
        ." flag to create it. Use $0 --help for full usage "
        ."information\n";
    }
    open STRFILE, "< $str_file";
    my $encrypted_str = ;
    close STRFILE;
    my $key = pack ("H16", $key);
    my $cipher = Crypt::CBC->new( -key    => $key,
                                -cipher => 'Blowfish',
                            );
    my $str = $cipher->decrypt($encrypted_str);
    return $str;
}

Of course your die text would probably be different than mine. The particular script I use this in has an option to execute only to encrypt the string and place it in the file. The string is required for every other execution of the script, so naturally I check to see if the file exists before trying to do anything with it.

Conclusion

When I was writing this code a few years ago, I struggled with it. I did many google searches to put it all together, and now I’m sharing it, in hopes that it will help someone else.

Is this a super-secure way to store data? No, no it’s not. Is it better than putting sensitive data you must store in plain text? Why yes, yes it is.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 549

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.