Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Junk values during field level encryption using AES

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.

qvc1.JPG

Thanks in advance.

Sam.

16 Replies
MVP
MVP

Re: Junk values during field level encryption using AES

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
Valued Contributor II

Re: Junk values during field level encryption using AES

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
Valued Contributor II

Re: Junk values during field level encryption using AES

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

Not applicable

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

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.

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

flipside
Valued Contributor II

Re: Junk values during field level encryption using AES

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

Not applicable

Re: Junk values during field level encryption using AES

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.

Not applicable

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

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.

aes1.jpg

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

aes2.jpg

flipside
Valued Contributor II

Re: Junk values during field level encryption using AES

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.

Employee
Employee

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

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

    Next


Decrypt

        ts = ""

        For i = 1 To Len(s) / 2

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

            ich = CLng("&H" & ch)

            ts = ts & (Chr(ich))

        Next

        s = ts

AES.png

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


Community Browser