CODEWITHSUNDEEP
phpmysqlencryptionpdo
Brownman Revival
Brownman Revival

Reputation: 3850

decrypted value from mysql pdo php

$stmt2 = $dbh-> prepare("Select * from encryptme where ".decrypt('encryptedcolumn', $key)." = ?");
$stmt2 -> bindValue(1, $dec, PDO::PARAM_STR);
$stmt2 -> execute();

I have encrypt() and decrypt() i save the data after using encrypt() now i want to select the data but i am not able to because the data are encrypted. What I tried is i used the function in the column name but it is not working i get error saying

Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= '

How can i decrypt the column in the query statement?

Upvotes: 0

Views: 654

Answers (2)

Philipp
Philipp

Reputation: 2796

You can not use a php function to decrypt the value in the database while querying. (You can use a database function if it supports the entcryption method however)

You CAN instead send an encrypted parameter to the database to compare it to the encrypted stored value.

After fetching the data you can then decrypt the value to do whatever you need to.

edit after new information:

If you can not reliably encrypt the values in php, you will have to fetch all the data from the database and then decrypt it so you can compare the values.

Upvotes: 1

Tejas Mehta
Tejas Mehta

Reputation: 951

Here is your Solution 

$stmt2 = $dbh-> prepare("Select * from encryptme where CAST(AES_DECRYPT(encryptedcolumn, 'key') AS CHAR) = ?");
$stmt2 -> bindValue(1, $dec, PDO::PARAM_STR);
$stmt2 -> execute();

All the best.

Upvotes: 0

Related Questions