2. PHPRunner General questions
  3. Decrypt an encrypted field in custom query
This topic is locked

Decrypt an encrypted field in custom query

 		3/29/2017 2:10:23 PM
PHPRunner General questions
romaldus author

In Phprunner 9.7 enterprise, i use AES128 (database based) encryption.

How to Decrypt value in MySql custom query ?
Using normal custom query, returns encrypted value:



$sql = "SELECT customer_name AS custname FROM customers_table WHERE id = 'CUST123'";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);

$_SESSION["custname"] = $data["custname"];
echo $_SESSION["custname"] ;
T
 		thamestrader 3/30/2017

Encryption is great until you have to do something in an event......
I've used the following code in v9.6 Enterprise with MySQL and PHPRunner encryption - if it helps.....
$tbl = $dal->Table("table");
$sql_cmd = "SELECT fieldname1,

cast(DES_DECRYPT(unhex(first_name), '".$_SESSION['Des_key']."') as char) as O_fname,
For each encrypted field you will need a CAST clause like the example shown above.
first_name is an encrypted field in the table,
$_SESSION['Des_Key'] is a session variable that I have populated elsewhere with the encryption key (as entered on the Security page), but you can simply hard-code the encryption key on every CAST clause if you want. Bit on the tedious side though and error prone.
O_fname will contain the decrypted data.
If you are going to amend the data and then update the table manually then ypou will have to encrypt it on the UPDATE command. See example below.
SET first_name = (hex(DES_ENCRYPT('".$data['O_fname']."' , '".$_SESSION['Des_key']."'))),
I developed this code for a conversion event code when having to run through the whole database to validate and cleanse personal data.
romaldus author 3/30/2017



Encryption is great until you have to do something in an event......
I've used the following code in v9.6 Enterprise with MySQL and PHPRunner encryption - if it helps.....
$tbl = $dal->Table("table");
$sql_cmd = "SELECT fieldname1,

cast(DES_DECRYPT(unhex(first_name), '".$_SESSION['Des_key']."') as char) as O_fname,
For each encrypted field you will need a CAST clause like the example shown above.
first_name is an encrypted field in the table,
$_SESSION['Des_Key'] is a session variable that I have populated elsewhere with the encryption key (as entered on the Security page), but you can simply hard-code the encryption key on every CAST clause if you want. Bit on the tedious side though and error prone.
O_fname will contain the decrypted data.
If you are going to amend the data and then update the table manually then ypou will have to encrypt it on the UPDATE command. See example below.
SET first_name = (hex(DES_ENCRYPT('".$data['O_fname']."' , '".$_SESSION['Des_key']."'))),
I developed this code for a conversion event code when having to run through the whole database to validate and cleanse personal data.


Many thanks.
romaldus author 3/30/2017

I have successfully decrypt value using the following code



//define encryption key

$key='09a308862fbe462095dd6eba33ab9dd21b8fd35b0d884b48819a34ce8636983b';
$sql = "SELECT cast(AES_DECRYPT(unhex(customer_name), '".$key."') as char) AS custname FROM customers_table WHERE id = 'CUST123'";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);
$_SESSION["custname"] = $data["custname"];


.....but i have one more question about phprunner security.

Is it save to put encryption key directly in a session variable? Is there any other way that is more secure?
romaldus author 3/30/2017

what i mean is, instead of manually declare a new variable with encryption key value (which is not secure), how to access built in encryption key session var?
Admin 3/31/2017

It is secure, just not very convenient. Your encryption key is stored in one of PHP files anyway. We will add a function that will provide more convenient access to encryption key.
romaldus author 3/31/2017



It is secure, just not very convenient. Your encryption key is stored in one of PHP files anyway. We will add a function that will provide more convenient access to encryption key.


Thanks. Solved