• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Unable to use AES256 DB Encryption with Power builder 2017 R3 ?

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (8)

Resolved Unable to use AES256 DB Encryption with Power builder 2017 R3 ?

  1. Issue
0
Votes
Undo
  1. Sarath Pappireddy
    Sarath Pappireddy
  2. PowerBuilder
  3. Monday, 13 January 2020 19:38 PM UTC

Hi Appeon Team,

We are trying to Implement the DB Encryption/Decryption in one of the our application using PB 2017 R3 and SQL ANYWHERE 17.

To Implement Encryption, we have initialized 3 Vector key values(CV_VCTR,CV_KEKY and CV_CEKY), which will be available for all DB users based on the logged in DB user.

Below is the logic we are using to encrypt the Column Values:

SELECT CAST(encrypt(:column_value,CV_CEKY,'AES256(format=raw;padding=zeroes)',CV_VCTR) AS Varchar(200)) FROM DUMMY;

The above statement is encrypting correctly for all values when we execute in DATABASE.

But when we try to execute same SQL statement from PB 2017 environment, its not returning proper encrypted values for few column values(its working Majority values but not for all ).

Can any one from Appeon Team confirm whether AES256 encryption is supported by PB 2017 or not?

DDE - PB to PB
Git issues: Unable to refresh
Responses (8)
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Tuesday, 14 January 2020 17:25 PM UTC
  2. PowerBuilder
  3. # 1

It might be ANSI vs Unicode. Can it return to PB using SELECTBLOB and then converted to a string like this:

 

ls_encrypted = String(lblb_data, EncodingAnsi!)

 

Comment
There are no comments made yet.
Sarath Pappireddy
Sarath Pappireddy Accepted Answer Pending Moderation
  1. Tuesday, 14 January 2020 18:05 PM UTC
  2. PowerBuilder
  3. # 2

Hi Ronald,

Thanks for your reply.

Even i tried to select the data into Blob and then tried to convert to string also getting same bad encrypted value.

Below is the code i have used:

SELECT encrypt(:column_value ,CV_CEKY,'AES256(format=raw;padding=zeroes)',CV_VCTR) INTO :lb_blobdata FROM DUMMY;

ls_encryptedvalue = String(lb_blobdata, EncodingAnsi!)

After Conversion also same bad encrypted value is returned.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Tuesday, 14 January 2020 19:13 PM UTC
Hi Sarath;

You would need to use a SelectBlob DML command in your PB App. That also means that your Select INTO needs to first place the result of the Select into another table that has a Blob column definition. In SA, that would be a Long Binary.

Regards ... Chris
  1. Helpful
There are no comments made yet.
Arthur Hefti
Arthur Hefti Accepted Answer Pending Moderation
  1. Wednesday, 15 January 2020 04:39 AM UTC
  2. PowerBuilder
  3. # 3

Hi

as said before the result is long binary and you can't convert the result to a string normally. Quoting from the help "The value returned by this function is not human-readable". So you either key and store is as binary (blob in PB) or use Base64_Encode if you want to have something that can be converted to a string.

Regards
Arthur

Comment
There are no comments made yet.
Sarath Pappireddy
Sarath Pappireddy Accepted Answer Pending Moderation
  1. Wednesday, 15 January 2020 21:26 PM UTC
  2. PowerBuilder
  3. # 4

As Mentioned I have Tried to select the encrypted value using SELECTBLOB and after selecting i am converting to string..then also i am getting same output. No change in the output.

Blob 

SELECTBLOB encrypt(:column_value,CV_CEKY,'AES256(format=raw;padding=zeroes)',CV_VCTR) INTO :lb_dblob FROM DUMMY USING SQLCA;

ls_temp = String(lb_dblob , EncodingAnsi!)

 

Even i tied to convert to String using EncodingAnsi or EncodingUTF8, EncodingUTF16..all these conversions i tried but out put is not in proper.

