HTTP POST Updates all rows

Question

I am having a project in which I am sending data from ESP32 to a MySQL Database. I am using a PHP script that fetches the POST and updates the data in the database. The script is the following:

<?php

$servername = "localhost";

// REPLACE with your Database name
$dbname = "licenta";
// REPLACE with Database user
$username = "admin";
// REPLACE with Database user password
$password = "mihnea";

// Keep this API Key value to be compatible with the ESP32 code provided in the project page$
// If you change this value, the ESP32 sketch needs to match
$api_key_value = "tPmAT5Ab3j7F9";

$api_key= $spot = $distance = $vacancy = "";

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $api_key = test_input($_POST["api_key"]);
    if($api_key == $api_key_value) {
        $spot = test_input($_POST["spot"]);
        $distance = test_input($_POST["distance"]);
        $vacancy = test_input($_POST["vacancy"]);

        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $sql = "UPDATE SensorData SET distance = '" . $distance . "', vacancy = '" . $vacanc$

        if ($conn->query($sql) === TRUE) {
            echo "Updated successfully";
        }
        else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }

        $conn->close();
    }
    else {
        echo "Wrong API Key provided.";
    }

}
else {
    echo "No data posted with HTTP POST.";
}

function test_input($data) {
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

My problem is that instead of searching for the specified spot and updating only that specific row, my script updates all the rows. My database is built like this:

MariaDB [licenta]> describe SensorData;
+--------------+-----------------+------+-----+---------------------+-------------------------------+
| Field        | Type            | Null | Key | Default             | Extra                         |
+--------------+-----------------+------+-----+---------------------+-------------------------------+
| id           | int(6) unsigned | NO   | PRI | NULL                | auto_increment                |
| spot         | varchar(30)     | NO   |     | NULL                |                               |
| mcu          | varchar(30)     | YES  |     | NULL                |                               |
| distance     | int(6)          | YES  |     | NULL                |                               |
| vacancy      | varchar(10)     | YES  |     | NULL                |                               |
| reading_time | timestamp       | NO   |     | current_timestamp() | on update current_timestamp() |
+--------------+-----------------+------+-----+---------------------+-------------------------------+

How can I write the query so that if, for example, I send spot = "B1", it will update only the row in which B1 is located. Thank you!

`WHERE SensorData.spot = 'B1'` ? Though you should really use [prepared statements with bound parameters](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) rather than interpolating variables in your SQL query. — CD001, Mar 10 '20 at 11:06
`$sql = "UPDATE SensorData SET distance = '" . $distance . "', vacancy = '" . $vacanc$` You posted an incomplete line, can you please edit your code to properly display the whole query? — El_Vanja, Mar 10 '20 at 11:06
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Mar 19 '20 at 11:47

score -3 · Accepted Answer · edited Mar 19 '20 at 11:50

-3

You are missing WHERE condition in your $sql query.

You have also syntax error in your code - I suppose that

$sql = "UPDATE SensorData SET distance = '" . $distance . "', vacancy = '" . $vacanc$

is not valid.

Try change $sql variable value to

$sql = "UPDATE SensorData SET distance = '".$distance."', vacancy = '".$vacancy."' WHERE spot = '".$spot."'";

edited Mar 19 '20 at 11:50

RiggsFolly

93,638
21
103
149

answered Mar 10 '20 at 11:10

Dawid Gałecki

66
1
7

HTTP POST Updates all rows

1 Answers1