Debugging why a mysql query failed

PHP
1

Hello, I have the following code:

$router->post('/v1/research', function() {
    header("Content-type: application/json");
    $cli_agent = htmlspecialchars($_POST["agent"]);
    $cli_locale = htmlspecialchars($_POST["locale"]);
    $cli_ref = htmlspecialchars($_POST["referrer"]);
    $cli_time = htmlspecialchars($_POST["time"]);

    $servername = $_ENV['MYSQL_SERVER'];
    $username = $_ENV["MYSQL_USERNAME"];
    $password = $_ENV["MYSQL_PASSWORD"];
    $dbname = $_ENV["MYSQL_DATABASE"];

    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $stmt = $conn->prepare("INSERT INTO analytics (`country`, `ref`, `agent`, `epoch`) VALUES (?, ?, ?, ?)");
    $stmt->bind_param("ssss", $cli_locale, $cli_ref, $cli_agent, $cli_time);
    
    die(json_encode(array("success" => "true", "message" => "OK"), true));
});

This query fails, but nothing is showing up in my error log related to this.

This is some details about the table I am using:

+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| country | varchar(30)  | NO   |     | NULL    |       |
| ref     | varchar(255) | NO   |     | NULL    |       |
| agent   | varchar(255) | NO   |     | NULL    |       |
| epoch   | varchar(40)  | NO   |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

How can I debug this code?

2

What exactly does “fails” mean? Do you have error reporting turned on?

3

I believe I do. The data is not inserted into the database.

4

You are not executing the query so of course it does not do anything.

htmlspecialchars is an output function, not input, so this whole block does not belong.

    $cli_agent = htmlspecialchars($_POST["agent"]);
    $cli_locale = htmlspecialchars($_POST["locale"]);
    $cli_ref = htmlspecialchars($_POST["referrer"]);
    $cli_time = htmlspecialchars($_POST["time"]);

Do not create variables for nothing. You already have the DB connection variables, just use them. The db connection does not belong where you have it. You will end up duplicating it countless times. Put it in a config file accessible to the files that would need it. Additionally, do not output internal system errors to the user.

5

Whether you htmlencode the data on the way in or out, makes no difference to the database. A string is a string. It’s processing the data to be stored.

I will grant you that it takes more space to store them encoded and may not be recommended, but there’s nothing to say that it does not belong.

I agree, but with an addition. This would appear to be an API reference, given that the positive outcome is a JSON string. What I would say is be consistent in your output; if your ‘good’ output is a json, your ‘bad’ output should be too; whatever’s calling your page is expecting JSON as the result, so don’t cause a failure in the fetching script as well.
Log the error somewhere (if it’s not already being logged) and report a descriptive-but-not-exposing negative outcome to the ‘user’.

6

True the database doesn’t care, but the concept naively assumes the data will always be used in an HTML output context so I stand by what I said. htmlspecialchars is an OUTPUT function ONLY for use in an HTML context which you don’t know what that will be UNTIL you output it. You don’t change data when you save it.

this function is sufficient to prepare input for inclusion in most contexts of an HTML document

https://www.php.net/manual/en/function.htmlspecialchars.php

2 Likes
7

Got the solution, thanks @benanamen. Was missing a $stmt->execute();.

1 Like
8

Not trying to beat a dead horse, but I agree. There’s that saying

Sanitize or validate on input, escape on output.

I don’t know why people put it that way, it should just be “validate on input, escape on output” since I rarely see people sanitize at all.

People tend to confuse this process a lot. It’s not really that complicated. When you validate, all you’re really doing is making sure the input has a set standard that you want. So for instance, a phone number. Do you think it’s appropriate to allow letters in your phone number? Not really. So that’s where you validate and make sure the structure of the input matches a legitimate phone number structure. If not, you give them an error message telling them to correct the input. You shouldn’t modify the users input at all. That’s why you let them do it themselves. That way, they know exactly what they put instead of guessing what they put.

1 Like
9

Mostly because sanitization by and large has been absolved by improved/proper handling of queries and retrieval (Parameterizing the query to sanitize against injection, output escaping rendering output safe). Everything else sort of falls under the Validation umbrella. The phrase simply predates the more common adoption of these improved practices.

3 Likes
10

@spaceshiptrooper, @m_hutley is exactly correct. It is a leftover relic from the olden days before we had cell phones and Prepared Statements. “Sanitation” was the way us old guys used to do things. I still have some old databases with jacked up data caused by sanitation.

1 Like
11

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.