PHP查询/比较MySQL中的用户输入和aes加密数据 [英] PHP Query/Compare user input and aes encrypted data in MySQL

问题描述

我试图找到通过 PHP 查询 MySQL 表的正确方法，表中的字段是 AES 加密的.我有一个登录表单，它获取 last_name 和其他一些字段并进行查询以进行比较.这是相关结构的一个片段.

I am trying to find the proper way to query a MySQL table via PHP, with fields in a table that were AES encrypted. I have a login form that grabs last_name plus a few other fields and does a query to compare. Here is a snippit of the relevant structure.

我使用以下方法插入数据:

I inserted the data using:

AES_ENCRYPT('".$last_name."','".$encryption_key."') 

我可以解密，并使用以下方法输出数据:

I can decrypt, and output the data using:

CAST(AES_DECRYPT(last_name, '$encryption_key') AS CHAR(66)) last_name_decrypt

但是我无法与工作进行比较.我试过了

However I can't get a compare to work. I've tried

$query = "SELECT * from {$tablename} where last_name = AES_ENCRYPT('".$unauth_last_name."','".$encryption_key."')";

感谢任何帮助或指导.

推荐答案

我认为您缺少与 php 获取的姓氏的比较 - 可能来自登录 html 表单.假设保存您要检查的姓氏的 php 变量名为 $in_last_name，则查询可能类似于 sg，如下所示:

I think you are missing the comparison with the last name obtained by php - probably from a login html form. Assuming that the php variable holding the last name you would like to check against is called $in_last_name, the query may look like sg like as follows:

    $query = "SELECT * from {$tablename} WHERE last_name = AES_ENCRYPT('".$in_last_name."','".$encryption_key."')";

(显然，您应该清理您的输入或使用带有参数绑定的准备好的语句来防止 sql 注入攻击.由于我不知道您使用什么 API 连接到 MySQL，因此我无法为此提出确切的解决方案.无论如何，这超出了这个问题的范围.)

(Obviously, you should sanitize your input or use prepared statements with parameter binding to prevent sql injection attacks. Since I do not know what API you use to connect to MySQL, I cannot suggest an exact solution for this. Anyway, this is beyond the scope of this question.)

查询对输入的姓氏(纯文本)进行加密，并根据存储的加密数据进行检查.此外，where 子句中不得有字段别名.

The query encrypts the inputted last name (plain text) and checks it against the stored encrypted data. Also, there must be no field alias in the where clause.

同样，以这种方式使用字段级加密不会使数据安全，因为它依赖于加密密钥的保密性.这种加密只会让您的生活更加困难.

Again, using field level encryption this way does not make the data that secure since it relies on the encryption key being secret. Such encryption only makes your life more difficult.

您可能想查看在 MySQL 中加密数据的其他方法.例如，查看 MySQL Enterprise Encryption，它允许 DBA 管理加密数据，而实际上无需访问纯文本版本.是的，这不是免费产品.

You may want to check out other means of encrypting data within MySQL. Check out MySQL Enterprise Encryption for example, which allows DBAs to manage encrypted data without actually having access to the plain text version. Yes, this is not a free product.

这篇关于PHP查询/比较MySQL中的用户输入和aes加密数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！