In this example we'll show you how to encrypt and decrypt the contents of Model field in Cars table in few easy steps.
[size="6"]Scenario 1. Pure PHP solution.[/size]
Works with all databases. Requires PHPRunner 5.3. Data encrypted and decrypted in PHP code.
- Create a file named encrypt.php in the output directory and paste the following code there
<?php
// put your own encryption key here
$encryption_key = "dsfF*sdfdlfA34(s";
function decrypt($value)
{
global $encryption_key;
if(function_exists("mcrypt_ecb"))
{
$temp = mcrypt_ecb(MCRYPT_3DES, $encryption_key, $value, MCRYPT_DECRYPT);
return rtrim($temp, "\0");
}
else return $value;
}
function encrypt($value)
{
global $encryption_key;
if(function_exists("mcrypt_ecb"))
{
return mcrypt_ecb(MCRYPT_3DES, $encryption_key, $value, MCRYPT_ENCRYPT);
}
else return $value;
}
?>
Now proceed to your project and setup events
2. Global event: After Application Initialized
include("encrypt.php");
3. Add page: Before record added
$values["Model"] = encrypt($values["Model"]);
4. Edit page: Before record updated
$values["Model"] = encrypt($values["Model"]);
5. Edit page: Process record values
$values["Model"]=decrypt($values["Model"]);
6. Use 'List page: Before record processed' event to decrypt data
$data["Model"]=decrypt($data["Model"]);
7. Build your application and enjoy.
[size="4"]Other considerations[/size]
- Encoding existing data
This approach works the best for new data to be added to your database. If you have unencrypted data in the database you need to encode it manually. This article provides sample code for this job: http://xlinesoft.com/articles/encrypt_passwords.htm.
- mcrypt extension needs to be enabled in php.ini
Additional info on PHP encryption:
http://php.net/manual/en/ref.mcrypt.php
- If your application uses a login page and password is encrypted as well you need to implement BeforeLogin event to decrypt the password.
global $conn;
$result = mysql_query("SELECT username, password from users where username='$username' ",$conn);
$data = mysql_fetch_array($result);
if (decrypt($data[1])==$password)
{
$_SESSION["UserID"] = $username;
$_SESSION["AccessLevel"] = ACCESS_LEVEL_USER;
header("Location: menu.php");
exit();
}
return true;
[size="6"]Scenario 2. Using MySQL functions.[/size]
Works with MySQL 5.x and all versions of PHPRunner.
- Create triggers in the database to encrypt data before insert and update operations.
delimiter |
CREATE TRIGGER insert_encrypt BEFORE INSERT ON cars
FOR EACH ROW BEGIN
SET NEW.Model = AES_ENCRYPT(NEW.Model,"my passphrase");
END;
|
delimiter |
CREATE TRIGGER update_encrypt BEFORE UPDATE ON cars
FOR EACH ROW BEGIN
SET NEW.Model = AES_ENCRYPT(NEW.Model,"my passphrase");
END;
|
2. Modify SQL query to decrypt data on the fly. Use the same passphrase.
SELECT
...
AES_DECRYPT(Model,"my passphrase") as Model2,
...
FROM carscars
Note the the alias of decrypted field.
3. BeforeEdit/BeforeAdd events.
Since calculated field cannot be updated directly we need a little magic to update our source field.
Add the following code to BeforeAdd/BeforeEdit event:
$values["Model"]=$values["Model2"];
unset($values["Model2"]);
This will replace Model2 with Model in SQL query and will allow to update our database. Data will be encrypted by trigger we created on step 1.
4. If your application uses a login page and password is encrypted as well you need to implement BeforeLogin event to decrypt the password.
global $conn;
$result = mysql_query("SELECT username, aes_decrypt(password,'my passphrase') from users where username='$username' ",$conn);
$decrypted = mysql_fetch_array($result);
if ($decrypted[1]==$password)
{
$_SESSION["UserID"] = $username;
$_SESSION["AccessLevel"] = ACCESS_LEVEL_USER;
header("Location: menu.php");
exit();
}
return true;
[size="4"]Other considerations[/size]
- Encoding existing data
This is a piece of cake since database handles encryption for you. To encrypt existing data create a trigger first (bullet #1) and then run the following query:
Update TableName set FieldName=FieldName
Trigger will update FieldName with encrypted value. Make sure you only run this query once. If you attempt to encrypt data twice you won't be able to decrypt it.
This is it.
Sergey Kornilov