Can't replace linebreaks in an mysql error string

Question

I try to catch erros from a prepared MySQL statement.

try {
    if ($stmt = $this->db->prepare($q)) {
    array_unshift($sqlData, $sqlTypes); // prepend the types
        if(call_user_func_array([$stmt, 'bind_param'], $sqlData)) {
            try {
                $stmt->execute();
                $r = $stmt->get_result();
                return $r;
            } catch (mysqli_sql_exception $e2) {
            // request error
            $err = error_get_last();
            $this->return['error'][] = $stmt->error;
            $this->return['error'][] = $err;
            $this->return['error'][] = $stmt->error_list;
            }
        } else {
            // sql statement bind error
            $this->return['error'][] = 'SQL bind error: ' . $this->db->error;
            $this->return['message'][] = 'endpoint: SQL bind Error';
        }
    } else {
        // sql statement error
        $this->return['error'][] = 'SQL statement Error: ' . $this->db->error;
        $this->return['message'][] = 'endpoint: SQL statement Error';
    }
} catch (mysqli_sql_exception $e1) {
    // sql query error
    $this->return['error'][] = $this->db->error;
    $this->return['message'][] = 'endpoint: Query error';
}

I add an error to the SQL statement to test the function. The $this->db->error in the catch part got it as a string. I send the whole $this->return via json_encode to my site and try to JSON.parse it. JS is triggering following error: Uncaught SyntaxError: missing ) after argument list.

The mysql error itself is:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM mytable LEFT JOIN `da...' at line 14

The json encoded string of this is:

["You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM \r\n mytable\r\n LEFT JOIN `da...' at line 14"]

So the php json_encode fails. It seems, that the problem are the linebreaks in the mysql error text.

Pretty easy to solve? Nope.
str_replace('\r\n', '', $err) - fails
nl2br - fails

still '\r\n' inside of the string. nl2br does its job and put some br into position, but '\r\n' follows that html linebreak

I thougt about wrong character encoding or so, but the string is ASCII - which seems to be part of UTF-8

I have no idea why I cant remove the linebreaks in the json encoded string. I can't reproduce that with any other string except the one coming from the mysql error.

Answer 1

Try it like this: str_replace(["\r\n", "\n", "\r"], ' ', $err);

And otherwise check your string with this: echo bin2hex($err);

what is the return?