Retrieving JSON post data in php and inserting into mySQL database

Question

I'm trying to send some Json formatted data via POST from a micropython controller to a PHP page and then into a mySQL database. The problem I am having is that the PHP code is not reading in the values from the Json data and therefore it is not being inserted into the database.

At the moment, it seems like the data is being successfully sent to the PHP page and in the right format. Here is the code below:

Python code sending the data to the php page:

data = '{"DeviceName": 1, "Humidity": %.2f, "Temperature": %.2f }'%(hum,temp)
headers = {'content-type': 'application/json'}
print(data)
res = urequests.post('http://192.168.1.187/insert.php', json=data, headers=headers)
print(res)

Here is the JSON string being sent:

{"DeviceName": 1, "Humidity": 36.88, "Temperature": 27.99 }

PHP code:

$server = "localhost";
$username = "admin";
$password = "passw";
$db = "test";

$dbCon = new mysqli($server, $username, $password, $db) or die("Unable to Connect");

$response = array();
$res=array();

$jsonRaw = file_get_contents('php://input');
$json = json_decode($jsonRaw);

if($json!=null){

    $temperature = $json->Temperature;
    $device = $json->DeviceName;

    $sql = "insert into hostdata (HostID, DandT, Temp) values ('$device', now(), '$temperature')";

    if(mysqli_query($dbCon, $sql)){
        $svrResp["code"] = "1";
        $svrResp["message"] = "Sucessfully Connected";

        echo json_encode($response);
    }else{
        $svrResp["code"] = "2";
        $svrResp["message"] = mysqli_error($dbCon);
        echo json_encode($response);
    }
}else{
    echo "JSON data error";

}
mysqli_close($dbCon);



 ?>

This should insert the deviceName (is actually a number) and the temperature value into the sql statement and update the database. However, it is triggering the sql insert statement, just both the HostID and Temperature values are 0 when it is inserted. Am I missing something?

Any help would be appreciated!!

**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add any data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or data *of any kind* directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jan 30 '19 at 00:21
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and ideally should not be used in new code. Use `$dbCon->error` in preference to `mysqli_error($dbCon)` — tadman, Jan 30 '19 at 00:21
@Brad That's generally where arbitrary data comes from. I'd say "arbitrary" but it's a more ambiguous term that leads to confusion. — tadman, Jan 30 '19 at 00:22
@tadman Any data then. People get confused and think that using parameterized queries is solely about security from user input, but that's just simply not the case. It's also about properly functioning software. You don't want some string with a quote mark in it breaking your data, even if you the coder put it there. Always use parameterized queries and this is a non-issue. — Brad, Jan 30 '19 at 00:23
That's a good point. I'll adjust the phrasing in that snippet. — tadman, Jan 30 '19 at 00:24
Tried dumping out the decoded JSON ~ `var_dump($json);`? What does it look like? What's your error reporting set to? See [How to get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php) — Phil, Jan 30 '19 at 00:34
@Phil the var_dump just prints , but surely the $json won't be populated until I run my python code to send the data? After I do send the data, should the php page automatically update in my browser to show the var_dump output? — Tom Raw, Jan 30 '19 at 00:47
Whoo boy, you seem to have a misunderstanding about how HTTP requests work. Let me see if I can dig up some reading material for you — Phil, Jan 30 '19 at 00:49
Have a read of [this question and the answers](https://stackoverflow.com/q/16181273/283366). It's about AJAX requests but substitute your python script for the JavaScript / AJAX part as they really are equivalent in this scenario. Also, seems you don't have decent error reporting enabled. See the other post I linked above. — Phil, Jan 30 '19 at 00:53
@Phil So I've had a look, still can't get it to work. Is there anything in my code that you can see which is wrong? To explain it better, I have the php page and database hosted locally on my laptop and then I need to run the python script in atom for the data to be sent via Post. This is because the JSON won't be populated until device has collected the readings and stored them in the relevant variables. The device posts the json data to my laptop Ip address with the /insert.php appended to it. Thanks — Tom Raw, Jan 30 '19 at 12:02

Retrieving JSON post data in php and inserting into mySQL database

0 Answers0