Retrieving MySQL data stored with special characters fails using PHP

[Ant_426]

Hi. Please could someone help me understand what's happening here.
I have a MySQL database which has a table with some data in the following format:

2022-03-15 06:55:39:<br/>LineOfSight:<br/>java.lang.Exception:<br/> UNAVAILABLE TELEMETRY BR$0

The field collation is utf8 and the data is stored as text.

This data is provided via a 3rd party API so I have no control over input format. I simply want to echo that line of data but the page chokes and never loads when I run my query. The query runs fine directly in phpMySQL as well as in Toad for MySQL and MySQL workbench so it has to be a PHP parsing issue.

My code:

$serialnumber = 123456;
$sql = "SELECT rmAlarms.instruct,
       rmAlarms.technical
  FROM qpsdf_db1.rmAlarms rmAlarms
 WHERE (rmAlarms.serialnumber = '$serialnumber')";
            $database->setQuery($sql);
            $result = $database -> loadObjectList();            
            foreach ($result as $key => $results){
     $technical = htmlentities($results->technical);
     $instruct = htmlentities($results->instruct);
     echo $technical;?><br><?php
     echo $instruct;?><br><?php
            }

The field in question is rmAlarms.technical

If I comment out the relevant fields as below then the query works and the data is returned correctly.

$serialnumber = 123456;
    $sql = "SELECT rmAlarms.instruct,
           rmAlarms.technical
      FROM qpsdf_db1.rmAlarms rmAlarms
     WHERE (rmAlarms.serialnumber = '$serialnumber')";
                $database->setQuery($sql);
                $result = $database -> loadObjectList();            
                foreach ($result as $key => $results){
         //$technical = htmlentities($results->technical);
         $instruct = htmlentities($results->instruct);
         //echo $technical;?><br><?php
         echo $instruct;?><br><?php
                }

I have tried using:
$technical = htmlentities($results->technical);
As well as:
$technical = $results->technical;
both with the same results.

What am I missing? Any insight appreciated.

[Ant_426]

The output (using Toad for MySQL as it's an easier environment for me to work in) is:
instruct technical
The modem has not connected to us. Check the server port number. 2022-08-12 05:55:15:<br/>LLCConnection:<br/>:GPRS:<br/>java.lang.IllegalStateException:<br/> checkOut failed:<br/> 89354600 not connected [GPRS]

Just for clarity as the format gets messed up in this editor ...
instruct: The modem has not connected to us. Check the server port number.
technical: 2022-08-12 05:55:15:<br/>LLCConnection:<br/>:GPRS:<br/>java.lang.IllegalStateException:<br/> checkOut failed:<br/> 89354600 not connected [GPRS]

[Dani]

If php is just spitting out a blank page, that means there’s some fatal error. Check the error logs or change php error reporting to echo the error message. (Sorry, I’m on my phone so it’s difficult for me to type out the code, but something along the lines of ini_set).

[Ant_426]

Thanks, but I have checked all the normal, obvious things like error logs (there are no errors in the logs, neither server nor PHP logs).

I have set error reporting to: error_reporting(E_ALL); and no PHP errors or warnings are generated. Neither are there any clues in the browser dev console. This is what is so confusing.

The behaviour is that on loading, the browser loading spinner just spins until the page timeout is reached (90 secs in this case) and then the page generates a timeout error.

[Dani]

Oh I see. So the page is timing out.

I’m addition to setting the field collation, are you setting it on the php side as well for the MySqli connection?

[Ant_426]

The connection collation is set as follows:
Conn Collation
utf8mb4_general_ci

[Ant_426]

Mmm... It looks like I was barking up the wrong tree as it does not seem to be a parsing issue after all, but now I am even more confused.

If I use the serial number instead of the variable in the query like this:
$serialnumber = 123456;

$sql = "SELECT rmAlarms.instruct,
           rmAlarms.technical
      FROM qpsdf_db1.rmAlarms rmAlarms
     WHERE (rmAlarms.serialnumber = '123456')";
                $database->setQuery($sql);
                $result = $database -> loadObjectList();            
                foreach ($result as $key => $results){
         //$technical = htmlentities($results->technical);
         $instruct = htmlentities($results->instruct);
         //echo $technical;?><br><?php
         echo $instruct;?><br><?php
                }

Then the correct output is generated.
Output:
2022-08-15 09:51:39:<br/>LineOfSight:<br/>java.lang.Exception:<br/> UNAVAILABLE TELEMETRY BR$0
Check device declination.

If I echo out the serial number like this:

$sql = "SELECT rmAlarms.instruct,
       rmAlarms.technical
  FROM qpsdf_db1.rmAlarms rmAlarms
 WHERE (rmAlarms.serialnumber = '$serialnumber')";
            $database->setQuery($sql);
            $result = $database -> loadObjectList();            
            foreach ($result as $key => $results){
     $technical = htmlentities($results->technical);
     $instruct = htmlentities($results->instruct);
     echo $serialnumber;?><br><?php
     //echo $instruct;?><br><?php
            }

I get the correct output:
123456

But as soon as I use the serial number as a variable, then the query fails without any error messages or warnings.

[Ant_426]

Further to this.
echo mysqli_error(); produces no errors either.

[Ant_426]

And finally a solution.

The only way I could manipulate this string was with a regular expression.

$technical = preg_replace('/[^a-zA-Z0-9\s!?.,\'\"]/', '', $results->technical);

I will need to tweak the regex a bit to get the data in the format I need but after testing it appears to be a solid compromise to using the inbuilt PHP functions.

Thanks for everyone's help.

[gce517]

If I understood correctly from the series or replies above, you want to strip HTML tags from technical. Of course, htmlentities() would not work because it prints out the tags instead of parsing them.

Your regex looks like a good start, but the replacement you are using (empty '') will delete the tags, and you'll be left with no spacing between the content separated by the tags.

You could simplify your regex with (see https://www.regextester.com/93515):

<[^>]*>

As an alternative to the preg_replace() function, you could use the strip_tags() function:

$technical = strip_tags($results->technical);

[Dani]

Oh, it seems that that column of the database has some characters in the string that PHP is having a difficult time with.

I would use PHP's built-in filter_var() function.

https://www.php.net/manual/en/function.filter-var.php

https://www.php.net/manual/en/filter.filters.sanitize.php

Do you know what in the serial number specifically PHP is choking on? It's very odd that the page is just timing out. I've never seen that before.

But as soon as I use the serial number as a variable, then the query fails without any error messages or warnings.

Can you explain to me what you mean by as soon as you use the serial number as a variable? When you do echo $serialnumber; and get the correct output, you are using the serial number as a variable?

[Ant_426]

Thanks so much. I am not familiar with the filter_var function. I'll check it out.

Can you explain to me what you mean by as soon as you use the serial number as a variable?

Sorry - badly worded. What I meant was, when I use the serial number (e.g. 123456) instead of the variable ($serialnumber)

Yes, I have also not seen this timeout behaviour before either which is what made diagnosing this a bit of a challenge. I can only assume that there is some server configuration causing this but since I don't have direct access to the server I cannot confirm that. Nevertheless, my "hacked" solution is sufficient for now. I will get back to refactoring the code before going to production.

All the help appreciated.

[Keith_16]

Check out stripslashes(); and addslashes(); Both are PHP functions. Simple to use, you can see if escaping slashes is your problem in a few minutes.

I

[Dani]

Would you be able to do a MySQL dump of the offending rows so we can actually see what could be causing the issue? Instead of guessing what characters might be the problem.

No worries if you’re just going to stick to your workaround and table this for now.