Junk values during field level encryption using AES

[#1]

Guys,

I've been working on this for quite some time. One of the QlikTech employees was kind enough to help me with the script but I'm having issues here.

I needed field level encryption using any encryption function. We have card numbers stored into QVDs and so wanted to encrypt them at load time and decrypt using the same key whenever needed.

Currently the app attached uses the AES encryption function to encrypt card numbers but during decryption, a few values don't get decrypted to their original clear texts. Almost 95% of values decrypt properly except a few. I tried changing the key, the length of the key etc, but it didn't help. With a lot of records, there are always a few that remain junk at decryption time. Since this is going to be used to store 70 million plus rows, I wanted help in fixing this.

I don't mind using other encryption techniques either, whatever works, but prefer 128 bit encryption at the minimum.

Thanks in advance.

Sam.

[flipside]

Since Wei first identified this issue, I've not investigated any further other than identifying the scale of the problem - it appears to happen 1 in every 30k to 50k values encrypted. Running your script, there still seems to be an issue, where the wrong value is assigned back when decrypting ...

... it could be due to something as simple as an end of line character somewhere causing the code to stop and return a previous variable value.

Probably won't get time for a while to look into this further. I just ran 10000 rows through and kept refreshing until my comparison table returned entries.

flipside

[Jeff_Koch]

Hi filpside.   What version of QlikView are you using?

I ran 10,000 reload iterations of the qvw and card_number.xls from Wei and did not get a single error.

Jeff

[flipside]

Interesting - I'm getting errors on one machine (11.2 32-bit quad-core) and not another (11.0 64-bit quad-core)!!

[Jeff_Koch]

Hey flipside,

I think I know what is going on. Check your .Net framework and make sure you are updated to 4.5 or maybe 4.5.1. They have been some .Net bugs affecting QlikView

Apply the latest updates, try again, and let me know

Jeff

Sent from my iPhone

[flipside]

Cheers, worth a shot.

Personally think it's a memory issue.  Do you have a link to any more info on the .net bugs - couldn't find anything via search.

[#7]

Take a look at this post, it details the steps to get AES encryption working.

Encryption in QlikView — Medium

[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.