Company

Products

Services

Partners

Media

Empress Technical News – November 2008

  Database Encryption - Safeguarding Confidential Data – Part 2
  Database With Encryption – Using Where Clause and Index

Introduction

Empress Database with Encryption that was shown in the September 2008 Technical News is used to show what happens when using an index and a “where clause” on encrypted data. The objective is to show that Empress Database with Encryption does two things. First, it can index encrypted data and select it. Second, it can select encrypted data from a table based on a “where clause”. All this can be done without any need to modify the SQL commands that are used for non-encrypted data.

In this example, the Linux system is Ubuntu Linux and the encryption algorithms are part of the “libgcrypt” library. This “libgcrypt” library is a standard part of most 2.6 Linux systems such as Red Hat Enterprise Linux, Novell Linux, Ubuntu, etc, and implements encryption algorithms such as AES (Advanced Encryption Standard) using key sizes of 128, 192 or 256 bits.

Preparing the EncryptionEnvironment and Database

Log onto a Linux system that has the Empress Database with encryption option installed. Type in “pwd” to find out what directory you are in. In this case it is “/home/alex”.

alex@knopit:~$ pwd
/home/alex

Use the Empress environment variable “MSCIPHERKEYINFO” to associate the new database name “testdb” with a short hexadecimal encryption key “74657374696e6731”. You can choose the most appropriate database name and encryption key.

alex@knopit:~$ MSCIPHERKEYINFO=" /home/alex/testdb" :74657374696e6731
alex@knopit:~$ export MSCIPHERKEYINFO
alex@knopit:~$ echo $MSCIPHERKEYINFO
/home/alex/testdb:74657374696e6731

Nowcreate the database “testdb” in “/home/alex” using the “empmkdb” command with the “cipher” option set to “AES256”. Encryption and decryption on this database will be performed using the Advanced Encryption Standard (AES) algorithm with a key size of 256 bits (32 bytes or 64 hexadecimal digits) through the “libgcrypt” library. For illustrative purposes only, we use a shorter and less secure key of 16 hex digits.

alex@knopit:~$ empmkdb -cipher AES256 testdb 

Start the Database and Create Two Tables, Hello1 & Hello2

Start up Empress Interactive SQL using the “empsql” command on the database “testdb”. The Interactive SQL prompt should appear.

alex@knopit:~$ empsql testdb
      EMPRESS V8.62 (Evaluation copy: licence will expire on Oct 29, 2009)
  (c) Copyright Empress Software Inc. 1983, 2008

1*

Create two new tables called “Hello1” and “Hello2” with two fields called “Name” and “Message”. “Name” will store 10 characters and “Message” will store 15 characters. The “Message” field in “Hello2” is also specified as “encrypted”.

1* create table Hello1 (Name character (10), Message character (15))
2* create table Hello2 (Name character (10), Message character (15) encrypted)

Store Values & Create Index on Two Tables, Hello2 Encrypted

Store three values for “Name” and “Message” into the “Hello1” and “Hello2” tables using the “insert” command. Use “Alex”, “Tom” and “Sam” for “Name” and “Hello World”, “Good Day” and “Welcome” for “Message”.

3* insert into Hello1 values (" Alex" , " Hello World" )
4* insert into Hello1 values (" Tom" , " Good Day" )
5* insert into Hello1 values (" Sam" , " Welcome" )
6* insert into Hello2 values (" Alex" , " Hello World" )
7* insert into Hello2 values (" Tom" , " Good Day" )
8* insert into Hello2 values (" Sam" , " Welcome" )

Create an index on “Messages” in both tables “Hello1” and “Hello2”.

9* create unique index Hello1x on Hello1( Message )
10* create unique index Hello2x on Hello2( Message )

Select ALL Data from TwoTables, Hello2 Encrypted

Select all the data from the “Hello1” and “Hello2” tables. The Name “Alex”, and the Message “Hello World” followed by “Sam” and “Welcome” is printed on the terminal. The Messages in “Hello2” are the same as in “Hello1” even though “Hello2” Messages are encrypted at the file level.

11* select * from Hello1
Name               Message
Alex                  Hello World
Tom                  Good Day
Sam                  Welcome

12*select * from Hello2
Name              Message
Alex                  Hello World
Tom                  Good Day
Sam                Welcome

Select Using a Where Clause from Two Tables, Hello2 Encrypted

Selectall the data from the “Hello1” and “Hello2” tables where the “Message” is equal to “Welcome”. The Message in “Hello2” is the same as “Hello1” even though “Hello2” is encrypted.

13* select * from Hello1 where Message = 'Welcome'

Name              Message
Sam                Welcome

14*select * from Hello2 where Message = 'Welcome'

Name              Message
Sam                Welcome

Show All Properties of Two Tables, Hello2 Encrypted

Show all properties of the “Hello1” and “Hello2” tables using the “display table” command with the option “all”. Note that the “Hello1” table is shown as Table #  6 by the “display table all” command and that the “Hello2” table is shown as Table #  7. In Empress, all tables are stored as files named “nnnn.rel”, so the “Hello1” table would be a file in the database named “0006.rel” and the “Hello2” table would be a file named “0007.rel”.

15* display table Hello1 all

***  Table: Hello1  ***

  Attributes:

      Name                                                       
      Message                                           

  Creator:        alex
  Indices:        UNIQUE BTREE Hello1x ON (Message)
  Lock Level:  RECORD

  Table #:                      6
  Records:                    3
    Record size:     26
 

16* display table Hello2 all

***  Table: Hello2  ***

  Attributes:

      Name                                                         
      Message                                                character(15,1) 

  Creator:        alex

  Indices:        UNIQUE BTREE Hello2x ON (Message)
  Lock Level:  RECORD
  Table #:        7
  Records:        3
  Record size: 27

Verify that the Index Data is Encrypted

Use theLinux escape “!” in Empress SQL to do a directory listing on “testdb”.
17* ! ls testdb
00010001.ix    00020009.dtf  00020016.dtf  00030013.dtf  00040002.ix    0005.rel        cdinator 
00010005.dtf  00020010.dtf  00020017.dtf  00030014.dtf  00040002.ixl 00060001.ix  dd_cache 
0001.rel          00020011.dtf  0002.rel          0003.rel          0004.rel          0006.rel        _index2
00020001.ix    00020012.dtf  00030001.ix    00040001.ix    00050001.ix    00070001.ix  _lock
00020008.dtf  00020013.dtf  00030001.ixl  00040001.ixl  00050001.ixl 0007.rel        _module

Use the Linux escape to do a grep for the word “Welcome”. Since “Hello1” is not encrypted on “Message”, we would expect to have grep find the word “Welcome” in the data file, 0006.rel, and the index file, 00060001.ix. Since “Hello2” is encrypted on “Message”, we would expect that grep will not find the word “Welcome” in the data file, 0007.rel or the index file 00070001.ix.

18* !grep Welcome testdb/*
Binary file testdb/0006.rel matches
Binary file testdb/00060001.ix matches

And this indeed is what happens.

All the SQL commands, other than the initial “create table” to specify which field is encrypted is exactly the same for both encrypted table and the non-encrypted table. The output of the select commands is also identical. The difference of the time to do the select commands between the encrypted table and the non-encrypted table is insignificant.

A Simple Example With More Data, Hello2 Encrypted

A “C” program was written to add 1,000,000 records to the two tables, “Hello1” and “Hello2”. The Name data was a sequence of 1 million values of the form “A0000nnnnnnn”.  The Message data was a sequence of 1 million values of the form “MSG00000nnnnnnn”. For each data pair, the values of “nnnnnnn” were the same.

The “C” program stub below was used as the starting point to generate the data and insert it into the two tables. Note: Only part of the whole “C” program is shown for brevity. Please email Empress to request a copy of the complete program.

printf (" Want to add %d records to table '%s'\n" , total, table_name)

              (i = 0 i < total i++)
              {
                              sprintf (buf1, " A%9.9d" , i)
                              (!mrputvs (rec_desc, attr1_desc, buf1))
                              {
                                              fprintf (stderr, " Failure:mroperr:%d => %s\n" ,
                                                              mroperr, mrerrmsg())
                                              break
                              }
                              sprintf (buf2, " MSG%11.11d" , i)
                              if (!mrputvs (rec_desc, attr2_desc, buf2))
                              {
                                              fprintf (stderr, " Failure:mroperr:%d => %s\n" ,
                                                              mroperr, mrerrmsg())
                                              break
                              }
                              mradd (rec_desc)
                              if ((i % 10000)== 0)
                                              printf (" Inserted %d\n" , i +1)
              }

EmpressSQL was invoked from the “shell” command so that the “time” command could be used to get timing values.

                    empcmd testdb " select * from Hello1 where Message = 'MSG00000900033'"

Name              Message

A000900033  MSG00000900033

real     
user      0m0.000s
sys        0m0.008s


alex@knopit:~$ time empcmd testdb " select * from Hello2 where Message = 'MSG00000900033'"

Name              Message

A000900033  MSG00000900033

real     
user      0m0.000s
sys        0m0.008s

Even with 1,000,000 records, there was little real difference in this example between the “select” times from the encrypted table “Hello2” and the non-encrypted table “Hello1”.

Appendix 1: A List of Commands Used in This Technical Note

The following may be cut and pasted to repeat the examples. You will need to change the database path location to suit your computer login. You may want to change the database name and the 16-digit hexadecimal encryption key as well.

pwd
MSCIPHERKEYINFO=" /home/alex/testdb" :74657374696e6731
export MSCIPHERKEYINFO
echo $MSCIPHERKEYINFO
empmkdb -cipher AES256 testdb
empsql testdb
create table Hello1 (Name character (10), Message character (15))
create table Hello2 (Name character (10), Message character (15) encrypted)
insert into Hello1 values (" Alex" , " Hello World" )
insert into Hello1 values (" Tom" , " Good Day" )
insert into Hello1 values (" Sam" , " Welcome" )
insert into Hello2 values (" Alex" , " Hello World" )
insert into Hello2 values (" Tom" , " Good Day" )
insert into Hello2 values (" Sam" , " Welcome" )
create unique index Hello1x on Hello1 (Message)
create unique index Hello2x on Hello2 (Message)
select * from Hello1
select * from Hello2
select * from Hello1 where Message = " Welcome"
select * from Hello2 where Message = " Welcome"
display table Hello1 all
display table Hello2 all
! ls testdb
! grep Welcome testdb/*

Empress Software Inc.
www.empress.com

 

Company
Information
GSA Contracts
Careers
Privacy Policy 
Contact US

Products
Empress RDBMS
Empress Servers
Empress API
Free Trial 
Empress iPad Apps 

Services
Consulting
Mobile Apps Development
Technical Support
Training Courses
Empress Extreme  

Partners
Distributors
Business Alliances
Partner Programs  
GSA Program
Distributors Program

Media 
Empress Markets
News and Events
Press releases
Technical News
Success Stories

        USA: 301-220-1919                 Canada & International:       905-513-8888         
Copyright © 2014         Empress Software Inc.           [email protected]           Join Empress Mailing List