How to encrypt and decrypt column using php and datatables - render

How to encrypt and decrypt column using php and datatables - render

Shivani VyasShivani 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

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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 the Field instance. There is documentation about formatters available here. A little function to decrypt what is stored in the database will do.

    Allan

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0
    edited January 2022

    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.

    <?php
    //SESSION START
    
    if(!isset($_SESSION)) {
        session_start(); 
        if(isset($_SESSION['userID'])) {
            $userID = $_SESSION['userID'];
        } else {
            $userID = null;
        }
    }    
    
    //Decryption Email
    
    
    function str_openssl_dec($str){
        $key='***********';
        $cipher="AES-128-CTR";
        $options=0;
        $iv=openssl_random_pseudo_bytes(16);
        $str=openssl_decrypt($str,$cipher,$key,$options,$iv);
        return $str;
    }
    
    
    
    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' )
        ->getFormatter( function ( $val, $data ) use ($key, $iv) { 
            return openssl_decrypt($val, $cipher, $key, $options, $iv);
    } ),
    

    Errors I am getting is undefined variables errors.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    That looks about right to me! What is the result? I'm guessing it doesn't work, but in what way? Any errors?

    Allan

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    undefined variables errors for all the variables - $cipher, $key, $options, $iv

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Yup, those variables are defined in str_openssl_dec but 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:

    ->getFormatter( function ( $val ) {
         return str_openssl_dec($val);
    }
    

    Allan

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    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

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Also is it possible to encrypt existing column using openssl_encrypt function?

    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:

    ->getFormatter( function ( $val ) {
         $str = str_openssl_dec($val);
         echo “Val: “. $val .”`\n”;
         echo “Dec: “. $dec .”`n”;
         return $str;
    }
    

    You’ll still get an error. But the debug might give us a clue.

    Allan

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    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.

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    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.

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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:

    ->getFormatter( function ( $val ) {
      return 'test';
    }
    

    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

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    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.

    //LOG USER IN
    function login_user($email, $password) {
    
      $fields = [
        'id',
        'username',
        'firstname',
        'lastname',
        'password',
        'role',
        'active',
        'email',
        'email_encrypt',
        'user_iv',
        'requirements',
        'client',
        'blocked',
        'qfm',
        'tpwd'
      ];
    
      $link = connect();
      $link->where('email', $email);
      $ip = $_SERVER["REMOTE_ADDR"];
      if (!$result = $link->getOne('users_enc', $fields)) {
        query("INSERT INTO ip (address ,timestamp)VALUES ('$ip',CURRENT_TIMESTAMP)");
        redirect("login.php");
      } elseif($result['blocked'] == 1) {
        set_error_message("Your account has been suspended.<br />Please contact an administrator if you believe this to be an error");
        $user = $result['id'];
        query("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'user blocked', '$ip')");
        redirect("login.php");
    
      } else {
        $user = $result['id'];
        $email_encrypt = $result['email_encrypt'];
        $iv = $result['user_iv'];
        $auth = password_verify($password, $result['password']);
    
          if ($auth) {
    
            if(!$result['active'] == 1) {
              set_info_message("Account not yet activated - Please check your email for activation link");
              query("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'User not activated', '$ip')");
              redirect("login.php");
            }
           // else if (!is_null($result['email_encrypt'])){
                //if password correct and email_encrypt field is NOT null
                //Decrypting email
               // $iv = hex2bin($result['user_iv']);
               // $email_decrypt= str_openssl_dec($result['email_encrypt'], $iv); 
                //set_info_message($email_decrypt);
                //if($email_decrypt == $result['email']){
    
                        // Set session variables
                    // $_SESSION['userID'] = $result['id'];
                    // $_SESSION['username'] = $result['username'];
                    // $_SESSION['firstname'] = $result['firstname'];
                    // $_SESSION['lastname'] = $result['lastname'];
                    // $_SESSION['role'] = $result['role'];
                    // $_SESSION['timeout'] = time();
                    // $_SESSION['email'] = $result['email'];
                    // $_SESSION['email_encrypt'] = $result['email_encrypt'];
                    // $_SESSION['user_iv'] = $result['user_iv'];
                    // $_SESSION['client'] = $result['client'];
                    // $_SESSION['blocked'] = $result['blocked'];
                    // $_SESSION['requirements'] = $result['requirements'];
                    // $_SESSION['qfm'] = $result['qfm'];
    
                    // $link->where('email', $result['email']);
                    // $lastLogin = date('Y-m-d H:i:s');
                    // $link->update('users_enc', ['lastLogin' => $lastLogin]);
    
                //     query("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'login Successful', '$ip')");
                //     //set_message("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'login Successful', '$ip')");
                //     if(!empty($_SESSION['userID'])) {
                //         $ip = $_SERVER["REMOTE_ADDR"];
                //         $time = 30;
                //         $link = f_sqlConnect();
                //         $sql = "DELETE FROM `ip` WHERE address = '$ip' OR `timestamp` < (now() - interval $time minute)";
                //         query($sql);
                //     }
                //     //set_message("Logged in successfully");
                //     redirect("../home.php");
                // }
                // else{
                //     //when decrypted email not matching email
                //     $ip = $_SERVER["REMOTE_ADDR"];
    
                //     set_info_message("Email mismatch - Please contact an administrator");
                //     //query("INSERT INTO `ip` (`address` ,`timestamp`)VALUES ('$ip',CURRENT_TIMESTAMP)");
                //     query("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'Decrypted email mismatch', '$ip')");
                //     redirect("login.php"); 
                // }
            //}
            else {
    
             //if Password correct 
    
            // Set session variables
              $_SESSION['userID'] = $result['id'];
              $_SESSION['username'] = $result['username'];
              $_SESSION['firstname'] = $result['firstname'];
              $_SESSION['lastname'] = $result['lastname'];
              $_SESSION['role'] = $result['role'];
              $_SESSION['timeout'] = time();
              $_SESSION['email'] = $result['email'];
              $_SESSION['email_encrypt'] = $result['email_encrypt'];
              $_SESSION['user_iv'] = $result['user_iv'];
              $_SESSION['client'] = $result['client'];
              $_SESSION['blocked'] = $result['blocked'];
              $_SESSION['requirements'] = $result['requirements'];
              $_SESSION['qfm'] = $result['qfm'];
    
              $link->where('email', $result['email']);
    
              //if email_encrypt field is null
              if(is_null($result['email_encrypt'])){
                //Email encryption
                $iv=openssl_random_pseudo_bytes(16);
                $email_encrypt= str_openssl_enc($email, $iv); 
                $iv = bin2hex($iv); //binary to hex 
    
                $link->update('users_enc', ['email_encrypt' => $email_encrypt]); // Adding encrypted emails in table
                $link->update('users_enc', ['user_iv' => $iv]); // Adding IVs in table
            }
    
    
              $lastLogin = date('Y-m-d H:i:s');
              $link->update('users_enc', ['lastLogin' => $lastLogin]);
    
              query("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'login Successful', '$ip')");
              //set_message("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'login Successful', '$ip')");
              if(!empty($_SESSION['userID'])) {
                $ip = $_SERVER["REMOTE_ADDR"];
                $time = 30;
                $link = f_sqlConnect();
                $sql = "DELETE FROM `ip` WHERE address = '$ip' OR `timestamp` < (now() - interval $time minute)";
                query($sql);
              }
                //set_message("Logged in successfully");
                redirect("../home.php");
            }
          } else {
              //when password is incorrect
            $ip = $_SERVER["REMOTE_ADDR"];
            query("INSERT INTO `ip` (`address` ,`timestamp`)VALUES ('$ip',CURRENT_TIMESTAMP)");
            query("INSERT INTO loginActivity (user ,loginTime, success, ip)VALUES ($user,CURRENT_TIMESTAMP, 'password incorrect', '$ip')");
            redirect("login.php");
          }
      } 
    }
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    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

  • Shivani VyasShivani Vyas Posts: 113Questions: 11Answers: 0

    Okay Thank you so much Allan for helping me.

Sign In or Register to comment.