HTTP integration


(I Connect) #1

I am trying to understand how I can upload data via HTTP integration through my website to my MySQL database.

I read the documentation https://www.thethingsnetwork.org/docs/applications/http/ but I am having trouble exactly understanding how to do this.

If I understood correctly you can automatically send a put or post request to a website containing the data, the website should be able to execute a query to put it into a database (I guess using some php...)

Does anybody know how to do this, maybe even has an example I could look at?
-What to put into the "add integration" part of the console
-An example of a php/web page which transfers the data to the MySql database.

(I cannot test it yet as my gateway is still somewhere in Shenzen :wink: )

Thx!!
Jeroen


How can make an http integration
(Rudiniemeijer) #2

The 'magic' you want to investigate is called MQTT. That is a protocol with which you 'get' the data from TTN and into a service of your calling.


(Arjan) #3

First try with https://requestb.in and see what is being sent. However...

I cannot test it yet as my gateway is still somewhere in Shenzen

It's not related to a specific gateway. If you see packets in the TTN Console, then you can have those sent to the HTTP integration too.

Nope. The question is about https://www.thethingsnetwork.org/docs/applications/http/ That's a different thing, brand new.


(Rudiniemeijer) #4

Brilliant! Wasn't aware http was already available!


(I Connect) #5

Hi Arjan,

Thx, indeed my question is wrt the "Brand new thing" :slight_smile:

I know I can use a different gateway but there is no other nearby enough, or maybe I am doing something wrong with my node... it is the first one. So I thought I would wait for my gateway to arrive to make sure that is not the issue... but therefor I cannot test :slight_smile:

The link you provided will help me test, but still the message would need to be parsed within my website and then passed to the MySQL database... I am not that experienced that I can create that on my own, if somebody could share an example that would be great.

I am currently also looking into node-red icw elastic search but for this I would need my own server and it would be nice if I can just run it via HTTPS to a MySQL db at a regular web hoster that I allready pay for the website.

thx,
Jeroen


(Arjan) #6

As for testing without a gateway you're in luck, Hylke posted the following in Slack today:

As we just updated all public routing servers to v2.5.0, I thought it would be nice to let you guys know what new features we added to the backend in the last month, so here's the summary :slight_smile:

  • ...
  • Sending simulated uplink to the Handler for testing applications or integrations

Some of these features still have to be implemented in the console, but you can already use them by talking directly to the APIs


(I Connect) #7

ah nice, will check it out


#8

Ik have played a bit with the Http Integration, and I used this code in a post.php file:

<?php 
$postdata = file_get_contents('php://input');
file_put_contents('d:\removedforsecurity.com\www\loradata\data.txt', 
    $postdata . PHP_EOL, FILE_APPEND);
echo 'ok';
?>

It will write your data to a txt file on your server. The URL to this file needs to be configured in the Http Integration, of course! I didn't have time yet, to figure out the SQL queries.

Good luck.


(I Connect) #9

ok, some steps further. On advice of Hylke simulated input via the payload decode function:

 var decoded = {"v1":12,"v2":13,"t":22,"h":88,"la":005,"lo":055};
 return decoded;

And I trigger a payload to be delivered via ttnctl:

ttnctl devices simulate 201612001 11111111

payload is then sent to a ttnpost.php page I made (honoustly looking up most of it via google…):

<?php

//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"));
 
//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!');
}

//Process the JSON.

// Create connection
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "dbname";

$myConnection= mysqli_connect($servername,$username,$password,$dbname) or die ("could not connect to mysql"); 

$vBattery1 =   $decoded['payload_fields']['v1'];
$vBattery2 =   $decoded['payload_fields']['v2'];
$temperature=  $decoded['payload_fields']['t'];
$humidity=     $decoded['payload_fields']['h'];
$locationLat=  $decoded['payload_fields']['La'];
$locationLong= $decoded['payload_fields']['lo'];

$sqlCommand="INSERT INTO sensordata(vBattery1, vBattery2, temperature, humidity, locationLat, locationLong) VALUES('$vBattery1','$vBattery2','$temperature','$humidity','$locationLat','$locationLong')";

$query=mysqli_query($myConnection, $sqlCommand) or die(mysql_error()) 

?>

(this ttnpost.php works if I call it from another php page with a json in the POST)

When I now trigger it with ttnctl a new row is added to the DB but has no values… To be continued (hints are welcome :wink: ).

EDIT: Now it works see post below

Jeroen


Parsing HTTP integration data in PHP
(I Connect) #10

ok, found it (corrected in the post above). I got much more than just my sensor value JSON.
I was expecting at the website:

{"v1":12,"v2":13,"t":22,"h":88,"la":005,"lo":055}

