Best Practices for Bcrypt Hashed Password Columns in MySQL
- Bcrypt (Blowfish) is a cryptographic hash function designed specifically for password hashing.
- It incorporates a work factor (cost) that makes brute-force attacks computationally expensive.
- When a user creates a password, it's hashed using Bcrypt, and the resulting hash is stored in the database, not the original password.
Choosing the Right Column Type and Length
In MySQL, you have two options for storing this hash:
CHAR(60)
(orBINARY(60)
)- This fixed-length type allocates exactly 60 bytes for each entry, ensuring consistency.
- It's suitable if you're confident Bcrypt's output will always be 60 characters.
VARCHAR(255)
(orVARBINARY(255)
)- This variable-length type allows more space (up to 255 bytes) for potential future changes in Bcrypt's output length.
- It's generally recommended for future-proofing, especially if you're unsure about Bcrypt's exact output size.
Additional Considerations
- Salting: Always use a random salt (unique string) along with Bcrypt to further protect against rainbow table attacks. The salt is prepended to the password before hashing.
- Work Factor: The work factor controls the number of iterations used during hashing. Higher values make brute-force attacks more difficult, but take longer to compute. A moderate value (e.g., 12) is a good starting point, and you can adjust it based on your security needs and performance requirements.
Example (using PHP and MySQLi):
<?php
require_once('mysqli_connect.php'); // Assuming a separate file for database connection
function create_user($username, $password) {
$salt = openssl_random_pseudo_bytes(22); // Generate a random 22-byte salt
$hashed_password = password_hash($password, PASSWORD_BCRYPT, ['cost' => 12]); // Hash with Bcrypt and work factor 12
$stmt = $mysqli->prepare("INSERT INTO users (username, password, salt) VALUES (?, ?, ?)");
$stmt->bind_param('sss', $username, $hashed_password, base64_encode($salt)); // Store salt as base64-encoded string
$stmt->execute();
$stmt->close();
}
Remember:
- Never store passwords in plain text.
- Regularly update your security practices and stay informed about potential vulnerabilities.
Using mysqli:
<?php
require_once('mysqli_connect.php'); // Assuming a separate file for database connection
function create_user($username, $password) {
$salt = openssl_random_pseudo_bytes(22); // Generate a random 22-byte salt
$hashed_password = password_hash($password . $salt, PASSWORD_BCRYPT, ['cost' => 12]); // Hash with Bcrypt, work factor 12, and salt appended
$stmt = $mysqli->prepare("INSERT INTO users (username, hashed_password) VALUES (?, ?)");
$stmt->bind_param('ss', $username, $hashed_password);
$stmt->execute();
$stmt->close();
}
Using PDO:
<?php
require_once('db_connect.php'); // Assuming a separate file for database connection
function create_user($username, $password) {
$salt = openssl_random_pseudo_bytes(22); // Generate a random 22-byte salt
$hashed_password = password_hash($password . $salt, PASSWORD_BCRYPT, ['cost' => 12]); // Hash with Bcrypt, work factor 12, and salt appended
$sql = "INSERT INTO users (username, hashed_password) VALUES (?, ?)";
$stmt = $db->prepare($sql);
$stmt->execute([$username, $hashed_password]);
}
Python:
import secrets
import bcrypt
def create_user(username, password):
salt = secrets.token_bytes(22) # Generate a random 22-byte salt
hashed_password = bcrypt.hashpw(password.encode('utf-8') + salt, bcrypt.gensalt(12)) # Hash with Bcrypt, work factor 12, and salt appended
# Insert username and hashed_password into your database (using libraries like pymysql or SQLAlchemy)
# ...
Explanation:
- Salt Generation: Both examples generate a random 22-byte salt using
openssl_random_pseudo_bytes
(PHP) orsecrets.token_bytes
(Python). - Bcrypt Hashing: The password is appended with the salt before hashing with Bcrypt (using
password_hash
in PHP andbcrypt.hashpw
in Python). This is a crucial security practice. - Work Factor: A moderate work factor of 12 is used to balance security and performance. You can adjust this as needed.
- Database Interaction: The examples don't include the specific database insertion code, as it depends on your chosen library (mysqli, PDO, pymysql, SQLAlchemy, etc.). Ensure you bind parameters to prevent SQL injection vulnerabilities.
- Column Type: While not explicitly shown, the database table should have a
VARCHAR(255)
orVARBINARY(255)
column to store the Bcrypt hash (which can be up to 255 characters).
- Replace
mysqli_connect.php
ordb_connect.php
with your actual database connection details. - Adapt the database interaction code to your specific library and table structure.
- Always prioritize security best practices when handling passwords.
- Functions like MD5, SHA-1, and SHA-256 can be used for hashing, but they are not designed specifically for password storage.
- These functions are vulnerable to brute-force attacks as they are relatively fast to compute. Attackers can pre-compute rainbow tables to easily reverse these hashes.
User-Entered Hashes (Highly Insecure):
- This approach involves allowing users to create their own password hashes before storing them in the database.
- It's extremely insecure because users might choose weak passwords or use insecure hashing methods.
- The server wouldn't have control over the password strength or the hashing process.
Why Bcrypt is Superior:
- Bcrypt incorporates a work factor (cost) that significantly slows down the hashing process, making brute-force attacks computationally expensive.
- It's specifically designed to resist these attacks.
- The use of a random salt further strengthens the security by making pre-computed rainbow tables useless.
- Password Storage Best Practices:
- Enforce strong password policies (minimum length, complexity requirements).
- Consider using password managers that handle secure password generation and storage for users.
Conclusion:
- While there are technically alternative methods, Bcrypt is the clear winner for secure password storage in MySQL due to its design, work factor, and ability to mitigate brute-force attacks. It's the industry standard for a reason.
mysql hash types