Plain text stores a value as ordinary characters rather than as a protected transformed value. A password column that holds the password itself is storing it in plain text. That may seem convenient, but it creates a serious risk: someone who gains access to the database can read and reuse those passwords. section 7.4
Do not store sensitive information, such as passwords, in plain text. Use an appropriate encryption method instead. For passwords, one-way encryption produces a hash: a fixed-length result from which the original password cannot be recovered. This limits the damage if a database is compromised. section 7.4 section 7.14
When replacing a plain-text password column with hashed values, account for the stored form:
- Choose a one-way algorithm, such as bcrypt, to hash each password. section 7.4
- Encode binary hash output as ASCII characters when it needs to be stored in a character column. section 7.4
- Resize the column to fit the encoded value. A 40-byte bcrypt result encoded as ASCII requires 60 bytes, so VARCHAR(20) must become CHAR(60). section 7.4
Updating a password column
A user table initially designed to keep passwords in plain text can change its password column to CHAR(60) before storing encoded bcrypt hashes. The following MySQL and MariaDB statement changes the column size; hashing and encoding must happen before values are stored. section 7.4
-- comment: works for MySQL and MariaDB -- comment: consult ChatGPT on adaptations -- comment: for other RDBMS ALTER TABLE user MODIFY COLUMN password CHAR(60);
Use this SQL statement to alter the user.password column to CHAR(60) for encoded password hashes. section 7.4
The same rule applies beyond passwords: select a suitable encryption method for sensitive data, then make sure each destination column has enough room for the encrypted result. Protecting data requires both transformation and a compatible database design. section 7.14 section 7.4
