topic Re: How to encrypt fields at load time? in QlikView

How to encrypt fields at load time?

Wed, 16 Jan 2013 05:44:14 GMT

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.

Re: How to encrypt fields at load time?

swuehl — Wed, 16 Jan 2013 09:45:09 GMT

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

Re: How to encrypt fields at load time?

Thu, 17 Jan 2013 04:57:44 GMT

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.

Re: How to encrypt fields at load time?

flipside — Thu, 17 Jan 2013 11:24:36 GMT

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

Re: How to encrypt fields at load time?

Mon, 28 Jan 2013 12:02:56 GMT

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

Re: How to encrypt fields at load time?

Wed, 30 Jan 2013 05:34:17 GMT

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.

Re: How to encrypt fields at load time?

Anonymous — Wed, 25 Feb 2015 01:51:33 GMT

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.

Re: How to encrypt fields at load time?

Wed, 11 Mar 2015 19:38:23 GMT

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

Re: How to encrypt fields at load time?

Anonymous — Thu, 12 Mar 2015 19:42:38 GMT

Panchal,

Do You know how to generate QVD outside qlik view?

Re: How to encrypt fields at load time?

Fri, 13 Mar 2015 02:34:57 GMT

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