How-To

Implement MySQL encryption in UDF data.

Disclaimer:

ABPro is specifically designed to use payment gateways where all the credit card data is stored by the gateway and no credit card information is retained in ABPro.
This How-to will show how to add MySQL encryption to your UDF data, should you decide to store sensitive information like credit card details.

Notes: 

  • With this How-to, ALL UDF data will be encrypted. If you include UDF data in confirmation emails it will appear as jiberish, as will UDF data in exports to CSV files.
  • After implementing this, UDF data in old bookings will appear as jiberish.

 

There are 3 basic steps:

  1. Change the data type of the database field where UDF data is stored
  2. Change the controller of the booking screen(s) you are using to save encrypted data
  3. Change the booking detail screen(s) to decrypt UDF data before display.
  4. Change the save code so edits to the booking detail will re-encrypt before being saved.

 

Step 1

The table where UDF data is 'jos_sv_apptpro2_udfvalues' (prefix jos_ will likely be different on your site)

You will need to change the data type of column udf_value from VARCHAR to BLOB or VARBINARY.  You can change the data type with phpMyAdmin.

 

 

Step 2

You will need to modify the controller for the booking screen you are using.
In this example I will modify the GAD booking screen controller file:
\components\com_rsappt_pro2\controllers\booking_screen_gad.php 

Around line 326 look for:

$udf_value = JRequest::getVar('user_field'.$i.'_value');
$sSql = sprintf("INSERT INTO #__sv_apptpro2_udfvalues (udf_id, request_id, udf_value) VALUES(%d, %d, %s)",
$_POST['user_field'.$i.'_udf_id'],
$last_id->last_id,
$database->getEscaped($udf_value));

 

Change to:

$udf_value = JRequest::getVar('user_field'.$i.'_value');
$key = "this is a secret key"; // change to a key phrase of your choosing
$sSql = sprintf("INSERT INTO #__sv_apptpro2_udfvalues (udf_id, request_id, udf_value) VALUES(%d, %d, %s)",
$_POST['user_field'.$i.'_udf_id'],
$last_id->last_id,
"AES_ENCRYPT('".$udf_value."','".$key."')");

 

Step 3

Modify the booking detail screen to decrypt the UDF data before display.

In this example I will modify the front-end booking detail used by staff.

File:\components\com_rsappt_pro2\views\requests_detail\tmpl\form.php

Around line 114 look for:

$sql = "SELECT ".
"#__sv_apptpro2_udfs.udf_label, #__sv_apptpro2_udfs.udf_type, ".
"#__sv_apptpro2_udfvalues.udf_value, #__sv_apptpro2_udfvalues.id as value_id, ".
"#__sv_apptpro2_udfvalues.request_id ".
"FROM ".
"#__sv_apptpro2_udfvalues INNER JOIN ".
"#__sv_apptpro2_udfs ON #__sv_apptpro2_udfvalues.udf_id = ".
"#__sv_apptpro2_udfs.id_udfs ".

 

Change to:

$key = "this is a secret key"; // change to a key phrase of your choosing
$sql = "SELECT ".
"#__sv_apptpro2_udfs.udf_label, #__sv_apptpro2_udfs.udf_type, ".
"AES_Decrypt(udf_value,'".$key."') as udf_value,#__sv_apptpro2_udfvalues.id as value_id, ". 
"#__sv_apptpro2_udfvalues.request_id ".
"FROM ".
"#__sv_apptpro2_udfvalues INNER JOIN ".
"#__sv_apptpro2_udfs ON #__sv_apptpro2_udfvalues.udf_id = ".
"#__sv_apptpro2_udfs.id_udfs ".

 

Step 4

Modify the save code to re-encrypt when admin saves changes from the booking detail.

The save is done in the Model file..
\components\com_rsappt_pro2\models\requests_detail.php

Around line 408 look for:
$udf_value = JRequest::getVar('udf_value_'.$udfr, null, 'default', 'none', JREQUEST_ALLOWHTML);
$sql = "UPDATE #__sv_apptpro2_udfvalues SET udf_value='".$this->_db->getEscaped($udf_value)."' WHERE id=".$udf_value_id;


Change to:
$key = "this is a secret key"; // change to a key phrase of your choosing
$udf_value = JRequest::getVar('udf_value_'.$udfr, null, 'default', 'none', JREQUEST_ALLOWHTML);
$sql = "UPDATE #__sv_apptpro2_udfvalues SET udf_value=AES_ENCRYPT('".$udf_value."','".$key."') WHERE id=".$udf_value_id;