0

I am trying to decrypt a value after a where statement looking like this below

$sqlz = $this->conn-> prepare("SELECT * FROM registration WHERE 
 CAST(AES_DECRYPT('email', '$tknz') AS CHAR) = ?");
 $sqlz-> bindValue(1,$email, PDO::PARAM_STR);
 $sqlz -> execute();
 $rowz = $sqlz->fetch(PDO::FETCH_ASSOC);

But every time I try to run this section I get the below error

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ErKhCAqKCgMIte802cqBHdTH0YxKhlWDXo4cYoDCZLPnWtZWdIZAlu1dMcpD3q7M012UwUbzzGhwXrxGasg78EDJIXVXDcdYSXo0rX8wNGhgOQgx6KQgwVQhubuCwJQW5ZozNpNR3qHgIbhTswSMzQYaUTjp0xJS5Kgvw9sz8LIAU1Dg7zSR0HnWL2P6UcVH' in 'field list'

What I'm missing or not doing?

Dharman
  • 30,962
  • 25
  • 85
  • 135
yobra89
  • 19
  • 6
  • MySQL is trying to use the result of `CAST(AES_DECRYPT('email', '$tknz') AS CHAR)` as a column name in the WHERE clause. I'm pretty sure that's not what you intended. e.g. your query will end up as `SELECT * FROM registration WHERE ErKhCAqKCgMIte802cqBHdTH0YxKhlWDXo4cYoDCZLPnWtZWdIZAlu1dMcpD3q7M012UwUbzzGhwXrxGasg78EDJIXVXDcdYSXo0rX8wNGhgOQgx6KQgwVQhubuCwJQW5ZozNpNR3qHgIbhTswSMzQYaUTjp0xJS5Kgvw9sz8LIAU1Dg7zSR0HnWL2P6UcVH = ?` – ADyson Oct 22 '21 at 15:26
  • I might suggest doing this the other way round - encrypt the `$email` value so you can compare it to the already-encrypted value in the `email` column, like the example in https://stackoverflow.com/questions/43477772/php-query-compare-user-input-and-aes-encrypted-data-in-mysql . Note also the recommendation against field-level encryption of this sort, though. There is probably a better way to protect this data. – ADyson Oct 22 '21 at 15:29
  • 1
    **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Oct 22 '21 at 16:47
  • hi @ADyson iv tried out your solution but im still getting the same result – yobra89 Oct 25 '21 at 11:00
  • The exact same error? Please edit the question to show precisely what you tried. – ADyson Oct 25 '21 at 15:19

0 Answers0