> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Password Hashing in MySQL

> Why MySQL's built-in hash functions aren't safe for passwords, and how to store password hashes correctly using PBKDF2 with VillageSQL.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/mysql-8.4/0.0.4/quickstart">
  All examples in this guide work on VillageSQL. Install Now →
</Card>

MySQL has `MD5()` and `SHA2()`. Developers reach for them when hashing passwords. That's a mistake — those functions are designed to be fast, which is exactly the wrong property for password storage.

## Why Fast Hashes Are Wrong for Passwords

Cryptographic hash functions like MD5 and SHA-256 are optimized for speed. A modern GPU can compute billions of SHA-256 hashes per second. That's useful for checksums and data integrity. For passwords, it means an attacker who gets your hashed password table can brute-force it in hours.

Password hashing algorithms — bcrypt, Argon2, PBKDF2 — are intentionally slow. They're designed so that checking one password takes tens of milliseconds, which is fine for a login but makes brute-force attacks impractical.

```sql theme={null}
-- Don't do this
INSERT INTO users (email, password_hash)
VALUES ('user@example.com', SHA2('mypassword', 256));
```

## The Standard Answer: Hash in Application Code

Most MySQL applications handle this correctly by hashing in the application layer — using `bcrypt` in PHP, Python, Node.js, or whatever language runs the app. The database only ever sees the hashed value.

This works, but it creates a split: the hashing logic lives in application code while the data lives in the database. Migrations, bulk operations, and data imports have to route through the application to hash correctly, or they have to replicate hashing logic across multiple codebases.

## With VillageSQL: PBKDF2 Directly in SQL

VillageSQL's `vsql_crypto` extension adds `crypt()` and `gen_salt()` — a PBKDF2-based password hashing API modeled on PostgreSQL's pgcrypto. You can hash and verify passwords in SQL without routing through application code.

```sql theme={null}
INSTALL EXTENSION vsql_crypto;
```

### Storing a password

```sql theme={null}
CREATE TABLE users (
    user_id       INT PRIMARY KEY AUTO_INCREMENT,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
);

-- gen_salt generates a random salt; the second argument is iteration count
-- Higher iterations = slower hash = harder to brute-force
INSERT INTO users (email, password_hash)
VALUES ('user@example.com', crypt('mypassword', gen_salt('sha256', 10000)));
```

### Verifying a password

`crypt()` extracts the salt and parameters from the stored hash automatically — you pass the stored hash as the second argument:

```sql theme={null}
-- Returns TRUE if the password matches, FALSE otherwise
SELECT crypt('mypassword', password_hash) = password_hash AS is_valid
FROM users
WHERE email = 'user@example.com';
```

### Choosing iteration count

The iteration count controls how long the hash takes to compute. More iterations = slower = harder to brute-force, but also slower logins.

| Iterations | Approx. time per hash | Recommendation                                 |
| :--------- | :-------------------- | :--------------------------------------------- |
| 1,000      | \< 1ms                | Too fast — don't use for passwords             |
| 10,000     | \~5ms                 | Minimum for production                         |
| 100,000    | \~50ms                | Better — NIST recommendation for PBKDF2-SHA256 |
| 600,000    | \~300ms               | OWASP 2023 recommendation                      |

Start at 10,000 and increase as your hardware allows. Your login endpoint can tolerate 100ms; an attacker trying billions of guesses can't.

```sql theme={null}
-- More secure: 100,000 iterations
INSERT INTO users (email, password_hash)
VALUES ('user@example.com', crypt('mypassword', gen_salt('sha256', 100000)));
```

## When to Hash in SQL vs. Application Code

Hashing in SQL isn't always the right choice. Use it when:

* You're doing bulk inserts or migrations where routing through the app isn't practical
* You want the hashing logic centralized in the database layer
* You're building SQL-driven admin tools or seed scripts

Hash in application code when:

* Your application framework already has a well-tested bcrypt/Argon2 library
* You're using a language where the crypto ecosystem is mature (most are)
* You want to swap hashing algorithms without a schema migration

Both approaches produce secure hashes. The key is using a slow algorithm — PBKDF2, bcrypt, or Argon2 — not raw SHA2.

## Frequently Asked Questions

#### Is PBKDF2 as strong as bcrypt?

At equivalent iteration counts, bcrypt has a slight edge because it's memory-hard (harder to parallelize on GPUs). PBKDF2 at high iteration counts (100,000+) is still strong and widely deployed. The difference matters in adversarial scenarios; both are far better than SHA2.

#### Can I migrate existing SHA2 hashes?

Yes. Add a `needs_rehash` flag. On successful login, check if the hash is an old SHA2 format and rehash with `crypt()`. Remove old hashes once all active users have logged in.

#### Does the stored hash include the salt?

Yes. The output of `crypt()` encodes the algorithm, iteration count, salt, and hash in a single string. You don't manage the salt separately.

## Troubleshooting

| Problem                           | Solution                                                                                     |
| :-------------------------------- | :------------------------------------------------------------------------------------------- |
| `FUNCTION crypt does not exist`   | Run `INSTALL EXTENSION vsql_crypto`                                                          |
| Verification always returns FALSE | Make sure you're passing the stored hash as the second argument to `crypt()`, not a new salt |
| Hash output is NULL               | Check that neither argument to `crypt()` is NULL                                             |

## See also

* [Hashing Data in MySQL](/guides/hashing-data) — non-password hashing for integrity checks and deduplication
* [Symmetric Encryption in MySQL](/guides/symmetric-encryption) — when encryption (not hashing) is the right choice
* [Generating Random Data in MySQL](/guides/random-data-mysql) — generating cryptographically secure salts and tokens