AES Encrypt/Decrypt Not Working in Grid, OK in PHPMyadmin

Code Examples Grid
, ,
#1

Dear All,

I’m testing AES encryption/decryption in my application. I have a simple 3 field file (id, first_name, address) and encrypting first_name and address. The code below is the SQL statements that Created the file in PHPMyAdmin and also Selects the record(s). When I create a Grid in ScriptCase with the same Select statement, an error is created saying send to support, and no grid is generated.

Thoughts much appreciated.

Lyle

Here are the statements:

CREATE TABLE user (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARBINARY(100) NULL,
address VARBINARY(200) NOT NULL
)

INSERT into user (first_name, address) VALUES (AES_ENCRYPT(‘Obama’, ‘usa2010’),AES_ENCRYPT(‘Obama’, ‘usa2010’));

SELECT ID, AES_DECRYPT(first_name, ‘usa2010’), AES_DECRYPT(address, ‘usa2010’) from user;

#2

Hi All,

Here is the error message I got on grid generation:

Invalid argument supplied for foreach() | Script: C:\Program Files (x86)\NetMake\v71\wwwroot\scriptcase\devel\compat
m_aplicacao_cria.php linha: 616

Lyle

#3

Hi lyleholc,

i noticed that your post is a few months back. i’m having plans to do the same as what you are trying to do. Did you finally resolve the issue or AES cant be done in scriptcase?

#4

Any luck on this?

#5

FYI Your original posting almost worked - but you had several problems at the same time (I am posting this so it will help others in future):

  1. You need to keep consistent with using field/column names - your first SQL created column id (lowercase) and not ID (uppercase) . Your decrypting SQL used ID (uppercase) - it ‘worked’ sort of in PHPMyadmin - but not really. You may have noticed a non-unique warning when you ran it there. Making it id (lowercase) is correct.
  2. SC was probably creating a field name (based on your SQL) with a weird name like: AES_DECRYPT(first_name, ‘usa2010’). This causes problems and errors. You need to add something like AES_DECRYPT(first_name, ‘usa2010’) AS name which will create a proper column name in your result table . PHPMyadmin does the same. But in Scriptcase it is like injecting bad stuff and errors out. Works fine with ‘As XXXXX’. Note that you may have to edit fields and move them around etc.
  3. Even with all that you still will get a grid in SC but it will have a bunch of weird symbols in the decrypted columns. It is actually working correctly. AES_DECRYPT produces binary! See this refrence: http://stackoverflow.com/questions/1…-in-phpmyadmin . So the way around it is to do something with CAST:

SELECT id, cast(AES_DECRYPT(first_name, ‘usa2010’) as char) as name, cast(AES_DECRYPT(address, ‘usa2010’) as char) as address FROM testaes WHERE 1

NOTE that I used a different table name (testaes) instead of user - and the WHERE 1 is just a place holder - it returns everything.

You put this SQl in your SC grid - SQL - SQL Settings - SQL Select Statement. Then just save and run tests with it.

I tested this in SC 8.1 and it produces a clean , clear, decrypted grid. Your first two SQL created the table correctly, and also inserted one person in. I suggest anyone doing this create several people in the table so the grid has something useful for testing.

Of course, the next step is to try to get the form to edit correctly (e.g showing a decrypted value for first_name and then saving it as encrypted. But that will be another post LOL.

peace everyone,
Jamie

PS Just another hint - you can get all crazy fouled up in SC with field and search fields not getting set when you are changing the SQL above - usually if you get an error about fields not being defined or same with search - just edit and move them around or flag them as needed. You have to have at least one good field so the grid will work. Good luck!