Dealing with Incorrect string value error

AndyB

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

Code:
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:

upload_2017-1-5_9-28-43.png

The SQL query to insert the user_agent data is this:

PHP:
// 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.
 
Last edited:
#2
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

XenForo developer
Staff member
#3
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

Well-known member
#4
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:

PHP:
// 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?

PHP:
// 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

XenForo developer
Staff member
#5
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

Well-known member
#6
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.
 
Top