Junk values during field level encryption using AE... - Qlik Community

Report Inappropriate Content · ‎2013-01-28

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.

swuehl · ‎2013-01-30

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').

flipside · ‎2013-01-30

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

flipside · ‎2013-01-30

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

Report Inappropriate Content · ‎2014-01-14

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.

flipside · ‎2014-01-14

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

Report Inappropriate Content · ‎2014-01-14

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.

Report Inappropriate Content · ‎2014-01-14

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.

flipside · ‎2014-01-15

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.

Jeff_Koch · ‎2014-01-22

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

ts = ""

For i = 1 To Len(s) / 2

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

ich = CLng("&H" & ch)

ts = ts & (Chr(ich))

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

Junk values during field level encryption using AES