Comment
Load more comments
Sarath Pappireddy
  1. Sarath Pappireddy
  2. Thursday, 16 January 2020 15:53 PM UTC
Thanks for your response and I am validating the encrypted value using decrypting using below code

select decrypt(:as_column_value,CV_CEKY,'AES256(format=raw;padding=zeroes)',CV_VCTR) FROM DUMMY;



For most of the scenarios its working but few scenarios this is not working either using blob or string conversion also.



I tried using the Code Object also, but its failing during decryption.

We have .NET and PowerBuilder applications, so we are trying to implement common encryption method using DB encryption which supports both platforms.

I think we have issue with PB IDE like way of interpreting the character set for few strings. because many strings this is working but few strings this is not working.





. conversion and if I start using Base64 encoding decoding approach itself is changing to different route



  1. Helpful
Ricardo Jasso
  1. Ricardo Jasso
  2. Thursday, 16 January 2020 16:54 PM UTC
Is :as_column_value a column from a table or a parameter sent from PowerBuilder? On your OP you put :column_value but now it's :as_column_value. It'll help us a lot to know exactly what you are typing in each case. That is, directly in the database (I assume you are using Interactive SQL or SQL Central) and in PowerBuilder's Interactive SQL pane.
  1. Helpful
Sarath Pappireddy
  1. Sarath Pappireddy
  2. Friday, 17 January 2020 14:54 PM UTC
as_column_value is the variable name which I declared and passing same in select statement. while I am entering the value and tabout from textbox then it will assign the value to as_column_value .
  1. Helpful
There are no comments made yet.
Arthur Hefti
Arthur Hefti Accepted Answer Pending Moderation
  1. Tuesday, 14 January 2020 04:38 AM UTC
  2. PowerBuilder
  3. # 5

Hi

Encrypt of SQL Anywhere return a long binary (see http://dcx.sap.com/index.html#sa160/en/dbreference/encrypt-function.html).

You need to either store it as binary or use Base64_Encode to get a string. According to the docs the binary size is at most 31 characters longer than the input string but as soon as you encode it Base64 it's much longer.

Regards
Arthur 

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 13 January 2020 19:58 PM UTC
  2. PowerBuilder
  3. # 6

Hi Sarath;

  I am wondering if you are hitting the VarChar(200) length limitation in PB because its Unicode (UTF-16LE) and thus will convert the SA DB's ANSI (single byte) into PB's Double-Byte when casting to a VarChar?

Regards ... Chris

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 13 January 2020 20:14 PM UTC
Just the opposite as I was thinking of "truncation" .... maybe try VarChar (400)

  1. Helpful
Sarath Pappireddy
  1. Sarath Pappireddy
  2. Monday, 13 January 2020 21:20 PM UTC
I tried with VARCHAR(400) also . I am getting same issue.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 13 January 2020 22:03 PM UTC
Sorry that this larger length was not the issue. I am beginning to think though that this might be a byte transposition error. I wonder what would happen if you did a Select ... INTO a Long Binary (blob) column in another table. Then performed a SelectBob command on that encrypted column's datum?
  1. Helpful
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Monday, 13 January 2020 20:28 PM UTC
  2. PowerBuilder
  3. # 7

You could try creating a user defined function and call that in the select.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Monday, 13 January 2020 21:44 PM UTC
Good suggestion Roland or an SP!

  1. Helpful
There are no comments made yet.
Sarath Pappireddy
Sarath Pappireddy Accepted Answer Pending Moderation
  1. Monday, 13 January 2020 21:52 PM UTC
  2. PowerBuilder
  3. # 8

Even We tried using both Stored procedure and Function also. We are getting same issue. 

Not sure what is the exact issue  with PB IDE. If we are returning the encrypted value back to PB IDE, its giving wrong value.

If we encrypt in stored procedure and saving in the End table using same stored procedure then we are not getting issue, because we are returning the encrypted value to IDE.

 

Not Sure whether AES256 is supported ..

 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.