PHP and MYSQL AES_Encrypt Issues

Question

A client of mine wants clients names to be encrypted in the database, don't ask why they are just intent of it so I have been trying to do MySQL AES_Encrypt and AES_Decrypt using PHP so I would REALLY appreciate some help...

Encrypt Code

function addname ($name, $refid) {

    include("../config.php");

    // Open up a new MySQLi connection to the MySQL database
    mysql_connect($dbHost, $dbUsername, $dbPassword);
    mysql_select_db($dbTable);

    $code = substr($output, 0, 8);

    if (!isset($refid)) {
        $refid = "ERROR";
    }

    $query = "INSERT INTO `clients` (fname, code, refid, active) VALUES     (AES_ENCRYPT('$fname', UNHEX('F3229A0B371ED2D9441B830D21A390C3')), '$code', '$refid',   0)";

    $runQuery = mysql_query($query);

    if ($runQuery != true) {
        return mysql_error();
    } else {
        return $code;
    }
}

Decrypt Code

function decryptname() {
        $input=947270;

        include("config.php");

        // Open up a new MySQLi connection to the MySQL database
        mysql_connect($dbHost, $dbUsername, $dbPassword);
        mysql_select_db($dbTable);


        // Build the query
        $sqlToRun = "SELECT * FROM `clients` WHERE code='$input' AND active=0";

        // Run it
        $check = mysql_query($sqlToRun);

        while($row = mysql_fetch_array($check)) {

        $encryptedname = $row['fname'];

        $decryptedname = mysql_query("AES_DECRYPT('$encryptedname', UNHEX('F3229A0B371ED2D9441B830D21A390C3'))");

        $check2 = $row['fname'];
          }

        mysql_close();


        if (!isset($check2)) {
            $check2 = "wow there is no check2";
        }


        exit($check2);

}

decryptname();

The Problem

MySQL Database shows the following value, which to be looks normal

e309367d1867c3273a8f8b298ed8beb3

Basically when ever I don't include the $decryptedname I get the following as a output

ã6}gÃ':‹)ŽØ¾³

If I do include it, I get a blank screen and no PHP or MySQL Errors?

Some More Information

The database column structure for names is

varbinary(9999)

If anyone can help me I would really appreciate it, if you need more info please ask!

UPDATE

I ran the following command in SQL and it returned NULL

SELECT AES_DECRYPT('password', "UNHEX('F3229A0B371ED2D9441B830D21A390C3')") FROM passwords WHERE code=947270

score 1 · Accepted Answer · edited May 23 '17 at 10:29

1

Take a look at PHP AES encrypt / decrypt, that should help you a lot.

Don't encrypt using MySQL, use PHP instead. You don't want queries taking any longer than they currently do performing reads & writes on the database.

edited May 23 '17 at 10:29

Community

1
1

answered Sep 08 '13 at 13:24

SamV

7,548
4
39
50

5

Using PHP to encrypt the person's names means that searching names will be more difficult. if the client needs the names encrypted in the database use MYSQL to encrypt it. then when you search you can use the mysql functions to decrypt/filter – pgee70 Jan 31 '14 at 00:04
While pgee70 is technically right, knowing that the names are encrypted means you can run the same encryption on the string before running the search and matching using that rather than the plaintext version. – Tarquin Nov 13 '19 at 23:05

score 0 · Answer 2 · answered Apr 15 '16 at 17:08

I was having similar problems. My encrypted data field was a CHAR field. I read somewhere online (https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html) that mysql may be cropping trailing blanks from the data and as a result corrupting it for the decrypt. Using a BLOB instead solved my problem.

PHP and MYSQL AES_Encrypt Issues

2 Answers2