MySQL数据库密码存取异常：加密入库后无法解密排查及安全方案咨询

Hey there! Let's break down your problem into two parts: solving the NULL decryption result first, then exploring more secure ways to store retrievable passwords for your internal system.

First: Troubleshooting the NULL Decryption #

Your encrypted string is the same length when pulled from the database, so truncation isn't the issue. Here are the most likely culprits and fixes:

1. Inconsistent Encryption Keys #

The #1 reason for openssl decryption failing is a mismatch between the encryption and decryption keys. Let's verify this first:

• Add var_dump(ENCRYPTION_KEY); right after defining it in both your encryption and decryption flows, then compare the two outputs exactly.
• Common mismatches happen if:
  • Your school or location values have hidden whitespace (like trailing spaces) when pulled from the database vs. when you encrypted the password.
  • You used HTML entities like & in your key string instead of raw & — PHP treats & as literal characters, so if you intended to use &, replace all & with & in your key template.
  • Case sensitivity: If your database stores school as "Harvard" but you accidentally read it as "harvard" later, the key will differ.

2. Encrypted String Corruption During Storage/Retrieval #

Even if the length matches, special characters in the encrypted string (like +, /, or = from base64 encoding) might get mangled by database escaping:

• Use parameterized queries (PDO or MySQLi prepared statements) when inserting and fetching the encrypted password. This avoids automatic escaping of special characters that could break the ciphertext.
• Double-check that your Openssl_EncryptDecrypt class properly encodes the raw binary ciphertext to a safe string (like base64) before returning it. If it outputs raw bytes, storing that in a VARCHAR field can cause invisible character corruption.

3. Missing IV Handling #

Most openssl encryption implementations require a random Initialization Vector (IV) to work correctly. If your class generates an IV during encryption but doesn't store it alongside the ciphertext, decryption will fail (since it uses a random IV by default):

• Check the class code you referenced — if it includes an IV in the encrypted output (e.g., concatenates IV + ciphertext and base64 encodes the whole thing), make sure you're storing the full output without modification.
• If the class doesn't handle IV storage, update it to include the IV with the ciphertext (use a safe separator like :: that won't appear in base64) so you can split it out during decryption.

Second: More Secure Retrievable Password Storage #

Since you need users to view passwords, one-way hashes (like bcrypt) aren't an option. Here's how to harden your symmetric encryption approach:

1. Use a Key Derivation Function (KDF) #

Don't directly concatenate school + location + dynamic 64-string as your key. Instead, use a KDF like Argon2 or PBKDF2 to generate a strong, salted key:

// Generate a random salt (store this with the encrypted password in the database)
$salt = random_bytes(16);
// Combine your inputs into a single seed
$seed = $school . $location . $dynamic_64_string;
// Derive a 32-byte AES-256 key
$encryption_key = hash_pbkdf2('sha256', $seed, $salt, 100000, 32);

This makes brute-force attacks on the key exponentially harder, even if parts of your seed are exposed.

2. Switch to Authenticated Encryption #

Use AES-GCM instead of CBC mode. GCM adds integrity checking, so you'll immediately know if the ciphertext was tampered with, and it avoids padding oracle attacks. Update your Openssl_EncryptDecrypt class to use aes-256-gcm as the cipher.

3. Secure the Dynamic 64-String #

• Always transmit the dynamic 64-string over HTTPS to prevent man-in-the-middle theft.
• Never log this string anywhere — even in server logs.
• Add short-lived session validation: Only allow the string to be used for a single decryption request, or expire it after 5 minutes.

4. Limit Decryption Permissions #

• Beyond IP restrictions, add role-based access control (RBAC) — only users with explicit "password view" permissions can trigger decryption.
• Log every decryption attempt: Record the user ID, timestamp, and which password was accessed. This helps detect misuse.

5. Avoid Plaintext in Memory #

After decrypting the password, use it immediately then overwrite the variable to clear it from memory:

// Use the decrypted password
echo $decrypted;
// Clear it from memory
$decrypted = null;
unset($decrypted);

6. Consider a Key Management Service (KMS) #

If your infrastructure allows, use a cloud KMS (like AWS KMS, Azure Key Vault) to handle key storage. You can encrypt your data key with the KMS master key, store the encrypted data key in your database, and only retrieve the plaintext data key when needed for decryption. This eliminates the risk of your key being exposed in code or server memory.

内容的提问来源于stack exchange，提问作者Rocket