Made a new version with $_GET password, prepared statement and check for correct app__id and dev__id:
<?php
//check for correct password in url, URL needs to look like this : www.yoururl.com/ttnpost2.php?pwd=password
if ($_GET["pwd"] == "password")
{
//Make sure that it is a POST request.
if(strcasecmp($_SERVER['REQUEST_METHOD'], 'POST') != 0){
throw new Exception('Request method must be POST!');
}
//Make sure that the content type of the POST request has been set to application/json
$contentType = isset($_SERVER["CONTENT_TYPE"]) ? trim($_SERVER["CONTENT_TYPE"]) : '';
if(strcasecmp($contentType, 'application/json') != 0){
throw new Exception('Content type must be: application/json');
}
//Receive the RAW post data.
$content = trim(file_get_contents("php://input"));
//write post data to file for debug, you need to create data2.txt in the same folder as ttnpost2
//file_put_contents('data2.txt', $content . PHP_EOL, FILE_APPEND);
//Attempt to decode the incoming RAW post data from JSON.
$decoded = json_decode($content, true);
//If json_decode failed, the JSON is invalid.
if(!is_array($decoded)){
throw new Exception('Received content contained invalid JSON!');
}
// Create connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
//fill variables from decoded JSON array
$celcius = $decoded['payload_fields']['celcius'];
$volt = $decoded['payload_fields']['volt'];
$latitude = $decoded['metadata']['gateways']['0']['latitude'];
$longitude = $decoded['metadata']['gateways']['0']['longitude'];
$app_id = $decoded['app_id'];
$dev_id = $decoded['dev_id'];
//validate app_ID and dev_id for security purposes before updating DB. You need to change these according to your own id'scandir'
if ($app_id == "boat" && $dev_id == "boat") {
//update DB using prepared statement
$stmt = $conn->prepare("INSERT INTO sensordata2(celcius, volt, latitude, longitude, app_id, dev_id) VALUES(?,?,?,?,?,?)");
$stmt->bind_param("ddddss", $celcius, $volt, $latitude, $longitude, $app_id, $dev_id);
$stmt->execute();
$stmt->close();
$conn->close();
}
}
else{
throw new Exception("Incorrect password");
}
?>
Let me know what you think…
and with this one you can test/check easily without needing input from TTN:
<?php
//API Url
$url = "www.yoururl.com/ttnpost2.php?pwd=password";
//Initiate cURL.
$ch = curl_init($url);
//Tell cURL that we want to send a POST request.
curl_setopt($ch, CURLOPT_POST, 1);
$jsonDataEncoded = "{\"app_id\":\"boat\",\"dev_id\":\"boat\",\"hardware_serial\":\"0004A30B001A4BC2\",\"port\":1,\"counter\":6854,\"payload_raw\":\"NdacWGYPaOmnH+LtowP5/xAAAgQB\",\"payload_fields\":{\"celcius\":15,\"volt\":4.02},\"metadata\":{\"time\":\"2017-02-09T20:51:08.72197317Z\",\"frequency\":868.1,\"modulation\":\"LORA\",\"data_rate\":\"SF12BW125\",\"coding_rate\":\"4/5\",\"gateways\":[{\"gtw_id\":\"eui-b827ebfffe817a05\",\"timestamp\":1452982252,\"time\":\"2017-02-09T20:51:08.6936Z\",\"channel\":0,\"rssi\":-119,\"snr\":-10,\"rf_chain\":1,\"latitude\":53.107,\"longitude\":6.125,\"altitude\":40}]},\"downlink_url\":\"https://integrations.thethingsnetwork.org/ttn-eu/api/v2/down/boat/boat?key=ttn-account-v2.*******************************\"}";
//Attach our encoded JSON string to the POST fields.
curl_setopt($ch, CURLOPT_POSTFIELDS, $jsonDataEncoded);
//Set the content type to application/json
curl_setopt($ch, CURLOPT_HTTPHEADER, array("Content-Type: application/json"));
//Execute the request
$result = curl_exec($ch);
echo $result;
echo "<br>";
echo "done";
?>
)
).
