MySQL PASSWORD() for encryption

[softarrow]

Hi to all!

 

I want to ask you that, what are the advantages and disadvantages of using MySQL PASSWORD() function to encrypt the sensitive data in database.

Should i opt for MySQL PASSWORD() function or PHP md5() function, please suggest me?

[bubblegum.anarchy]

md5() should be used instead of password() to encrypt data, from mysql documentation:

 

Note: The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, use MD5() or SHA1() instead. Also see RFC 2195 for more information about handling passwords and authentication securely in your applications.

[softarrow]

Thanks for your reply.

I have read MYSQL documentation and they say:

  The encryption and compression functions return binary strings. For many of these functions, the result might contain arbitrary byte values. If you want to store these results, use a BLOB  column rather than a CHAR or VARCHAR column to avoid potential problems with trailing space removal that would change data values.

 

BLOB column will take unnecessary storage space in database, is there any other option to use CAHR or VARCHAR field to store the data.

[bubblegum.anarchy]

I have never had a problem using a varchar column for encrypted data, besides BLOB holds a variable amount of data anyway or use VARBINARY instead.

[fenway]

I have never had a problem using a varchar column for encrypted data, besides BLOB holds a variable amount of data anyway or use VARBINARY instead.

Just be careful about the maximum length, certain encryption functions are block-based.

[bubblegum.anarchy]

Is the following a good idea, fenway:

 

ALTER TABLE account MODIFY password VARBINARY(100) not null default 'password' comment 'account password';

[fenway]

Good idea how?

[bubblegum.anarchy]

Good idea how?

 

To resolve any issues relating to encryption functions that might contain arbitrary byte values.

[fenway]

Ah... depends which encryption function, but most produce normal ascii output without hex.