But I got:

{"app_id":"201612001","dev_id":"201612001","hardware_serial":"***************","port":1,"counter":0,"payload_raw":"EREREQ==","payload_fields":{"h":88,"la":5,"lo":45,"t":22,"v1":12,"v2":13},"metadata":{"time":"2017-02-09T18:24:23.022746155Z"},"downlink_url":"https://integrations.thethingsnetwork.org/ttn-eu/api/v2/down/201612001/01?key=ttn-account-v2.************************"}

so needed to adapt the array from

$vBattery1 = $decoded['v1'];

to

$vBattery1 = $decoded['payload_fields']['v1'];

very nice :slight_smile:

Now waiting for my gateway to arrive and build the live node :slight_smile:


(Arjan) #11

…so meanwhile you can read about SQL injection and prepared statements. :wink:

Mandatory XKCD comic about SQL Injection

Also, to ensure the requests are really coming from TTN:

  • Set a value for the integration’s Authorization HTTP header (beware that some servers might only support the official Authorization: <type> <credentials> syntax, and might even ignore invalid values when using Basic Access Authentication or Digest Access Authentication; see references in my other answer below). Or add a custom header and a value†, and validate that in your code:

  • Or: add some authentication to the URL (something like ttnpost.php?myauth=my-super-secret)

  • Or: check the key in downlink_url against what you know is correct

  • Or: or use a random URL (like my-super-secret-h7dnt01kqq9.php)


† Added to this answer September 2017; the Authorization header and custom header were added to the integration somewhere after its initial release, and after the initial version of this answer, as posted February 2017.


Authenticated and Authorized HTTP Integration on AWS or Azure
(I Connect) #12

hahahaha, yes indeed, already did some reading on that topic. Lot of knowledge to digest, one step at a time :slight_smile:

and that is a good tip to check if it actually comes from TTN... I could check the TTN account that is hidden in the downlink URL?

thx

ps still having some issue though passing a decimal via the query to the db, also there some reading up and improvements to make...


(Arjan) #13
  • Make sure you get decimals in the JSON payload. I don't see any in your {"h":88,"la":5,"lo":45,"t":22,"v1":12,"v2":13}.

  • The JSON that is sent by TTN should send numbers, so should not have quotes around numeric values:

    "payload_fields": {
      "v1": 12.3,
      "v2": 4.56
    }

    Check your payload functions if it looks like "v1": "12.3".

  • Make sure the database column allows for decimals, like MySQL DECIMAL(6,2) to store values from -9999.99 to 9999.99.

  • Use prepared statements; in your example above you're passing strings to the database: VALUES('$vBattery1', '$vBattery2', ...), being VALUES('12.3', '4.56', ...). The database will very likely convert that to numbers if your database columns are numbers, but it's still an unneeded conversion that might give you surprises. In your example, VALUES($vBattery1, $vBattery2, ...) would already be better (but is still insecure).

Yes, that's what I meant. Or, to avoid losing any data when you change the keys: don't validate the key when getting the data, but store the key in the database as well. This way you can filter later on.


(I Connect) #14

ah, it was the declaration in the database, I selected DECIMAL but this has a default type of 10,0 ... pfff


(I Connect) #15

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";

?>

Help with HTTP integration
(Arjan) #16

Note that application id's are not secret; for device id's I don't know. For inspiration about goats see http://discovery.thethingsnetwork.org:8080/announcements/handler

It seemed the first payload you posted had longitude and latitude from some GPS on the node? Now you're using the location of the first gateway in the list (and I don't know if the first has the best reception).

$stmt->bind_param("ddddss", $celcius, ...

Some debugging left there I guess :wink:

Don't forget to store a timestamp too (or use a column that is automatically set on insert, like MySQL: received timestamp default current_timestamp).


(Vorstendom) #17

Hi Arjan, again thx for the feedback. How would you come to know the app or dev id? But i now know how to do it so can use anything... the lat lon indeed comes from the gateway, it is not really relevant info i was just trying to get some data from deeper in the json.

But still good remark because now i understand why is called gatewayS; there can be more then 1 listed if in range :slight_smile:

Not sure what you mean with some debugging to do, do you mean something is wrong in the sql part?


(Arjan) #18

I was referring to the "ddddss" in your code (and hence supplying 7 values for 6 parameters). :slight_smile:


(I Connect) #19

Maybe i misunderstood how it works, but i thought the ddddss says next inputs are 4 doubles and 2 strings, so 6 definitions for the 6 inputs...?


(Arjan) #20

Oh, totally my bad! I don't know PHP, but it looked like garbage and I thought you deleted some columns before posting here... I almost deleted it from your post; glad I didn't :slight_smile: