Using the QlikView Expressor Encryption & Decryption Functionality

    In your applications, you may need to ensure confidentiality by encrypting certain fields of data such as social security numbers.  QlikView Expressor provides three utility functions that allow you to manage encryption and decryption.  Additionally, the functions that encode and decode characters strings as base64 representations can be used to make the handling of private keys containing non-printable data easier.

    The functions used to manage encryption and decryption are found in the utility grouping.

    • utility.custom_encrypt: use this function to encrypt a value using a private key you provide
    • utility.custom_decrypt: use this function to decrypt a value using the same private key used to encrypt
    • utility.encrypt_custom_key: use this function to encrypt your private key using the QlikView Expressor private key
      • Once encrypted with this function, a value may only be decrypted by the QlikView Expressor  Engine when running a dataflow.

    The utility.custom_encrypt and utility.custom_decrypt functions, which implement the advanced encryption standard, support six block cipher modes of action:

    1. Cipher Block Chaining: designated utility.MODE_CBD (the default mode)
    2. Electonic Codebook: designated utility.MODE_ECB
    3. Cipher Feedback with a 1 bit shift: designated utility.MODE_CFB1
    4. Cipher Feedback with an 8 bit shift: designated utility.MODE_CFB8
    5. Cipher Feedback with no shift: designated utility.MODE_CFB
    6. Output Feedback: designated utility.MODE_OFB

    Moreover, you may select from three key sizes.

    1. utility.KEYSIZE_256: the default key size
    2. utility.KEYSIZE_128
    3. utility.KEYSIZE_192

    When you call either the utility.custom_encrypt and utility.custom_decrypt functions, you provide as an argument a Datascript table containing entries that specify which mode and key size you want to use.  In addition, this table includes an element that holds your private key.  To guarantee the security of your private key, you may encrypt it using the utility.encrypt_custom_key function before adding it to this table.

    This Datascript table includes the following elements (you must refer to the table elements using these string key values).

    • key: your private key
    • iv: the initialization vector used by all block cipher modes except utility.MODE_ECB
    • algorithm: the only accepted value is utility.ALGORITHM_AES; including this element is optional
    • mode: specify one of the six block cipher modes
    • keysize: specify one of the three key sizes
    • encrypted_key: a flag (true or false) indicating whether the key has be encrypted using utility.encrypt_custom_key function; including this element is optional, the default is false

    One decision you need to make before using these functions is where you want to store your private key.  Obviously the actual value of this key is a secret so you want to store it in a secure manner.  One option is to store this key in an external database table that can only be accessed using special credentials.  However, QlikView Expressor provides an alternative approach that is much easier to use from within an Expressor dataflow.

    The QlikView Expressor Engine includes an embedded relational database management system that allows you to store any type of data as a name:value pair.  That is, you may store your private key within this database and easily retrieve it by referring to this entry through its unique name. In the following example this approach is used to store the private key encrypted with the utility.encrypt_custom_key function.

    Since many private keys contain unprintable characters, it is frequently convenient to convert the encrypted key to a base64 representation so it appears as a printable and readable character string.  The following example uses this technique.

    Storing the Encrypted Private Key

    The first task is to store the encrypted private key in the relational database embedded within the QlikView Expressor Engine.  This is performed by issuing Datascript commands from within an Expressor command window.  In this example, assume that both the unencrypted and encrypted versions of the private key are maintained as base64 encoded character strings.

    1. Select the Start > All Programs > expressor > expressor3 > expressor command prompt menu item to open an Expressor command window.
    2. Issue the command datascript to start an interactive coding session.
    3. Then issue the following series of commands, which first decodes the base64 key, encrypts it using the QlikView Expressor private key, encodes the encrypted key as a base64 character string, and stores the encoded encrypted key in the database embedded within the QlikView Expressor Engine.

    key = byte.decode(base64_encoded_private_key)
    key_encrypted = utility.encrypt_custom_key(key)
    key_encrypted_base64 = byte.encode(key_encrypted)
    utility.store_string("KEY",key_encrypted_base64)

    This series of commands needs to be executed on each server hosting the Qlikview Expressor Engine.

    Encrypting and Decrypting Data

    You will generally encrypt or decrypt data from within code in a Transform operator although you might also perform these actions in the Read Custom or Write Custom operators.  Before you can invoke either the utility.custom_encrypt or utility.custom_decrypt functions, you need to initialize a Datascript table with the required function arguments.  Include the following code (this example uses utility.MODE_ECB as the block cipher mode and a 128 bit key) within your Datascript rule.

    config = {}
    key_encrypted_base64 = utility.retrieve_string("KEY")
    key_encrypted = byte.decode(key_encrypted_base64)
    config.key = key_encrypted
    config.mode = utility.MODE_ECB
    config.keysize = utility.KEYSIZE_128
    config.encrypted_key = true

    Then to encrypt a value, use the utility.custom_encrypt function.

    encrypted_value = utility.custom_encrypt(value_to_encrypt,config)

    And to decrypt a value, use the utility.custom_decrypt function.

    decrypted_value = utility.custom_decrypt(value_to_decrypt,config)