MySQL查询有值但HTML页面显示NULL，含加密字段SQL问题排查

Hey there, let's break down why your decrypted patient_name_en2 field shows up as NULL in your HTML page even though the query returns valid results directly in MySQL. Here are the key areas to investigate:

1. Encryption/Decryption Key Mismatch #

This is the most common culprit when AES_DECRYPT returns NULL:

• Double-check that the :encKey parameter passed from your application to MySQL matches exactly what you used during testing in the MySQL client. Even tiny differences (extra spaces, case sensitivity, or encoding discrepancies) will cause decryption to fail.
• Verify the AES mode and padding used during encryption. MySQL's AES_DECRYPT defaults to AES-128 ECB mode (note: ECB is insecure for production use!). If you encrypted the data with a different mode (like CBC) or padding scheme, decryption will return NULL.

2. Character Set Misconfiguration #

Your query uses CONVERT(aes_decrypt(patient_name_en, :encKey) using utf8mb4)—make sure all layers handle character sets correctly:

• Confirm the original encrypted patient_name_en field was stored using a compatible character set (preferably utf8mb4). If the original string was encoded in a different charset before encryption, converting to utf8mb4 post-decryption might corrupt the value.
• Ensure your application's database connection uses utf8mb4. For example:
  • In Java (JDBC): Add characterEncoding=utf8mb4&useUnicode=true to your connection URL.
  • In PHP: Call mysqli_set_charset($connection, 'utf8mb4') after connecting.
    Without this, decrypted strings can get mangled during transit from MySQL to your app, appearing as NULL.

3. Parameter Binding Errors in Application Code #

Incorrect parameter handling can break decryption:

• If your encryption key is a binary value, make sure your app passes it as a byte array, not a string. For example, in Java use preparedStatement.setBytes() instead of setString() for :encKey.
• Check for accidental type conversions in your code (e.g., trimming the key, converting it to uppercase/lowercase unintentionally).

4. Source Data NULLs #

It’s easy to miss:

• Some rows in your patient table might have a NULL patient_name_en value. When you test the query in MySQL, you might be looking at rows with valid encrypted data, but your HTML page displays all rows—including those with NULL source values (which decrypt to NULL).
• Add a WHERE patient.patient_name_en IS NOT NULL clause to your query temporarily to see if the HTML displays valid values for those rows.

5. Application/HTML Rendering Issues #

Sometimes the decrypted value exists in the app but fails to render in HTML:

• Print the query result set directly in your application code (right after executing the query) to confirm patient_name_en2 has a valid value. If it’s non-NULL here, the problem is in how the data is passed to your HTML template.
• Check your HTML template for typos: Ensure you’re referencing the correct alias (patient_name_en2, not patient_name_en).
• Verify your app isn’t accidentally converting empty strings to NULL (or vice versa) when passing data to the template.

6. Incomplete Query in Application #

Your provided query is truncated at the subquery (SELECT sum(consultation_med.given_quantity) FROM med_pharmacy LEFT JOIN medication ON med_pharmacy.me...—make sure the full query used in your application matches exactly what you tested in MySQL. A missing join condition or syntax error could lead to unexpected NULLs, though this is less likely to affect only the decrypted field.

Quick Debugging Checklist #

1. Print the :encKey value in your app (temporarily, for debugging only!) and compare to the key used in MySQL.
2. Log the full query executed by your app (with bound parameters) and run it directly in MySQL to see if it returns NULL.
3. Check the raw result set in your app before it reaches the HTML template.

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