Laravel exception: 1366 Incorrect string value: '\\xC0\\xA7\\xC0\\xA2%2...' for column 'user_agent'

Question

Occasionally I will get the following exception thrown from Laravel:

"class": "Illuminate\\Database\\QueryException",
    "message": "SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\\xC0\\xA7\\xC0\\xA2%2...' for column 'user_agent' at row 1 (SQL: update `sessions` set `payload` = testpayload, `last_activity` = 1663058232, `user_id` = ?, `ip_address` = ipAddress, `user_agent` = 1 ����%2527%2522 where `id` = blahblah)"

From what I have read, it most likely is that my table is not using the correct encoding and maybe a special character (something like Ö) is being used inside the user_agent from someone viewing the site and therefore it throws an error.

But checking that, I assume if I have the default Laravel setup, how others have not run into the same issue. Checking my setup:

config/database.php

            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',

^ and confirming in the db itself, this is what I am using.

I then suspected maybe when I upgraded from Laravel 5.x > 7.x maybe a migration changed or something and I should be using a different charset. But checking migrations and also the db config for version 7, nothing has changed.

Laravel Version: 7.30.4

Mysql Version: 5.7.33-log

Anyone seen this before or point me in the right direction?

What is the actual encoding on the DB column? You can get it with SQL commands but it's easier to just check the table definition with your favourite MySQL utility. — Álvaro González, Sep 13 '22 at 09:50
`user_agent` is ----> Encoding: `UTF-8 Unicode (utf8mb4)`. Collation: `utf8mb4_unicode_ci`. Which is what the table is set as — Oli Girling, Sep 13 '22 at 09:58
That end should be fine then. I presume data comes from `User-Agent` HTTP header. Unfortunately, it often comes as Latin-1 and a conversion is needed, and there's no guarantee of any given encoding. — Álvaro González, Sep 13 '22 at 10:01

Wander Nauta · Accepted Answer · 2022-09-13T10:27:02.153

That garbage string is not a legitimate User-Agent; it looks like it's not even valid UTF-8 but instead some kind of escaped form of an over-long encoding, which is invalid:

select convert(x'C0A7C0A2' using utf8);
(null)

The %2527%2522 part, meanwhile, is a double URL-encoded version of %27%22 which is an URL-encoded version of "". It is also garbage.

The request from your example is likely from some robot or penetration testing tool trying to find vulnerabilities, something like sqlmap, which can use tricks like these (here, here) to try to avoid filters. It is not from a person. If you have other requests with the same issue, or with different strange-looking User-Agents, those are probably other attempts by the same tool.

You may just want to block requests from this IP altogether.

Thanks for the helpful reply. This is good and bad news so thanks for that. The IP's do seem to change on each attempt, so guess someone is trying to be a little naughty. Didnt know about sqlmap, so just cloned it and going to bash it at my local instance. Thanks again. — Oli Girling, Sep 13 '22 at 10:37

Laravel exception: 1366 Incorrect string value: '\\xC0\\xA7\\xC0\\xA2%2...' for column 'user_agent'

1 Answers1