topic Re: Re: Junk values during field level encryption using AES in QlikView

Junk values during field level encryption using AES

Mon, 28 Jan 2013 12:44:08 GMT

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.

Re: Junk values during field level encryption using AES

swuehl — Wed, 30 Jan 2013 09:34:22 GMT

You'll notice that the problematic card numbers show an encrypted length smaller than 15, while the correct ones will show 15 or 16.

I assume that QV interpretes one of the characters used in the encrypted part as stop sign, i.e. it will disregard all other characters. This will lead to an incorrect result when decrypting, of course.

I don't have a complete solution right now, but maybe you can add an additional coding of the encryption character set to the character set QlikView accepts (like a 'Quoted-printable encoding').

Re: Junk values during field level encryption using AES

flipside — Wed, 30 Jan 2013 10:47:12 GMT

swuehl is correct, the error seems to occur when building the sCipher variable in this line ...

sCipher = sCipher & Chr(block((i Mod 4) * 4 + (i \ 4)))

When this equates to sCipher+Chr(0), it will stop building the variable. You can't currently increment the value submitted to Chr() by 1 because in some cases it will then take it past the upper limit and the encryption will fail completely.

flipside

Re: Junk values during field level encryption using AES

flipside — Wed, 30 Jan 2013 11:21:51 GMT

One solution you could try is to create a 2-char cipher build routine, using a set char value placeholder to identify Chr(0) and a random char for irrelevant values ...

For i = 0 To 15

if block((i Mod 4) * 4 + (i \ 4)) = 0 then

sCipher = sCipher & Chr(1) & Chr(225)

else

sCipher = sCipher & Chr(block((i Mod 4) * 4 + (i \ 4))) & Chr(Int(Rnd*200)+10)

end if

Next

... the random char value needs to be guaranteed to be more than 0 & not to possibly equal 225 in this case.

When decrypting, split your submitted encrypted value into 2 ...

s=mid(sf,1,1) & mid(sf,3,1) & ...etc... mid(sf,29,1) & mid(sf,31,1)

f=mid(sf,2,1) & mid(sf,4,1) & ... etc ... mid(sf,30,1) & mid(sf,32,1)

... and just inspect the asc values of f when getting the asc value ...

For i = 0 To 15

if Asc(Mid(fTemp, (i Mod 4) * 4 + (i \ 4) + 1, 1))=225 then

block(i) = 0

else

block(i) = Asc(Mid(sTemp, (i Mod 4) * 4 + (i \ 4) + 1, 1))

end if

Next

flipside

Re: Re: Junk values during field level encryption using AES

Tue, 14 Jan 2014 08:58:05 GMT

Hi,

I am running into exactly the same encryption/decryption problem, and am super glad that I found this post.

I attempted to implement the fix according to flipside's latest post, and thought I got it working (at least on Sam's original qvw attached in his post). However, I am finding one issue with my fix, and would appreciate inputs from any expert out there.

Attached qvw contains my fix to the encryption/decryption functions. I noticed that if the original cell contains missing data (i.e. null), after applying encryption followed by decryption on that cell, the decrypted value becomes "empty" but not null. As you will find in the attached qvw, I added a new row in the source excel file (also attached), where card number is empty. When loaded into QV, that row shows - for Card_Number_ClearText column but "" (no quotes) for Card_Number_Decrypted. Making dropdowns for those two fields also shows the difference- Card_Number_ClearText simply does not have the null value whereas Card_Number_Decrypted has an empty line.

Re: Junk values during field level encryption using AES

flipside — Tue, 14 Jan 2014 09:43:48 GMT

Hi,

There are a number of ways to resolve this. You could change nulls to a known 'holding' value such as 9999999999999999 and submit this to the encrypting function, or you could exit the functions aes_Encrypt and aes_Decrypt at the start when you hit a null or empty string ...

if len(s) <1 then
exit function
end if

... maybe set the test to <16 when encrypting so it captures bad formats.

