Debugging why a mysql query failed

[RiversideRocks]

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?

[benanamen]

You are not executing the query.

[RiversideRocks]

Oh my god you are right, I feel like such an idiot. Its missing a $stmt->execute();

[benanamen]

Just to sync what I told you in the other forum…

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.

[RiversideRocks]

Good points you made, I always prefer to sanitize input before it goes into the database, but if that isn’t effective I’ll take note.