PHP - MySQL - Java - Ensuring charset uniformity

Question

I am having an issue where I send some text from a java program to a PHP page, which uploads the text to a MySQL database, and I later retrieve the text in the java program from the MySQL database (through another PHP page). The issue is that I think the MySQL database isn't using the same charset (utf-8) as the java program and the PHP page.

When I send the string "This is a super—long dash" to the PHP page, it returns from the MySQL database as "This is a super—long dash".

I know the first PHP page is receiving the correct string, because I use "echo $_POST[xxx];" to make sure.

I also use the line:

"mysql:host=$servername;dbname=$dbname;charset=utf8"

When creating the PDO to connect to the MySQL database.

I have checked the MySQL database and the string there is "This is a superùlong dash" which leads me to believe it must be some problem with the MySQL, not the PHP or Java.

I tried to correct this by using the fixUTF8 function in the solution here: Detect encoding and make everything UTF-8 by Sebastián Grignoli. However, as a result I get "This is a super?long dash".

EDIT:

Here is my PHP code for uploading to MySQL:

<?php

$servername = "**********";
$username = "**********";
$password = "**********";
$dbname = "**********";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $conn->prepare("INSERT INTO authors (first_name, middle_name, last_name, blurb)
VALUES (?, ?, ?, ?)");

    $stmt->execute(array("$_POST[firstName]", "$_POST[middleName]", "$_POST[lastName]", "$_POST[blurb]"));

    echo "New author created successfully";
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

EDIT2:

Here is what SHOW CREATE TABLE authors provides:

Answer 1

When super—long comes out super—long, you have Mojibake.

This is the classic case of

• The bytes you have in the client are correctly encoded in utf8 (good).
• You connected with SET NAMES latin1 (or set_charset('latin1') or ...), probably by default. (It should have been utf8.)
• The column in the table was declared CHARACTER SET latin1. (Or possibly it was inherited from the table/database.) (It should have been utf8.)

The fix for the data is a "2-step ALTER".

ALTER TABLE Tbl MODIFY COLUMN col VARBINARY(...) ...;
ALTER TABLE Tbl MODIFY COLUMN col VARCHAR(...) ... CHARACTER SET utf8 ...;

where the lengths are big enough and the other "..." have whatever else (NOT NULL, etc) was already on the column.

Unfortunately, if you have a lot of columns to work with, it will take a lot of ALTERs. You can (should) MODIFY all the necessary columns to VARBINARY for a single table in a pair of ALTERs.

The fix for the code is to establish utf8 as the connection; this depends on the api used in PHP. The ALTERs will change the column definition.

How to avoid Junk/garbage characters while reading data from multiple languages? http://mysql.rjweb.org/doc.php/charcoll#how_mangling_happens_on_insert