Hello,
I have the following situation:
Table 1:
Table1.ID
Table1.LastName
Table1.FirstName
Table1.MiddleName
In Table 1 the LastName, FirstName and MiddleName are AES encrypted in the database.
Table 2:
Table2.ID
Table2.role
Table2.person
I want to add a row in Table 2, via a lookupwizzard.
The linkfield is ID, but the displayfield should be: concat(LastName,', ',FirstName,' ',MiddleName)
I just can't figure out where and jow to perform the decrypt.
Or how this could be managed easily.
Any tips or an example would be much appreciated.
I can't really advise with the lookup wizard part....
Thinking about this a bit further I may I have a similar situation in my application; I have the Person table (name address etc) and Voucher table, there is a 1 to Many relationship between them, and the Person Id is a link field on the Voucher table, which has its own unique VoucherID field. I use the master-detail functionality, so that to add a voucher I have to locate the Person first. Then there are no issues with manual decryption. This might the solution to you problem your Table 1 would be the Master and Table 2 the Detail, I'm guessing that the Table 1 record has to exist before a Table 2 record can be created, and that there is a 1-2-* relationship between T1 and T2.
I have single record Add and Edit pages for the Voucher table and I like to show the name and address at the top of the Add or Edit pop-up page, that does require manual decrypt.
I 'hide' the encryption key in a session variable. This code below is actually in a Custom PHP function library, I found that by using a custom function library it simplified event and snippet code and more code was resusable.
// Gets Customer name and Address for Add and Edit pages
function GetCustNameAddress($dal, $strTableName) {
global $dal, $strTableName;
if ($_SESSION[$strTableName."_masterkey1"]){
$sql_cmd = "SELECT FB_id,
cast(DES_DECRYPT(unhex(first_name), '".$_SESSION['Des_key']."') as char) as fname,
cast(DES_DECRYPT(unhex(last_name), '".$_SESSION['Des_key']."') as char) as lname,
cast(DES_DECRYPT(unhex(street1), '".$_SESSION['Des_key']."') as char) as address1
from fb_person where FB_id =" .$_SESSION[$strTableName."_masterkey1"]."";
$rs = CustomQuery($sql_cmd);
if ($data = db_fetch_array($rs)) {
echo " ".$data["fname"]." " .$data["lname"] ." , " .$data["address1"]."
";
}
}
}
//
Hope this helps........
