I'm working on a PHP application that accepts user input via a text area. It will be stored encrypted in the database (using AES_ENCRYPT).
Should I use a BLOB or VARBINARY field? Are there performance implications for either type of field?
2
-
AES_ENCRYPT() and AES_DECRYPT() enable encryption and decryption of data using the official AES (Advanced Encryption Standard) algorithm, previously known as “Rijndael.” Encoding with a 128-bit key length is used, but you can extend it up to 256 bits by modifying the source. We chose 128 bits because it is much faster and it is secure enough for most purposes. dev.mysql.com/doc/refman/5.1/en/…zod– zod2013-04-12 20:29:29 +00:00Commented Apr 12, 2013 at 20:29
-
stackoverflow.com/questions/504268/…zod– zod2013-04-12 20:32:25 +00:00Commented Apr 12, 2013 at 20:32
Add a comment
|
2 Answers
Both BLOB and VARBINARY are "string" data types, that store binary strings (effectively byte arrays), as opposed to the usual string types, which store character strings, with charset encoding etc.
In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like.
BLOB differs from VARBINARY in the following ways:
- There is no trailing-space removal for BLOB columns when values are stored or retrieved.
- For indexes on BLOB columns, you must specify an index prefix length.
- BLOB columns can not have DEFAULT values.
Use BLOB, because if your encrypted values happen to end in a space byte (hex 20), it would be truncated with VARBINARY, effectively corrupting your value. Also, you won't be putting an index on the encrypted value so the index issue doesn't matter, nor will you have a default value.
I don't think Bohemian's answer is accurate. VARBINARY like BLOB is to store binary data so it is capable of storing any data including spaces.
mysql> create table t1 (id integer auto_increment, data varbinary(100), primary key (id));
Query OK, 0 rows affected (0.09 sec)
// inserting '0', ' ', '0', ' ' - 4 characters including trailing space
mysql> insert into t1 (data) values (unhex('30203020'));
Query OK, 1 row affected (0.02 sec)
+----+------+
| id | data |
+----+------+
| 1 | 0 0 |
+----+------+
1 row in set (0.00 sec)
mysql> select t1.*, length(data) from t1;
+----+------+--------------+
| id | data | length(data) |
+----+------+--------------+
| 1 | 0 0 | 4 |
+----+------+--------------+
1 row in set (0.00 sec)
2 Comments
fgrieu
The assertion implicit in Bohemian's answer is that trailing spaces are removed form a VARBINARY. That assertion seems wrong to me on the basis of the doc, but is not disproved by the test shown.
fgrieu
@elsouf: so your (refined) experiment and the doc confirm that the advise in Bohemian's answer is based on the incorrect premise that in MySQL, trailing spaces are removed form a VARBINARY.