How to encrypt fields at load time?

[#1]

Hi..

I'm currently involved in a project for a bank and have to adhere to their security policies with respect to data storage. We're creating a QVD warehouse and some of the QVDs will contain sensitive data, so I was wondering how to proceed to secure the information I pull from the source system. This is due to the fact that once a QVD is created and say if some sort of theft occurs, this QVD can be opened by anyone using a licensed version of QlikView. Thefore, there is inherently no security feature to protect the QVD file.

What we need is this: We are storing card numbers along with some other sensitive data into the QVDs we create. Our hard drives are already encrypted, therefore we do not require QVD file level encryption, but field level encryption/scrambling. So at the time of QVD generation, we would like to encrypt the card numbers (during load) before the storage to QVD. At the time of reloading the qvd, we would decrypt it and make it available to the app.

I’d be glad if anyone could give it a thought and may be suggest alternatives if any or advise a possible solution to this. By the way, one of my thoughts were to use some sort of logic to scramble the card number field during qvd generation and devise a key to unscramble during app reload. Is there some sort of logic I can use?

Thanks for your help..

Sam.

[swuehl · Accepted solution]

If you are using QV Expressor, you can have a look at

http://community.qlik.com/docs/DOC-3359

It should also be possible to write a VBScript Macro that does the encryption / decryption. Search the Web for your favorite encrpytion method, e.g.

http://www.example-code.com/vbscript/AesIv.asp

[#3]

Thanks for your suggestion, swuehl and for the links.... Will try this.

We're not using QV Expressor currently, but the VBScript function could serve well.

Thanks again..

Sam.

[flipside]

Hi Sam,

If your database already has an encryption mechanism, then would it not be possible to store your encrypted fields in a password protected .qvw to binary load into your final application? Your QVDs then only hold the encrypted GUIDs.

flipside

[#5]

Thanks for that suggestion. It's a good alternative to encrypting during load. I'll consider it.

[#6]

Guys,

I was able to implement field level encryption using AES with some help.... I've uploaded the same to this discussion here:

http://community.qlik.com/message/304652#304652

Please take a look.. Everything goes well with encryption but after decryption, a few values remain as junk and don't get decrypted correctly to their clear texts...

Any help would be appreciated..

Thanks

Sam.

[Anonymous]

The best solution is :

1) On QVW - Edit Module create decrypt function with CryptoJS.

function decrypt(value) {

if(null==value||''==value) return '';

-----

return decryptedData.toString(CryptoJS.enc.Utf8);

}

Protected  Edit Module with password on Document Properties.

2) Create a QVD dynamic with java / C# / C++

In java:

private static byte[] encryptBytes(final String s) throws Exception {

final Key key = new SecretKeySpec("KEYKEYKEYKEYKEYKEYKEYKEYKEYKEYKEYKEY", "AES");

final Cipher c = Cipher.getInstance("AES");

c.init(Cipher.ENCRYPT_MODE, key);

return c.doFinal(s.getBytes());

}

3) On Qlik - Edit Script

SELECT decrypt(column)....

from .... qvd

Protected  Edit Script with password on Document Properties.

Notes:

* The null and empty column aren't encrypted.

* The qvd generation use Real Column hashcode()  (java) to test repeated values on columns

because Crypt generate different values for same input.

* The speed decrease 10% or more depending number of columns encrypted.

This process is tested for a QVD with more than 100 millions records.

[#8]

Hello Antonio,

Do you have any sample qvw and qvd files where you have implemented the above solution.

I need to do a PoC on decrypting data that was encrypted using some third party encryption tool.

The encryption here is column level, and I want to decrypt those columns before displaying it on the Access Point.

Appreciate all you help.

Thanks

Piyush

[Anonymous]

Panchal,

´

Do You know how to generate QVD outside qlik view?

[#10]

No, haven't tried creating qvd outside of qlikview.