flipside

Re: Junk values during field level encryption using AES

Tue, 14 Jan 2014 16:38:00 GMT

Thanks flipside,

Silly me for not seeing the obvious. I guess it was too late in the night when I made the post, and at the time I was focusing on finding out what's wrong with my functions with the assumption that what goes into encryption/decryption cycle should be what comes out, even for nulls.

In my case, I am encrypting strings with different meanings and varying lengths. So I will just test for length and exit the encryption/decryption function if len < 1.

Re: Re: Junk values during field level encryption using AES

Wed, 15 Jan 2014 01:42:34 GMT

Looks like I spoke too soon. Upon further testing, I don't think the original issue was resolved. I was still able to see some junk values after decryption. Worse, it's not consistently happening at the same place.

The following screenshot shows that for a given card number, the decrypted text is still gobbled up.

Now if I run reload one more time, the same card now decrypted just fine.

Re: Junk values during field level encryption using AES

flipside — Wed, 15 Jan 2014 13:40:23 GMT

Yes, it looks like there is something wrong with this encryption algorithm. I can see this happening to me also. The reload fixes it BUT if you keep reloading, the errors return after a while then disappear again. Just doing some testing but this might be difficult to fix.

Re: Re: Junk values during field level encryption using AES

Jeff_Koch — Wed, 22 Jan 2014 17:25:51 GMT

Hi All,

I have fixed this issue. It is caused when an character in the source string encrypts to chr(0) which can not be successfully concatenated to the encrypted string.

I solved this by hexing the encrypted characters and building a hex string instead of string of char. The decrypt routine then converts the hex string to the encrypted string of char and processes it normally.

Encrypt

For i = 0 To 15

If Len(Hex(block((i Mod 4) * 4 + (i \ 4)))) = 1 Then

sCipher = sCipher & "0" & Hex(block((i Mod 4) * 4 + (i \ 4)))

Else

sCipher = sCipher & Hex(block((i Mod 4) * 4 + (i \ 4)))

End If

Decrypt

ts = ""

For i = 1 To Len(s) / 2

ch = Mid(s, (i * 2) - 1, 2)

ich = CLng("&H" & ch)

ts = ts & (Chr(ich))

s = ts

I've attached the vbs script.

1) Open the AES_Cipher_block_256.txt file

2) Copy the text

3) Paste it into the VBscript Edit module in QlikView

4) Make the appropriate Encrypt/Decrypt calls in your load script.

example:

let pwd = '09876554321dsefrtgyh';

// Encrypt field to be secured in the qvd file

encrypted:

load

id,

clearText as origClearText,

text(aesEncrypt(trim(text(clearText)), '$(pwd)')) as EncyptedText

from drg.qvd (qvd);

//store encrypted into EncyptedText.qvd (qvd);

decrypted:

NoConcatenate

LOAD

id,

text(aesDecrypt(trim(text(EncyptedText)), '$(pwd)')) as DecyptedText

FROM EncyptedText.qvd (qvd);

let pwd = '';

Regards,

Jeff Koch

Principle OEM Solution Architect

Re: Junk values during field level encryption using AES

flipside — Thu, 23 Jan 2014 09:35:18 GMT

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

Re: Junk values during field level encryption using AES

Jeff_Koch — Fri, 24 Jan 2014 00:52:30 GMT

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

Re: Junk values during field level encryption using AES

flipside — Fri, 24 Jan 2014 12:36:57 GMT

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

Re: Junk values during field level encryption using AES

Jeff_Koch — Sat, 25 Jan 2014 06:16:13 GMT

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

Re: Junk values during field level encryption using AES

flipside — Tue, 28 Jan 2014 15:40:29 GMT

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.

Re: Junk values during field level encryption using AES

Wed, 05 Mar 2014 04:09:31 GMT

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

Encryption in QlikView — Medium

Re: Junk values during field level encryption using AES

Anonymous — Fri, 20 Feb 2015 18:33:03 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.