MySQL的AES_DECRYPT问题 [英] Problem with MySQL's AES_DECRYPT

问题描述

我正在寻找一种方法来加密MySQL数据库中的数据，并在出路上进行解密。此外，我希望能够在这些字段上执行正常的SQL查询，例如搜索和比较，这阻止我使用纯PHP解决方案。

我到AES_ENCRYPT（）和AES_DECRYPT（），可以使用MCRYPT在PHP中重复。

我很难与AES_DECRYPT一起尝试所有的建议，我可以

这是我的表：

  CREATE TABLE如果不存在`test_table`（
`id` int（6）NOT NULL，
`secure_info` text NOT NULL，
`encrypted_blob` blob NOT NULL，
`encrypted`文本NOT NULL，
 PRIMARY KEY（`id`）
）ENGINE = MyISAM DEFAULT CHARSET = utf8; 
  

我执行这些查询：

  INSERT INTO test_table（id，secure_info）VALUES（1，'Testing'）; 
 UPDATE test_table SET encrypted = AES_ENCRYPT（secure_info，'key'）WHERE id = 1 LIMIT 1; 
 UPDATE test_table SET encrypted_blob = AES_ENCRYPT（secure_info，'key'）WHERE id = 1 LIMIT 1; 
 
 SELECT *，AES_DECRYPT（加密，'key'）解密，AES_DECRYPT（encrypted_blob，'key'）as decryptpted_blob FROM test_table WHERE id = 1; 
  

我无法获得原始值。 'decryptpted'返回NULL，'decryptpted_blob'返回54657374696e67

任何想法或者更好的解决方案？

Blob解密工作很好，54657374696e67是测试，只有十六进制编码。您可能正在使用将BLOB显示为十六进制的工具来执行此操作。文本解密不起作用（不应该）。

I'm looking for a way to encrypt data on its way into a MySQL database, and decrypt it on the way out. Additionally, I would like to be able to perform normal SQL queries on those fields, such as searching and comparison, which prevents me from using a pure PHP solution.

This leads me to AES_ENCRYPT() and AES_DECRYPT(), which can be duplicated in PHP using MCRYPT.

I'm having a hard time with AES_DECRYPT and have tried all suggestions I can find through searches online.

Here's my table:

CREATE TABLE IF NOT EXISTS `test_table` (
  `id` int(6) NOT NULL,
  `secure_info` text NOT NULL,
  `encrypted_blob` blob NOT NULL,
  `encrypted` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I perform these queries:

INSERT INTO test_table (id, secure_info) VALUES (1,'Testing');
UPDATE test_table SET encrypted = AES_ENCRYPT(secure_info,'key') WHERE id=1 LIMIT 1;
UPDATE test_table SET encrypted_blob = AES_ENCRYPT(secure_info,'key') WHERE id=1 LIMIT 1;

SELECT *, AES_DECRYPT(encrypted,'key') as decrypted, AES_DECRYPT(encrypted_blob,'key') as decrypted_blob FROM test_table WHERE id=1;

I cannot get the original value. 'decrypted' returns NULL, and 'decrypted_blob' returns 54657374696e67

Any ideas, or perhaps a better solution?

解决方案

Blob decryption works just fine, "54657374696e67" is "Testing", only hex-encoded. You're probably executing this with a tool that displays blobs to hex. Text decryption doesn't work (and shouldn't).

这篇关于MySQL的AES_DECRYPT问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！