#StackBounty: #php #html #laravel #laravel-blade #utf8mb4 HTML Special Characters and Emojis Showing up Incorrectly on Laravel Webpage

Bounty: 100

I am likely doing something silly and thought this would be pretty straightforward. I am pulling data from an external API and storing it right into the database via Eloquent model’s I am creating/saving.

The data saved into the database field looks like this:
I Can't Believe It's A – The field is using utf8mb4_unicode_ci collation.

My webpage has the following meta data:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">

And I am displaying it via my Laravel blade template like so:

<td>{{ $company->type->name }}</td>

I am a bit confused what I am doing wrong here? From the documentation and other stackoverflow questions I appear to be doing it correctly. My config/database.php has the following:

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => 'InnoDB',
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

I believe this is all correct. Is there something I am missing here? Switching to use the unescaped {!! !!} seems to result in the content still being displayed the same. I am using Laravel v7.13.0

Also Google Chrome reports it is UTF-8 rendered:

> document.characterSet
"UTF-8"

Edit:

Here is an example of getting the response and what the returned JSON name looks like:

>>> $response = Http::withOptions(
            [
                'verify' => false,
                'base_uri' => config('custom.torn_api_base'),
                'timeout' => 5.0
            ]
        )->get(
            "company/79831",
            [
                'selections' => 'profile',
                'key' => config('custom.torn_api_key')
            ]
        );
=> IlluminateHttpClientResponse {#3393
     +"cookies": GuzzleHttpCookieCookieJar {#3379},
     +"transferStats": GuzzleHttpTransferStats {#3418},
   }

>>> $response->json()['company']['name'];
=> "👾 Button Mashers™"

^ You can see above their API is giving me the same string with the UTF-8 encoding that would be used on their website.

Here is me creating and saving the model to the database:

            $company = Company::updateOrCreate(
                [
                    'id' => $tornPlayerData['job']['company_id']
                ],
                [
                    'name' => $tornPlayerData['job']['company_name'],
                    'player_id' => $this->player->id,
                    'isOwner' => true
                ]
            );

I also am logging the response and here is what one of the lines with an Emoji looks like:

Log::info("Updating company '{$company->name}'' now", ['company' => $company]);

laravel.log│[2020-06-22 00:43:52] staging.INFO: Updating company '👾 Button Mashers™'' now {"company":{"App\Company":{"id":79831,"name":"👾 Button Mashers™","player_id":2141091,"updated_at":"2020-06-22T04:43:52.000000Z","created_at":"2020-06-22T04:43:52.000000Z"}}}

Edit 2:
I just manually copied and pasted the Tinker output of 👾 Button Mashers™ to name in the associated database row. Now the website displays that manually adjusted one properly. So it seems Laravel is doing something weird to the data from the API when it is storing it and I am unsure why that would be.

Edit 3:

Using the HEX query as the user asked in the answer.

SELECT id,name,HEX(name) FROM `companies` WHERE id=60335

(60335, 'Pokey's Play House!', '506F6B657926233033393B7320506C617920486F75736521');

(60335, ''', '26233033393B');

The second result is with me modifying the Pokey's Play House! to just ' so you can see the result of just the apostrophe.

Edit 4:

The blade template:

<!DOCTYPE html>
<html lang="{{ str_replace('_', '-', app()->getLocale()) }}">
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <title>Company Directory</title>

        <!-- Fonts -->
{{--        <link href="https://fonts.googleapis.com/css?family=Nunito:200,600" rel="stylesheet">--}}

        <!-- Styles -->
          ** Snipped some minor CSS away from here **
        </style>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css">

        <!-- Scripts -->

        https://code.jquery.com/jquery-3.5.1.slim.min.js
        https://cdn.datatables.net/1.10.20/js/jquery.dataTables.js
        <script type="text/javascript" charset="utf8">
            **removed some datatables code from here for this paste**
        </script>

    </head>
    <body>
        <table id='directory-table' class='display'>
            <thead>
                <tr>
                    <th>Player Name</th>
                    <th>Company Name</th>
                    <th>Type</th>
                    <th>Rank</th>
                    <th>Positions</th>
                </tr>
            </thead>
            <tbody>
                @forelse ($companies as $company)
                    <tr>
                        <td>
                            <a href='https://www.example.
net/profiles.php?XID={{ $company->player->id }}'>
                                {{ $company->player->name }}
                            </a>
                        </td>
                        <td>
                            <a href='https://www.example.net/joblist.php#/p=corpinfo&userID={{ $company->id }}'>
                                {{ $company->name }}
                            </a>
                        </td>
                        <td>{{ $company->type->name }}</td> //THIS IS THE PROBLEM STRING HERE BEING OUTPUTTED
                        <td> {{ $company->rank }}</td>
                        <td> {{ $company->hired_employees }}/{{ $company->max_employees }}</td>
                    </tr>
                @empty
                    <tr>
                        <td></td>
                        <td></td>
                        <td></td>
                        <td></td>
                        <td></td>
                    </tr>
                @endforelse
            </tbody>
        </table>
    </body>
</html>


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.