# HTTP integration

**URL:** <https://www.thethingsnetwork.org/forum/t/http-integration/5271>\
**Category:** Uncategorized\
**Created:** [February 7, 2017, 10:17am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271 "2017-02-07T10:17:49Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 7, 2017, 10:17am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/1 "2017-02-07T10:17:49Z")

</div>

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/](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 😉 )

Thx!!  
Jeroen

---

<div class="post-metadata">

**Author:** ![rudiniemeijer](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/rudiniemeijer/32/28312_2.png) [@rudiniemeijer](https://www.thethingsnetwork.org/forum/u/rudiniemeijer)\
**Post date:** [February 7, 2017, 12:41pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/2 "2017-02-07T12:41:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 7, 2017, 3:16pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/3 "2017-02-07T15:16:40Z")

</div>

> [@I-Connect](#):
>
> What to put into the “add integration” part of the console

First try with [https://requestb.in](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.

> [@rudiniemeijer](#):
>
> The ‘magic’ you want to investigate is called MQTT

Nope. The question is about [https://www.thethingsnetwork.org/docs/applications/http/](https://www.thethingsnetwork.org/docs/applications/http/) That’s a different thing, brand new.

---

<div class="post-metadata">

**Author:** ![rudiniemeijer](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/rudiniemeijer/32/28312_2.png) [@rudiniemeijer](https://www.thethingsnetwork.org/forum/u/rudiniemeijer)\
**Post date:** [February 7, 2017, 4:10pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/4 "2017-02-07T16:10:16Z")

</div>

Brilliant! Wasn’t aware http was already available!

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 7, 2017, 4:47pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/5 "2017-02-07T16:47:25Z")

</div>

Hi Arjan,

Thx, indeed my question is wrt the “Brand new thing” 🙂

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 🙂

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

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 7, 2017, 5:44pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/6 "2017-02-07T17:44:46Z")

</div>

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 🙂
> 
> - …
> - 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

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 7, 2017, 6:21pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/7 "2017-02-07T18:21:14Z")

</div>

ah nice, will check it out

---

<div class="post-metadata">

**Author:** ![EelkeVisser](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/eelkevisser/32/35697_2.png) [@EelkeVisser](https://www.thethingsnetwork.org/forum/u/EelkeVisser)\
**Post date:** [February 7, 2017, 6:43pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/8 "2017-02-07T18:43:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 9, 2017, 4:53pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/9 "2017-02-09T16:53:55Z")

</div>

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
<?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 😉 ).~~

EDIT: Now it works see post below

Jeroen

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 9, 2017, 6:31pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/10 "2017-02-09T18:31:53Z")

</div>

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 🙂

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

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 9, 2017, 11:27pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/11 "2017-02-09T23:27:28Z")

</div>

> [@I-Connect](#):
>
> Now waiting for my gateway to arrive and build the live node

…so meanwhile you can read [about SQL injection](https://en.wikipedia.org/wiki/SQL_injection) and [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). 😉

![Mandatory XKCD comic about SQL Injection](https://www.thethingsnetwork.org/forum/uploads/default/original/2X/6/68458148094853d37fb5feaa543f31d0cd512856.png "Her daughter is named Help I'm trapped in a driver's license factory.")

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](https://en.wikipedia.org/wiki/Basic_access_authentication#Client_side) or [Digest Access Authentication](https://en.wikipedia.org/wiki/Digest_access_authentication); see references [in my other answer below](https://www.thethingsnetwork.org/forum/t/http-integration/5271/28)). 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.

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 10, 2017, 9:54am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/12 "2017-02-10T09:54:17Z")

</div>

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

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…

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 10, 2017, 11:16am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/13 "2017-02-10T11:16:07Z")

</div>

> [@I-Connect](#):
>
> having some issue though passing a decimal via the query to the db

- 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:

- 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).

> [@I-Connect](#):
>
> I could check the TTN account that is hidden in the downlink URL?

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.

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 10, 2017, 5:38pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/14 "2017-02-10T17:38:01Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 10, 2017, 5:42pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/15 "2017-02-10T17:42:56Z")

</div>

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

?>
```

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 10, 2017, 6:46pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/16 "2017-02-10T18:46:59Z")

</div>

Note that application id’s are not secret; for device id’s I don’t know. For inspiration about goats see [https://discovery.thethingsnetwork.org/announcements/handler](https://discovery.thethingsnetwork.org/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 😉~~

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`).

---

<div class="post-metadata">

**Author:** ![vorstendom](https://www.thethingsnetwork.org/forum/letter_avatar_proxy/v4/letter/v/7cd45c/32.png) [@vorstendom](https://www.thethingsnetwork.org/forum/u/vorstendom)\
**Post date:** [February 10, 2017, 8:22pm UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/17 "2017-02-10T20:22:23Z")

</div>

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 🙂

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

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 11, 2017, 9:47am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/18 "2017-02-11T09:47:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![I-Connect](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/i-connect/32/20484_2.png) [@I-Connect](https://www.thethingsnetwork.org/forum/u/I-Connect)\
**Post date:** [February 11, 2017, 11:06am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/19 "2017-02-11T11:06:46Z")

</div>

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…?

---

<div class="post-metadata">

**Author:** ![arjanvanb](https://www.thethingsnetwork.org/forum/user_avatar/www.thethingsnetwork.org/arjanvanb/32/46626_2.png) [@arjanvanb](https://www.thethingsnetwork.org/forum/u/arjanvanb)\
**Post date:** [February 11, 2017, 11:35am UTC](https://www.thethingsnetwork.org/forum/t/http-integration/5271/20 "2017-02-11T11:35:26Z")

</div>

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 🙂

[Next page](https://www.thethingsnetwork.org/forum/t/http-integration/5271.md?page=2)