Openssl_Encryption with prepared statements

Question

I have written a bit of code below that allows for the creation of accounts. I'd like to add an extra layer of protection by encrypting all data (except for password and username). I have a two questions:

1. Is Openssl the best php encryption practice?

2. How would I add openssl to my code?

I'm having a bit of difficulty integrating openssl with my prepared statement code.

My code:

<?php
session_start();
require_once './config/config.php';
require_once 'includes/auth_validate.php';

//Only super admin is allowed to access this page
if ($_SESSION['admin_type'] !== 'super') {
    // show permission denied message
    echo 'Permission Denied';
    exit();
}

if ($_SERVER['REQUEST_METHOD'] == 'POST') 
{
    $admin_type = mysqli_real_escape_string($conn, $_POST['admin_type']);
    $position = mysqli_real_escape_string($conn, $_POST['position']);
    $first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
    $last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
    $user_name = mysqli_real_escape_string($conn, $_POST['user_name']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $phone_number = mysqli_real_escape_string($conn, $_POST['phone_number']);
    $passwd = mysqli_real_escape_string($conn, $_POST['passwd']);
    $about = mysqli_real_escape_string($conn, $_POST['about']);

    //Error handlers 
    //Check for empty fields 
    if (empty($admin_type) || empty($position) || empty($first_name) || empty($last_name) || empty($user_name) || empty($passwd)){
        $_SESSION['failure'] = "Admin was not created, missing imporant details!";
        header('location: admin_users');
        exit();
    } else {
        $sql = "SELECT * FROM admin_accounts WHERE user_name='$user_name'";
        $result = mysqli_query($conn, $sql);
        $resultCheck = mysqli_num_rows($result);

        if ($resultCheck > 0) {
            $_SESSION['failure'] = "Admin was not created, username already used!";
            header('location: admin_users');
            exit();    
        } else {
            //Hashing password 
            $hashedPasswd = password_hash($passwd, PASSWORD_DEFAULT); 

            //Insert the user into the database 
            $sql = "INSERT INTO admin_accounts (admin_type, position, first_name, last_name, user_name, email, phone_number, passwd, about) VALUES (?,?,?,?,?,?,?,?,?);";

            $stmt = mysqli_stmt_init($conn);
            if (!mysqli_stmt_prepare($stmt, $sql)) {
                echo "SQL Error";
            } else {
                mysqli_stmt_bind_param($stmt, "sssssssss", $admin_type, $position, $first_name, $last_name, $user_name, $email, $phone_number, $hashedPasswd, $about);
                mysqli_stmt_execute($stmt);
              {
                    $_SESSION['success'] = "Admin user added successfully!";
                    header('location: admin_users');
                    exit();
                }     
            }
        }
    }
}
$edit = false;

Openssl_Encryption Example:

<?php
//$key should have been previously generated in a cryptographically safe way, like openssl_random_pseudo_bytes
$plaintext = "message to be encrypted";
$cipher = "aes-128-gcm";
if (in_array($cipher, openssl_get_cipher_methods()))
{
    $ivlen = openssl_cipher_iv_length($cipher);
    $iv = openssl_random_pseudo_bytes($ivlen);
    $ciphertext = openssl_encrypt($plaintext, $cipher, $key, $options=0, $iv, $tag);
    //store $cipher, $iv, and $tag for decryption later
    $original_plaintext = openssl_decrypt($ciphertext, $cipher, $key, $options=0, $iv, $tag);
    echo $original_plaintext."\n";
}

My Attempt at encrypting First_Name only: (This does not work, no effect in database)

<?php
session_start();
require_once './config/config.php';
require_once 'includes/auth_validate.php';

//ONLY SUPER ADMINS ARE ALLOWED TO ACCESS THIS PAGE 
if ($_SESSION['admin_type'] !== 'super') {
    // show permission denied message
    echo 'Permission Denied';
    exit();
}


if ($_SERVER['REQUEST_METHOD'] == 'POST') 
{
$admin_type = mysqli_real_escape_string($conn, $_POST['admin_type']);
$position = mysqli_real_escape_string($conn, $_POST['position']);
$first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
$last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
$user_name = mysqli_real_escape_string($conn, $_POST['user_name']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$phone_number = mysqli_real_escape_string($conn, $_POST['phone_number']);
$passwd = mysqli_real_escape_string($conn, $_POST['passwd']);
$about = mysqli_real_escape_string($conn, $_POST['about']);
    
    //EROOR HANDLERS
    //CHECK FOR EMPTY FIELDS 
    if (empty($admin_type) || empty($position) || empty($first_name) || empty($last_name) || empty($user_name) || empty($passwd)){
        $_SESSION['failure'] = "Admin was not created, missing imporant details!";
        header('location: admin_users');
        exit();
    } else {
                $sql = "SELECT * FROM admin_accounts WHERE user_name='$user_name'";
                $result = mysqli_query($conn, $sql);
                $resultCheck = mysqli_num_rows($result);
                
                if ($resultCheck > 0) {
                    $_SESSION['failure'] = "Admin was not created, username already used!";
                    header('location: admin_users');
                    exit();    
                } else {
                    //HASHING PASSWORD 
                    $hashedPasswd = password_hash($passwd, PASSWORD_DEFAULT); 
                    
                    //INSERT THE USER INTO THE DATABASE  
                    $sql = "INSERT INTO admin_accounts (admin_type, position, first_name, last_name, user_name, email, phone_number, passwd, about) VALUES (?,?,?,?,?,?,?,?,?);";

                    $stmt = mysqli_stmt_init($conn);
                    if (!mysqli_stmt_prepare($stmt, $sql)) {
                        echo "SQL Error";
                    } else {
                        mysqli_stmt_bind_param($stmt, "sssssssss", $admin_type, $position, $first_name, $last_name, $user_name, $email, $phone_number, $hashedPasswd, $about);
                        mysqli_stmt_execute($stmt);
                        
                        {
                            $first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
                            $cipher = "aes-128-gcm";
                            if (in_array($cipher, openssl_get_cipher_methods()))
                            {
                                $ivlen = openssl_cipher_iv_length($cipher);
                                $iv = openssl_random_pseudo_bytes($ivlen);
                                $ciphertext = openssl_encrypt($first_name, $cipher, $key, $options=0, $iv, $tag);
                            }

                        }
                        
                      {
                            $_SESSION['success'] = "Admin user added successfully!";
                            header('location: admin_users');
                            exit();
                        }     
                    }

                }
                
            }
            
        }
               

$edit = false;

?>

Answer 1

. I'd like to add an extra layer of protection by encrypting all data (except for password and username).

As I mentioned In my comments, the Practical aspect of this is you probably wont be able to search on any data that you encrypt using AES, this is because AES returns a different value every time you encrypt something. This is a good thing, but it renders searching on it nearly impossible. You can do something like in addition to AES create a hash using something like SHA256, hashes do return the same value everytime, (but they are one way, no decryption). So with a hash stored in the DB you can hash your data and look up the hash in the DB you previously stored. The drawback of this, besides added complexity, is it generally weakens your encryption. If they cant attack the AES they can try to brute force the HASH for example.

Now that said, if you really need to encrypt some data. Then I create one field in the user table, and then take the data, JSON encode, or serialize it. This will convert an array of data to a string basically. Then you can encrypt that string.

You wont be able to search on the data, but it will all be in one place, and you only have to unecrypt it one time.

       $data = ['foo'=>'bar', 'stuff'=>'otherthing'];

       $json = json_encode($data); //'{"foo":"bar", "stuff":"otherthing"}'

       $encrypted = AESencrypt($json); //or whatever the function is

Then you take $encrypted save that in a field called secure or something.

One last thing, Is I would highly recommend using PHPSecLib2.0

https://github.com/phpseclib/phpseclib

No only will it give you a better API as its all object oriented, but it also has native implantation of SSL (although installing open_ssl extension will make it faster).

With PHPseclib this is all you need to do

function encryptAES($key, $plaintext){
    $Cipher = new AES(AES::MODE_CBC);
    $Cipher->setKey($key);    

    //create the IV
    $iv = Random::string($Cipher->getBlockLength() >> 3);
    $Cipher->setIV($iv);

    //encrypt and combine (you have to know the IV to decode. this is the typical way it's done) the IV is like a salt.
    $encrypted = $iv_base64 . base64_encode($Cipher->encrypt($plaintext));
    return $encrypted;
}


function decryptAES($key, $plaintext){
    $Cipher = new AES(AES::MODE_CBC);
    $Cipher->setKey($key);
    //find and decode the iv
    $iv = base64_decode(substr($ciphertext, 0, 22) . '==');

    //remove the iv from ciphertext
    $encrypted = substr($ciphertext, 22);

    $Cipher->setIV($iv);

    $decrypted = $Cipher->decrypt(base64_decode($encrypted));
    return $decrypted;
}

You have to base64 encode it because it's binary, this way it will store in a text field. You may be able to use sometype of binary field in the DB. Most the time when I do encryption it's dealing with files, so I am not sure about that.

NOTE

One word of caution, and this holds true for any symmetric 2-way encryption (encryption that uses 1 key instead of public/private pairs). Is that the security of it is only as secure as your key for the encryption. Because the server must know that KEY, if someone were to gain access to the filesystem and the the DB of your server there is a good chance they will be able to find the key you used to do encryption. This severely limits the "real" security this offers. That is not to say it's not worth doing, it just you have to understand the limitations and be aware of the risks whenever handling data that needs to be encrypted. For example I wouldn't use AES on server to store Credit Cards or other PCI type data.

Basically it shifts the responsibility for security from the database to the server where the code is. The raw data is useless because its encrypted, but the code must have access to the KEY, and if someone has access to that they can likewise decrypt the data. There are things you can do to harden a server against attack (DMZ etc.) but it's beyond the scope of the question here.

Cheers.