Trace and fix UTF-8 corruption across PHP and MySQL

A customer submits José, القاهرة, or an emoji. The browser looks correct, but a later page shows José, question marks, or replacement diamonds. “Set everything to UTF-8” is directionally right and operationally incomplete: corruption happens at a boundary where bytes are decoded using the wrong character set, rejected by a column, or encoded twice.

Classify the symptom before migrating anything

Symptom	Likely mechanism
José	UTF-8 bytes decoded as Windows-1252/Latin-1, then re-encoded
?	Lossy conversion or unsupported character at write time
�	Decoder encountered invalid byte sequence
Emoji rejected	MySQL utf8mb3, old schema, or wrong connection character set
Correct storage, broken page	HTTP/HTML declaration or output transformation mismatch

Do not run a blanket conversion until you know whether stored bytes are valid UTF-8, mojibake text, or irreversibly replaced characters. The same visual symptom can require opposite repairs.

Trace one probe through every boundary

José · القاهرة · 北京科技有限公司 · 🙂

Use a synthetic record, never a customer record, and record:

1. the UTF-8 bytes sent by the browser;
2. PHP’s received bytes and validation result;
3. the database connection character-set variables;
4. column character set and collation;
5. stored bytes via HEX();
6. response Content-Type and browser rendering.

SELECT
  @@character_set_client,
  @@character_set_connection,
  @@character_set_results,
  @@collation_connection;

SELECT name, HEX(name), CHAR_LENGTH(name), LENGTH(name)
FROM encoding_probe;

CHAR_LENGTH counts characters; LENGTH counts bytes. Their difference is normal for multibyte text and useful when checking what the server stored.

Make MySQL’s character set unambiguous

Use utf8mb4. Current MySQL documentation deprecates utf8mb3 and the ambiguous utf8 alias. utf8mb4 stores one-to-four-byte UTF-8, including supplementary characters such as most emoji.

SHOW CREATE TABLE customer;
SHOW FULL COLUMNS FROM customer;

A database default does not rewrite existing columns. For a new table:

CREATE TABLE customer (
  id BIGINT UNSIGNED PRIMARY KEY,
  display_name VARCHAR(200) NOT NULL
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Choose collation from comparison requirements. Accent-insensitive, case-insensitive, language-specific, and binary comparisons are different business rules; do not copy a collation merely because it contains utf8mb4.

Set the driver connection, not an ad hoc query

$pdo = new PDO(
    'mysql:host=db;dbname=app;charset=utf8mb4',
    $user,
    $password,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

The accepted answer correctly prefers the driver’s character-set mechanism. It synchronizes driver and server expectations. Avoid sprinkling SET NAMES through request code; missed paths and connection pools create intermittent corruption.

Declare and validate the HTTP boundary

header('Content-Type: text/html; charset=utf-8');

<meta charset="utf-8">

The HTTP header is authoritative for an HTTP response; the early HTML declaration remains useful for saved or transformed documents. Save PHP, templates, JavaScript, translations, and fixtures as UTF-8 without an accidental byte-order mark before PHP headers.

For JSON, use the correct media type and fail on invalid data:

header('Content-Type: application/json; charset=utf-8');
echo json_encode(
    $payload,
    JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR
);

JSON_UNESCAPED_UNICODE changes presentation, not correctness. A compliant JSON parser understands escapes. JSON_THROW_ON_ERROR prevents a silent false result from masquerading as a valid response.

Validate input without guessing its encoding

if (!mb_check_encoding($name, 'UTF-8')) {
    http_response_code(400);
    throw new InvalidArgumentException('display_name must be valid UTF-8');
}

An attacker is not bound by the page’s declared charset. Validate external text at the boundary. Do not “repair” arbitrary bytes with encoding detection: detection is heuristic, and converting from the wrong guessed source can create durable corruption.

Use multibyte-aware operations where characters matter

mb_internal_encoding('UTF-8');

if (mb_strlen($name, 'UTF-8') > 200) {
    throw new LengthException('display_name is too long');
}

PHP strings are byte sequences. strlen is correct for byte limits and protocol framing; mb_strlen is appropriate for a character-oriented product rule. Grapheme clusters can contain multiple code points, so user-perceived length may require grapheme_strlen from the Intl extension.

Migrate schema without destroying evidence

1. Take and restore-test a backup.
2. Inventory server, database, table, column, connection, and application settings.
3. Sample HEX() values and classify valid UTF-8, mojibake, and replacement loss.
4. Test conversion on a restored copy with production schema and indexes.
5. Estimate lock, rebuild, replication-lag, disk, and index-size impact.
6. Convert in a controlled window or use a reviewed online-schema method.
7. Verify bytes and application behavior before removing the rollback path.

ALTER TABLE customer
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE ... DEFAULT CHARACTER SET changes defaults for future columns; it does not convert all existing text columns. CONVERT TO can rebuild the table, change column definitions, increase index bytes, and alter comparison behavior. Review the exact plan on the deployed MySQL version.

Repair only data whose original bytes are recoverable

If valid UTF-8 bytes were stored in a Latin-1 column, a controlled byte-preserving conversion may recover them. If characters were already replaced with ?, the original information is gone and must come from a trusted source or backup. Never run a double-conversion recipe across an unclassified column.

Create a shadow column or table, transform a sample, compare expected Unicode code points and business keys, then batch with checkpoints. Preserve the original bytes until acceptance. Stop if unique constraints collide under the new collation; that is a business-data conflict, not merely a migration nuisance.

Production failure modes

• A worker or CLI path uses a different DSN from web requests.
• A replica, export, CSV tool, message queue, or search index decodes bytes differently.
• A reverse proxy injects or rewrites Content-Type.
• Normalization differences make visually identical strings compare differently.
• A collation change merges values previously considered unique.
• A four-byte character enlarges an indexed value beyond an old schema limit.

Verification and rollback

SELECT display_name, HEX(display_name)
FROM customer
WHERE id = :probe_id;

Verify create, read, update, search, sort, export, import, API serialization, background jobs, logs, and backups with accented Latin, right-to-left text, CJK, combining marks, and supplementary characters. Check replicas and restored backups, not only the primary.

Rollback the application DSN only if the old schema can represent every new write. Once four-byte characters are accepted, downgrading to utf8mb3 can lose data. During a staged migration, dual-read or maintenance-mode strategies may be safer than attempting an in-place reversal. If validation fails, stop writes, preserve original and transformed bytes, restore the tested backup or switch back to the untouched table, then reconcile writes captured during the window.

Encoding is a contract at every boundary. Fix the first boundary that changes the bytes, then prove the contract end to end.

Sources: the Stack Overflow question by mercutio and accepted answer by chazomaticus (CC BY-SA), the current PHP manuals for PDO connections, HTTP headers, and mbstring encoding, the MySQL references for Unicode character sets and connection character sets, and the WHATWG HTML encoding declaration.