Data not reaching database after arriving to php endpoint

[ranger2022]

Hello,

Here's my problem, I have a device that sends a post request to a php endpoint. The php code takes that data and puts it into a MySQL database, but the data does not always end up in the database. I have about 100 of these devices posting to this endpoint running on a server in plesk. For a majority of devices, the code seems to work fine the vast majority of the time. Some devices however have much of they're data missing. It is very important that all of the data the device successfully sends out is recorded in the database. I've verified that this is not a device issue i.e. a post request is made and a 200 OK response is received but even then the data doesn't show up in the database so I've narrowed down the search to the issue being between the data base and the php code that sends the data to the database, and that is where I'm stuck.

I'm new to this system and am unfortunately unfamiliar with php and MySQL databases. Nonetheless I've been tasked with fixing this issue. Unfortunately I don't think I can share any of the php code so I'm really just reaching out into the void here. But has anyone ever dealt with an issue like this? If you can give me literally any information or things to look for I would really appreciate it. I'm learning php and MySQL while I'm trying to fix this issue so my knowledge is very limited.

Please help me lol Thanks.

[requinix]

Not being able to share any code is going to be a problem. Surely you can post a few lines from some select locations?

The most likely culprit is an improper SQL statement that let the data somehow corrupt the query, causing it to fail. For example, if it contains apostrophes and quotation marks.

Knowing what data is not being inserted would be nice so that you could look for a pattern in what does and doesn't work.

[mac_gyver]

when data insertion fails occasionally, it's usually because of sql special characters in a value breaking the sql query syntax, data containing duplicate values that violate a unique index or other constraint, or data that is out of range for a column definition. these conditions would produce query errors, so, the question becomes does the php code have error handling for all the database statements that can fail - connection, query, prepare, and execute that would be logging the errors to the php error log or is the mysql general query log enabled (it's not by default)?

 

[ranger2022]

1 hour ago, requinix said:

Not being able to share any code is going to be a problem. Surely you can post a few lines from some select locations?

The most likely culprit is an improper SQL statement that let the data somehow corrupt the query, causing it to fail. For example, if it contains apostrophes and quotation marks.

Knowing what data is not being inserted would be nice so that you could look for a pattern in what does and doesn't work.

I will have to check if I am allowed to share any and I'll post as soon as I know. Thank you!

[ranger2022]

1 hour ago, mac_gyver said:

when data insertion fails occasionally, it's usually because of sql special characters in a value breaking the sql query syntax, data containing duplicate values that violate a unique index or other constraint, or data that is out of range for a column definition. these conditions would produce query errors, so, the question becomes does the php code have error handling for all the database statements that can fail - connection, query, prepare, and execute that would be logging the errors to the php error log or is the mysql general query log enabled (it's not by default)?

 

I'll look for that, based on what I've worked on so far this definitely seems like a possibility. Thank you!

[ranger2022]

12 hours ago, requinix said:

Not being able to share any code is going to be a problem. Surely you can post a few lines from some select locations?

The most likely culprit is an improper SQL statement that let the data somehow corrupt the query, causing it to fail. For example, if it contains apostrophes and quotation marks.

Knowing what data is not being inserted would be nice so that you could look for a pattern in what does and doesn't work.

I was approved to share some code! Here is where it looks like data is inserted into the database.

 

[requinix]

Looks like a prepared statement, which is good and would avoid the SQL problems.

Have you turned on whatever "debug" support this class offers? Checked error logs?

[ranger2022]

The error_log file on the server is empty so I'm not sure if anything is actually being reported. Honestly I'm not sure if error logging is on or how to turn it on. Unfortunately I'm new to all of this and the person who build all this stuff is gone.

[mac_gyver]

if the PDO error mode IS set to use exceptions, the $this->_debugOn code won't have any effect, since it won't ever get executed upon an error. if the PDO error mode is not set to use exceptions, the $this->_debugOn code will be executed, but it is then up to the application code to actually test and use those class properties. since this code apparently doesn't have a user interface, the $this->_debugOn code was probably only ever there for direct requests during developmental testing.

for the the try/catch code to 'work' the PDO error mode must be set to use exceptions. there would be something in the  database connection code using PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION if this setting is on.

without investigating (logging) what the actual sql syntax being built is and what is being supplied to the ->execute(...) call, there's no guarantee that this is an actual prepared query, and if an emulated prepared query is being used, sql special characters in a value can still break the sql query syntax if the character set that php is using is not the same as the character set of the database tables.

posting the database connection code, less any connection credentials, would show the error mode setting, emulated prepared query setting, and any character set setting.

[mac_gyver]

as a separate post. this code is typical misuse of OOP. someone took a few lines of main code, building and executing an insert query, threw a class definition around it, then to make it 'work' had to add $this-> in front of everything, add a bunch more code to build the input properties, code to instantiate the class, code to call the various methods, code (if any) to test the returned values, and code (if any) to use the output properties.

if this method gets called and there isn't a database connection (what the first few lines of code are testing), it means that the connection code doesn't have error handling that would have stopped the whole database dependent application from being executed upon a connection error, and for the rare cases where a connection was successfully made, but lost during execution of the code, the fatal php error that would have occurred when the non-existent connection was used should be getting logged to let you know what type of problems are occurring. edit: since the code is just returning in this case, and it's doubtful that the returned value is causing any sort of logging/reporting to occur, you will never know if this is the cause of the missing inserted data.

this brings up an additional point for debugging the problem. php's error_reporting should be set to E_ALL and log_errors should be set to ON, preferably in the php.ini on the system, so that all php errors will get reported and logged. if the php/web server error log is empty, either this is not the case or the log file settings are not setup.

Edited July 1, 2022 by mac_gyver

[ranger2022]

I'll see if I can figure out how to get proper error logging going. I have a suspicion that what you said about the lack of error handling for connection failure is a likely culprit based on the other things I've worked on from the individual who created this monstrosity. Thank you!

[mac_gyver]

most database errors are fatal problems, due to programming mistakes, or due to data issues like have been listed in this thread. since there's no user interaction with this code, there's no case where a database statement error is recoverable by the non-existent user. therefore, all database statement errors should simply get logged.

the simplest way of doing this, which requires no code in the application (the current catch logic, if it is being executed at all, is only logging the error message, it does not include the filename and line number where the error occurred, and it is allowing execution to continue when a fatal problem has occurred) is to use exceptions for database statement errors and simply let php catch the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.)

the PDO connection already always uses exceptions for errors, but if there's tri/catch code present that isn't properly dealing with a connection error, it's not helping, and should be removed anyways. setting the PDO error mode to use exceptions, applies to all the rest of the statements - query, prepare, and execute. after insuring that this is being set, for what this application is for, any try/catch code should be removed so that php will catch the database exceptions.

Edited July 1, 2022 by mac_gyver

[benanamen]

Just an FYI: Pretty pictures are nice and all but we cant paste an image into our editors. In the future, please post actual code using the code tags on the post menu <>. 😃