How to encrypt and decrypt column using php and datatables - render
How to encrypt and decrypt column using php and datatables - render
Shivani Vyas
Posts: 113Questions: 11Answers: 0
How to Encrypt existing Maria db database column using php on server side and decrypt it on client side ?
0
I would like to encrypt email column in database server side and display decrypted emails on my webpage. I used openssl_encrypt() function to encrypt new user's email. It works fine. But I am not sure how I can use openssl_decrypt() function to display decrypted emails on webpage because we are using datatables. I try to use "render" to call decrypt function but not working. Here what I have done so far.
we have function.php file which has all function stored. I created encrypt function in this file.
function str_openssl_enc($str,$iv){
$key='*********';
$cipher="AES-128-CTR";
$options=0;
$iv=openssl_random_pseudo_bytes(16);
$str=openssl_encrypt($str,$cipher,$key,$options,$iv);
return $str;
}
**In below function I am calling the encryption function
//VALIDATE NEW USER INFO**
function validate_new_user() {
if($_SERVER['REQUEST_METHOD'] == "POST") {
$errors = [];
$min = 3;
$max = 25;
$username = clean($_POST['username']);
$email = clean($_POST['email']);
$email= str_openssl_enc($email, $iv); // Call Encryption function
$firstname = clean($_POST['firstname']);
$lastname = clean($_POST['lastname']);
$company = clean($_POST['company']);
if($_POST['client'] == 1) {
$client = clean($_POST['client']);
$client = escape($client);
} else {
$client = 0;
}
if($_POST['req'] == 1) {
$requirements = clean($_POST['req']);
$requirements = escape($requirements);
} else {
$requirements = 0;
}
if($_POST['qfm'] == 1) {
$qfm = clean($_POST['qfm']);
$qfm = escape($qfm);
} else {
$qfm = 0;
}
$password = random_password(8);
$dateAdded = date('Y-m-d H:i:s');
$username = escape($username);
$email = escape($email);
$firstname = escape($firstname);
$lastname = escape($lastname);
$company = escape($company);
if (strlen($username) > $max) {
$errors[] = "Username cannot be more than $max characters";
}
if (strlen($username) < $min) {
$errors[] = "Username cannot be fewer than $min characters";
}
//if (strlen($password) > $max) {
//$errors[] = "Password cannot be more than $max characters";
//}
//if (strlen($password) < $min) {
//$errors[] = "Password cannot be fewer than $min characters";
//}
if (ctype_alnum($username) != true) {
$errors[] = "Username must be alpha numeric";
}
//if($password != $Cpassword) {
//$errors[] = "Your passwords do not match";
//}
if(email_check($email)) {
$errors[] = "Email address already exists";
}
if(username_check($username)) {
$errors[] = "Username already exists";
}
if(!empty($errors)) {
foreach ($errors as $error) {
echo validation_error($error);
}
} else {
if(new_user($firstname, $lastname, $username, $email, $company, $password, $client, $qfm, $requirements)) {
set_message("New User Added");
//set_message("SQL: $sql");
redirect("users.php");
} else {
set_error_message("Error: User could not be added");
redirect("newUser.php");
}
}
}
}
and I also call encryption function on newUser.php page like this
<div class="container content-justify-center">
<div class="row">
<div class="col-lg-6 col-lg-offset-3 mx-auto">
<?php
display_message();
str_openssl_enc($str, $iv); // Email Encryption
validate_new_user();
?>
</div>
</div>
Once we add new user its redirect to users.php page where I want to display decrypted emails. Here is my users.php page
var table = $('#users').DataTable( {
dom: "lBfrtip",
orderCellsTop: true,
ajax: "../ajax/at/users.php",
fixedHeader: true,
responsive: {
details: {
display: $.fn.dataTable.Responsive.display.modal( {
header: function ( row ) {
var data = row.data();
return 'User Details';
}
} ),
renderer: $.fn.dataTable.Responsive.renderer.tableAll()
}
},
columns: [
{ data: "U.id" },
{ data: "U.firstname" },
{ data: "U.lastname" },
{ data: "U.email"
render: str_openssl_dec() // call decryption function
},
{ data: "R.roleName" },
{ data: "U.company" },
{ data: "U.lastLogin" },
{ data: "U.lastUpdated" },
{ data: "U1.username" },
{ data: "Y.yesNo" },
{ data: "Y1.yesNo" },
{ data: "Y2.yesNo" },
{ data: "Y4.yesNo" },
{ data: "Y3.yesNo" },
],
select: true,
stateSave: true,
"autoWidth" : false,
buttons: [],
"pageLength": 50,
"lengthMenu": [10, 25, 50, 100, 250]
} );
function str_openssl_dec($str,$iv){
$key='**********';
$cipher="AES-128-CTR";
$options=0;
$iv=openssl_random_pseudo_bytes(16);
$str=openssl_decrypt($str,$cipher,$key,$options,$iv);
return $str;
}
</script>
<?php require('../includes/fileend.php') ?>
**Here is my users controller page.
include("../lib/DataTables.php");
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate,
DataTables\Editor\ValidateOptions;
// Log function
require("logchange.php");
Editor::inst( $db, 'users_enc U', 'U.id' )
->field(
Field::inst( 'U.id' ),
Field::inst( 'U.firstname' ),
Field::inst( 'U.lastname' ),
Field::inst( 'U.email' ),
Field::inst( 'U.role' )
->options( Options::inst()
->table('role')
->value('roleID')
->label('roleName')
->order('roleID')
)
->validator( Validate::notEmpty( ValidateOptions::inst()
->message( 'What level of access should the user have?' )
) ),
Can experts please advice how I can decrypt the email column on users.php page and also how I can encrypt and decrypt existing email data which is plain text? Currently If I add new user - that email only encrypt and store in database, and on users page same encrypted email displayed. Really appreciate your help. Please share link examples, any function which I can use for decrypt in datatables ?
PS - I am very new to php, datatables and also doing encryption first time.
This question has an accepted answers - jump to answer
Answers
Hi,
Great question. To be clear, you do not want to do the decryption on the client-side as that would involve sending a decryption key to the client-side, which basically would mitigate any benefit you get from the encryption!
render: str_openssl_dec()won't work, since that function calls a PHP function. The key is to remember that Javascript will run on the client-side in this case, PHP on the server-side. You can't call functions from one or the other directly. You'd need to exchange information between then using a defined API (e.g. Ajax with JSON).What you need to do is use the
getFormatter()method of theFieldinstance. There is documentation about formatters available here. A little function to decrypt what is stored in the database will do.Allan
Hello allan, I tried using getFormatter() method this way. I define decryption function in the same file. Is it correct way ? I am getting errors.
Errors I am getting is undefined variables errors.
That looks about right to me! What is the result? I'm guessing it doesn't work, but in what way? Any errors?
Allan
undefined variables errors for all the variables - $cipher, $key, $options, $iv
Yup, those variables are defined in
str_openssl_decbut no where else. And the way scoping works in PHP means they are only accessible in that function.Try calling your function which defines those variables and returns the decrypted value:
Allan
Hi Allan
I used str_openssl_dec as you suggested but still getting error.
DataTables warning: table id=users - JSON encoding error: Malformed UTF-8 characters, possibly incorrectly encoded
Also is it possible to encrypt existing column using openssl_encrypt function? Currently I am encrypting only new users. So when I add new user email , it will encrypt and store encrypted email in email column in database.
That is out side the scope of what Editor does. You would need to have a little one time script that would read the value of each row, encrypt it and then write it back.
Regarding the UTF8 error, that suggests to me there might be some binary data hitting the JSON encode. Try:
You’ll still get an error. But the debug might give us a clue.
Allan
Yes Allan, its giving error but no response. its blank. Error is as per below
DataTables warning: table id=users - Ajax error. For more information about this error, please see http://datatables.net/tn/7
no response data available.
Also in for encryption I am converting iv like this ...
$iv=bin2hex($iv)so I tried $iv=hex2bin($iv) outside the function but same error.
That's unexpected. The JSON encoding is done after the formatter runs, so I'm not sure what is going wrong there.
As a sanity check you could use:
Just to make sure that is where the problem is. Assuming it is okay, add
echo $val."\n";just before the return and let me know what that shows please.Allan
Hi allan, I did changes in login_user function and now I am storing encrypted emails in email_encrypt column. I added decryption in same function. But there is something wrong in my if else loop. so if email_encrypt column is empty it will store encryption of email in that column , and if not empty it will verify email and allow user to login. But rightnow if email_encrypt not null it not allowing user to login in the system. Please check. right now I commented whole decryption section so user can relogin.
I'm afraid that is really beyond the scope of the support we can offer, as that is into general PHP development rather than something that is specific to DataTables or Editor.
You might be best asking on StackOverflow or similar for general web-dev support.
Allan
Okay Thank you so much Allan for helping me.