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:

mkdir $HOME/tmp

Copy the files Crypt-Blowfish-2.10.tar.gz and Crypt-CBC-2.30.tar.gz into $HOME/tmp

mkdir $HOME/lib

cd $HOME/tmp

gunzip Crypt-Blowfish-2.10.tar.gz

tar –xvf Crypt-Blowfish-2.10.tar

gunzip Crypt-CBC-2.30.tar.gz

tar –xvf Crypt-CBC-2.30.tar

cd $HOME/Crypt-Blowfish-2.10

perl ./Makefile.PL PREFIX=$HOME/lib LIB=$HOME/lib

make

make test

make install

cd Crypt-CBC-2.30

perl ./Makefile.PL PREFIX=$HOME/lib LIB=$HOME/pruning/lib

make

make test

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:

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.

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 is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science.
Ember lives in Denver and work from home for XTIVIA, leading a team of Db2 DBAs.