CODEWITHSUNDEEP
mysqlaes
Senica Gonzalez
Senica Gonzalez

Reputation: 8192

mysql aes_encrypt into longtext column

Is it possible to store a MySQL AES_ENCRYPT into a LONGTEXT column?

I know I'm suppose to use varbinary or blob, but I have a table that I'm storing a bunch of random "settings" in, and the settings_value column is longtext.

I went to store a "smtp mail password" in there, and got a little stuck.

If not, I guess, I'll store it as a hex string through php.

SOLUTION:

My query was something like this:

INSERT INTO table (setting_value)VALUES(AES_ENCRYPT('password', 'key')) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)

As you will see in my comments below, I tried changing my column encoding from utf8_unicode_ci to utf8_bin and still it failed. I changed to latin1_bin and it worked.

I switched back to utf8_unicode_ci and changed my query to the following:

INSERT INTO table (setting_value)VALUES(HEX(AES_ENCRYPT('password', 'key'))) ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value)

That worked since it just turned my value into a hex string.

Took me a second to figure out how to get the value back out correctly, so for documentation purposes:

$pass = SELECT AES_DECRYPT(BINARY(UNHEX(setting_value)), 'key') as orig_text FROM table
echo $pass->orig_text

Upvotes: 3

Views: 4747

Answers (2)

sbrbot
sbrbot

Reputation: 6469

Use some binary column type (like BLOB instead of LONGTEXT) for storing AES_ENCRYPTed content.

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65577

Did you try it? It's pretty easy to set up a test case, and from what I can see it works fine for your requirements:

mysql> create table t (id int unsigned not null auto_increment primary key, str LONGTEXT);
Query OK, 0 rows affected (0.13 sec)

mysql> desc t;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| str   | longtext         | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

mysql> 
mysql> INSERT INTO t VALUES (1,AES_ENCRYPT('text','password'));
Query OK, 1 row affected (0.02 sec)

mysql> 
mysql> select id,str,AES_DECRYPT(str,'password') from t;
+----+-----------------------------+-----------------------------+
| id | str                         | AES_DECRYPT(str,'password') |
+----+-----------------------------+-----------------------------+
|  1 | ö½¨Ü·øÍJ/ª¼Tf€D            | text                        |
+----+-----------------------------+-----------------------------+
1 row in set (0.00 sec)

Upvotes: 2

Related Questions