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:

  1. CHAR(60) (or BINARY(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.
  2. VARCHAR(255) (or VARBINARY(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) or secrets.token_bytes (Python).
• Bcrypt Hashing: The password is appended with the salt before hashing with Bcrypt (using password_hash in PHP and bcrypt.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) or VARBINARY(255) column to store the Bcrypt hash (which can be up to 255 characters).

• Replace mysqli_connect.php or db_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.