CODEWITHSUNDEEP
mysqldatabase-designschemarelational-database
Trent Scott
Trent Scott

Reputation: 2028

Use BLOB or VARBINARY for Encrypted Data in MySQL?

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?

Upvotes: 5

Views: 12523

Answers (2)

elousf
elousf

Reputation: 41

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)

Upvotes: 4

Bohemian
Bohemian

Reputation: 424993

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.

Upvotes: 12

Related Questions