Dealing with Incorrect string value error

[AndyB]

My add-on called Robots v1.5 works very well, but one admin receives the following error once in a while:

Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Incorrect string value: '\xA3\xA9' for column 'user_agent' at row 1 - library/Zend/Db/Statement/Mysqli.php:214

Here's the structure of the xf_robots table:



The SQL query to insert the user_agent data is this:

// run query
$db->query("
    INSERT INTO xf_robots
        (ip, user_agent, type, view_date)
    VALUES
        (?,?,?,?)
", array($ip, $userAgent, $type, $timestamp));

I assume I need to use some PHP code to clean the $userAgent variable, what should I use?

Thank you.

 

[Jarvis]

I would personally use datawriters any time you need to insert data into your database within XenForo.

Is user_agent just a string? What's the usual/expected format.

 

[Mike]

This happens if the string isn't valid UTF-8. If you're just pulling the user agent from the HTTP_USER_AGENT value, there's no guarantee of its encoding and I have no idea if there's a spec that says what encoding it should use.

I don't think I've directly used it, but I believe you may need to run the value through utf8_bad_replace() to remove invalid characters and then XenForo_Input::cleanString() to remove control characters/make it MySQL safe.

 

[AndyB]

If you're just pulling the user agent from the HTTP_USER_AGENT value

Yes exactly what I'm doing.

I added the following per your suggestion:

// decalare str
$str = $userAgent;

// replace bad bytes with an alternative character
$userAgent = utf8_bad_replace($str);	

// cleans invalid characters out of a string, such as nulls, nbsp, \r, etc
$userAgent = XenForo_Input::cleanString($userAgent);

One question, is it possible to do this instead?

// replace bad bytes with an alternative character
$userAgent = utf8_bad_replace($userAgent);	

// cleans invalid characters out of a string, such as nulls, nbsp, \r, etc
$userAgent = XenForo_Input::cleanString($userAgent);

Thank you.

 

[Mike]

I don't see an effective difference between the 2 options. $str has the same value as $userAgent so they're the same thing. The $str assignment doesn't do anything.

 

[AndyB]

I don't see an effective difference between the 2 options. $str has the same value as $userAgent so they're the same thing. The $str assignment doesn't do anything.

That's what I thought, but I wanted to make sure.

Thank you kindly, Mike.