PDO + MySQL and broken UTF-8 encoding
Seeing Arabic text or other non-Latin scripts render as ????????? in your application is a classic symptom of an encoding mismatch between your PHP script, your connection string, and your database server. When you report that inserting UTF-8 data into a MySQL database using PDO results in garbled characters despite running SET NAMES utf8, the issue almost always lies in how the client libraries interpret the incoming byte stream versus how the server expects to receive it.
The standard approach involves establishing a connection, then immediately setting the character set on both sides of the connection. In your current implementation, you are creating the PDO instance with DSN parameters and then issuing two distinct queries: SET NAMES utf8 and SET CHARACTER SET utf8. While these commands are valid MySQL syntax, they do not always guarantee the intended behavior when mixed together or when the underlying client library defaults to a specific binary mode.
When you execute SET NAMES 'utf8', MySQL updates its internal character set variables for both the client and the server. The SET CHARACTER SET command is actually deprecated in favor of SET NAMES, but running both does not inherently cause corruption; rather, it suggests that the initial handshake might be failing before these commands take effect. If your PHP environment defaults to ISO-8859-1 or if the connection string hints at a different charset (like charset=utf8 in an older MySQL connector), the bytes representing Arabic characters are interpreted as Latin-1 symbols upon arrival, resulting in the replacement characters you see.
The most robust solution is to ensure that the PDO driver explicitly uses UTF-8 without relying on runtime SET NAMES queries that might be skipped or misordered by the driver. Instead of constructing the connection string with a charset parameter and then sending SQL commands, you should configure the PDO attributes directly after instantiation. This forces the driver to handle the conversion correctly before any SQL is executed.
You can achieve this by setting the PDO::MYSQL_ATTR_INIT_COMMAND attribute or, more specifically for PDO MySQL, ensuring that the connection options include PDO::MYSQL_ATTR_USE_BUFFERED_QUERY if applicable, but the critical step is often simply passing the charset in the DSN and avoiding the deprecated SET CHARACTER SET. However, a more modern and reliable pattern involves setting the charset via an attribute on the connection object immediately after creation.
Consider this corrected implementation where you explicitly set the character set using the PDO::MYSQL_ATTR_INIT_COMMAND attribute or by setting the charset via a prepared statement configuration that overrides defaults:
$dsn = sprintf(
'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4',
confitem('database', 'host'),
confitem('database', 'port'),
confitem('database', 'name')
);
$options = array(
PDO::ATTR_PERSISTENT => confitem('database', 'pconnect'),
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
);
$dbh = new PDO($dsn, confitem('database', 'username'), confitem('database', 'password'), $options);
By including charset=utf8mb4 in the DSN and using SET NAMES utf8mb4, you ensure compatibility with four-byte characters like emoji or complex Arabic ligatures. Using utf8mb4 is strictly preferred over utf8 because standard MySQL utf8 only supports three bytes per character, which can truncate multi-byte sequences found in modern text.
The workaround of using json_encode and json_decode effectively sidesteps the encoding issue by converting text to a byte-for-byte safe ASCII-compatible format before storage. This works well for specific data types but introduces significant overhead: increased memory usage during serialization, slower insert speeds due to JSON parsing, and potential data structure changes if your schema expects native strings. It is generally better to fix the root causeâencoding negotiationârather than masking the symptom with serialization.
If you are using Laravel or a similar framework, ensure that your database migration files define columns with utf8mb4 collation and that your environment variables do not override these settings with legacy charset parameters. For high-performance applications handling heavy international content, avoiding JSON serialization for text fields is critical to maintaining throughput and keeping the schema simple.