How to Encrypt existing Email column using openssl_encrypt() in PHP

Hello I am trying to encrypt existing email column using openssl_encrypt() function. So currently I am able to encrypt new user’s email and encrypted email I am storing in another column called email_encrypt. so now I want to update login_user function such that when user login using email and password that email should be encrypt and should be store in email_encrypt column in database if email_encrypt column is null. but when I call openssl_encrypt function in login_user function it generates new encryption every time when user login. So everytime different encrypted email stored in database for same email address. I am not sure what is wrong. I am learning PHP and my task is to update existing user emails with encryption. here what I have done so far.

This is my function.php file in which all the functions are stored.

// Encryption Email

function str_openssl_enc($str, $iv){
	$key='**************';
	$cipher="AES-128-CTR";
	$options=0;
	$encyptedStr=openssl_encrypt($str,$cipher,$key,$options,$iv);
	return $encyptedStr;
}

here is login functions.

//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'];
    $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(empty($result['email_encrypt'])){

                //Email encryption

            $iv=openssl_random_pseudo_bytes(16); // generate random 16 digit number which should be unique for each email
            $email_encrypt= str_openssl_enc($email, $iv); // storing encrypted emails in email_encrypt column
            $iv = bin2hex($iv); //binary to hex 

          }
        // 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']);
          $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 {
        $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");
      }
  } 
}

So I want to check if email_encrypt column is empty than encryption should work and stored the value in database only one time, when user login. Now there are encrypted email stored and when user login again with same email address it should just verify with encrypted column and allow user to login. But in my case when I run above code and try to login using my email address everytime when I login it stores different encrypted email in database and also with different IV. So please help what changes should I do in my login_user function so that once encryption is done it should stored and should not change each and every time when I login using same email address.

Exactly why are you trying to do this?

You do realize that to use the encrypted email address to log anyone in, since you have a random iv value for each email address, you will need to retrieve all the email and iv values and either decrypt each of the email addresses to find if one matches the submitted email address or you will need to do the reverse and encrypt the submitted email address using each iv value to find if one matches the stored encrypted email address. Is this really what you intended to do?

I just noticed that you have a username column. This and the hashed password should be used for user authentication/login purposes.

BTW - the only user value you should store in a session variable upon successful login is the user’s id. All other user related data should be queried for on each page request so that any changes in a value will take effect on the very next page request. By storing these other user values in session variables, they will only take effect upon the next login, so, you won’t be able to do things like promote or demote the user role, block a user, …

As to why your current code always generates a new encrypted email value, you would need to add debugging statements to the code to find out why. What value is in $result[‘email_encrypt’]? Are the update queries actually working and setting those columns with the correct values?

I just realized what is wrong with the code. The update queries for email_encrypt and user_iv are in the wrong place. If there is a non-empty $result[‘email_encrypt’], the $email_encrypt and $iv variables won’t be set, and the two update queries will update those columns to null/empty values, causing the next successful login to generate and store new values. Those two update queries need to be right after where $email_encrypt and $iv are being set.

Hello @phdr , Thank you for the response.

You mean update queries need to be in this code after $iv=bin2hex() ??

else {

            if(empty($result['email_encrypt'])){

                //Email encryption

            $iv=openssl_random_pseudo_bytes(16); // generate random 16 digit number which should be unique for each email
            $email_encrypt= str_openssl_enc($email, $iv); // storing encrypted emails in email_encrypt column
            $iv = bin2hex($iv); //binary to hex 

          }

@phdr this is how I updated yesterday… So now Issue is if $email_encrypt column is not null it should verify email and email’s encrypted version, when user login… if its matched allow user to login. But rightnow if I try to login again it won’t allow me. Giving error. Please check commented part after > if(!$result[‘active’] == 1) {…}

//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");

      }

  }

}
Sponsor our Newsletter | Privacy Policy | Terms of Service