16 Replies Latest reply: Feb 20, 2015 1:33 PM by Antonio Caria RSS

    Junk values during field level encryption using AES



      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.



        • Re: Junk values during field level encryption using AES
          Stefan Wühl

          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
            Dave Riley

            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.



              • Re: Junk values during field level encryption using AES
                Dave Riley

                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)


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

                                end if



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


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

                            end if




                  • Re: Re: Junk values during field level encryption using AES
                    Wei Wang



                    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.

                    1-14-2014 12-53-36 AM.jpg

                      • Re: Junk values during field level encryption using AES
                        Dave Riley



                        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.



                          • Re: Junk values during field level encryption using AES
                            Wei Wang

                            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
                              Wei Wang

                              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
                                  Dave Riley

                                  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 - Old

                                      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.


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


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


                                              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.



                                                let pwd = '09876554321dsefrtgyh';


                                                // Encrypt field to be secured in the qvd file




                                                     clearText as origClearText,

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

                                                from drg.qvd (qvd);


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





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

                                                FROM EncyptedText.qvd (qvd);


                                                let pwd = '';



                                      Jeff Koch

                                      Principle OEM Solution Architect

                          • Re: Junk values during field level encryption using AES
                            Justin Skaggs

                            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
                              Antonio Caria

                              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.


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