PHP file upload with japanese filename turns into corrupted filename in DB

Question

I have a REST API endpoint that lets you upload a file to the server. When I save a file that is named using the latin alphabet there is no issue. But when I try to save a file that has a japanese character, the file is saved and the filename in the server is okay but when I look into the database the filename is not right.

The result in my DB when saving the file:

But when I look at the server's shell the filename is correct:

I tried changing the database collation into:

utf8mb4_unicode_ci
utf8
utf8_general_ci ...but the issue still persists.

Update: This is my query to insert the data:

public function setFileQuery($param,$file){
  $this->module_id = $param['moduleid'];
  $this->file_name = $file['name'];
  $this->file_size = $file['size'];

  $q = "INSERT INTO data_file
       (module_id,file_name,file_size,start_time,end_time,elapse_time,through_put)
       VALUES
       (?,?,?,?,?,?,?)";
            
  $insertStmt = $this->conn->prepare($q);
  $insertStmt->execute([
    $this->module_id,
    $this->file_name,
    $this->file_size,
    null,
    null,
    null,
    null,
  ]);
            
  Responses::http_ok();
}

user_11854986 · Answer

First, the table collation should be set utf8mb4.
Second, make sure that the data field (aka column) collations are also utf8mb4.

Now you may insert data (e.g. thru phpMyAdmin or thru any PHP script):

phpMyAdmin:

PHP:

If you are using PDO then make sure you have set the charset in your connection, such as:
```
$conn = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass);
```

If you are using MySQLi:

$conn = mysqli_connect($servername, $username, $password, $dbname);

/* change character set to utf8mb4 */
mysqli_set_charset($conn,"utf8mb4");
$mysqli->query("set names utf8mb4");

...and then executing the query, i.e.:

mysqli_query( "INSERT INTO song( songname ) VALUES( 'あああ.txt' )" );

The result will be as wanted:

PHP file upload with japanese filename turns into corrupted filename in DB

1 Answers