ESP8266 – Logging data in a backend with end to end encryption

One off my posts is how to log data that is sent from an ESP8266 device into a MySql database: Logging data on a MySQL database by using the Nodemcu firmware and some PHP on the server side. Keep in mind that post/solution is/was just a quick hack to move data from the ESP8266 into a database. There are better solutions for implementing a process that receives data and stores data from the ESP8266, or any other device, into a database.

Why we should improve the “hack”? Well there is no security or authentication on that example. If deployed in a real world scenario, someone could just inject random value data into MySQL or do some other nasty things, like SQL Injection.

So the idea of this post is to do something more generic and more robust than the original post, namely build a server backend that can satisfy these following points:

  1. Authentication – Only allow authorized devices to log data into a database
  2. E2EE: End to End Encryption – Secure data while in transit from the device to the final storage
  3. MySQL database is hardcoded: – The original example is hardcoded to MySQL. Let’s do something more generic, namely something that can store data on other databases.

One natural candidate for building this generic backend is IBM Node Red. Node Red would allows us to keep developments to a minimum and still have great flexibility.

Options to connect to the backend:
There are several options to get data from the devices into a backend database server securely. Let’s see some of them:

Implement a native database driver on the device:
Ok, this crosses the mind of several people as a possible solution. But we are talking on implementing a database driver on devices that have constrained resources (memory, CPU, connectivity!). It also means that the database must be exposed directly on the network so that the devices can access it directly. Also, in most of the database drivers, the database connection needs to have a constant network connection. So this solution might make sense if the device is an RPi or some other larger/powerful device with a permanent network connection, but it’s not a good idea for a device like an ESP8266 (Not to mention that it might be just plain impossible to port the database driver).

HTTP based interface: REST, SOAP, plain HTTP:
This is far easier to implement since it uses plain HTTP (as the my original example from the ESP8266 to MySQL example), needs no permanent connection to the server, and we can implement authentication and E2EE either by configuring the backend server to use HTTPS or by encrypting data by ourselves. Still HTTPS could be difficult to use, but HTTP is almost universal. So HTTP(S) call to a REST/SOAP server is a good candidate.

MQTT data publishing:
MQTT is also a good candidate but it won’t work well for devices that have intermittent connectivity since MQTT needs a permanent TCP/IP connection to be available from the device to the MQTT broker. MQTT recognizes this limitation, and there is MQTT-SN, for sensor networks, that doesn’t demand a TCP/IP connection.
Authentication and E2EE can be implemented at the protocol level, namely by using authentication on the broker and accessing the broker with TLS/SSL. Also MQTT is not the only alternative, COAP and AMQP are also valid solutions.

So using a native database driver on the device is out of question and so far it seems that the HTTP, MQTT(-SN) and/or COAP protocols are the best way to transfer data from the devices to the backend. Still even implementing these protocols on the end devices might not be good enough for very constrained devices, but that is another issue…

Anyway HTTP and MQTT support, is as of today, pretty standard on all the available firmwares for the ESP8266. But using HTTPS and MQTT over SSL support might still not possible to use1.

So on this post I’ll just use plain REST over HTTP with the payload encrypted with AES.
This means that before we send our data, we will encrypt it with a key (our device key) and send it to the server. The server also holds our device key, and decrypts the data.

The basics:

Since we want to encrypt data during transmission and then decoding at the other, we need to setup a secure channel between our source and our destination database. For the sake of an example I’ll just AES encrypt the source data and decrypt it at the destination. Encryption will solve the possibility of someone eavesdropping the data but it won’t protect from replay attacks, which means someone can grab the full encrypted data and try to flood our server with N repetitions of the same data.

To solve this latest issue, we will add a sequence number to each request before is encrypted and at the receive side we check if the sequence number is not repeated. The sequence number will also allow us to see if we have lost any messages.

So we will use just a plain private shared key, without any kind of key management, or any kind of Public key based solution, to setup our encrypted channel.

Setting up Node-Red:

For setting up Node-red we need to do the following:

– Install the Sqlite node for accessing Sqlite databases:

cd ~/.node-red
npm install node-red-node-sqlite

Install the CryptoJS libraries to allow the use of AES crypto functions in our workflows.

Restart Node-Red so the new Sqlite nodes appear at the nodes pallet.

Implementing the backend server side:
Our backend server based on Node-red should now allow to do the following:

– Decrypt the receiving data sent by the devices.
– Check if the sequence number for the sending device is valid. If not reject the request.
– Store the received data on the database.

The requesting device at startup, since it might not have any kind of permanent storage, might not know what is/was the current/correct sequence number. There are at least three solutions for this:

  1. Case 1: The device requests what is the current sequence number for itself at startup.
  2. Case 2: The device starts at zero, again, but informs the backend that it has reset the sequence to zero
  3. Case 3: The device starts at a random number and informs the backend what is now the current sequence number.

As we can see Case 2 is just a special case of Case 3. We just need to make sure that:

  1. On the first case the backend response with the current sequence number is in fact from a trusted backend
  2. On the other cases the backend must validate that the device is who claims to be and sets the new sequence number.

Also, as for the orinial problem of setting data with protection from replay attacks, Case 2 and Case 3, suffer from the same issue. We also should also protect this calls from replay attacks, but for the sake of simplicity, we not going to do that on this cases.

For implementing the solution we need two tables: One for the device id-> device key association and current sequence number, and the table for storing data. In this last case let’s just suppose it is a simple value.

TABLE Devices
      deviceId: String,
      deviceKey: String,
      deviceSQN: integer

      deviceId: String,
      dataDate: date
      dataValue: integer

We can create both tables on MySQL/PostGres/SQLITE, or for a real example, create the Devices table on a relational standard RDBMS as MySQL/SQLITE, and the Data “table” available on a timeseries database like InfluxDB.

We going to keep everything, for now on SQLITE, since we going to use the Node-Red Sqlite node:

Creating the database:
On the machine running Node-Red, create the database and tables:

cd ~
mkdir Databases
cd Databases

We create now the database and tables:

pcortex@pcortex:~/Databases$ sqlite3 wsn.db 
SQLite version 3.8.2 
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/pcortex/Databases/wsn.db                             

And now we create the tables:

sqlite> create table if not exists Devices ( deviceID String, deviceKey String, deviceSQN INTEGER);
sqlite> create table if not exists Data ( deviceID String, dataDate Date, dataValue Integer);
sqlite> .tables
Data     Devices

We can now just insert some dummy data into the device table:

sqlite> insert into Devices values( '12FA', '2B7E151628AED2A6ABF7158809CF4F3C', 0);
sqlite> select * from Devices;

The process of filling up this tableis another story in itself, so for the purpose of this post we just insert the device id, in this case 12FA, an AES key and the sequence number 0.

Retrieving the Sequence Number (Case 1):

Our device will retrieve the current sequence number using a REST Api GET operation by calling our backend, running on Node-Red. The request for retrieving the sequence number will return an encrypted JSON object as follow:

{"SEQN": 20 }

Where in this case, 20 is the current sequence number stored in the database.

The REST API is built as follow:



The HTTP input node will listen to the URL /wsn/:id where id is the device ID for the device that we want to obtain the sequence number. We then, based on the device ID, query the SQLITE Devices table, obtain the current sequence number, encrypt it with the device key and send the response to the requesting device. The requesting party using the device key, decrypts the answer and obtains the sequence number.

[{"id":"501864bd.eea2ec","type":"sqlitedb","z":"ee002ffe.ffd9e8","db":"/home/odroid/Databases/wsn.db"},{"id":"27489da4.ff5382","type":"http in","z":"ee002ffe.ffd9e8","name":"getSensorSEQN","url":"/wsn/:id","method":"get","swaggerDoc":"","x":169.1666717529297,"y":96.0833511352539,"wires":[["e9c38500.a29dc"]]},{"id":"313d3819.34686","type":"http response","z":"ee002ffe.ffd9e8","name":"","x":801.1666870117188,"y":92.00000762939453,"wires":[]},{"id":"aec88434.e67e98","type":"sqlite","z":"ee002ffe.ffd9e8","mydb":"501864bd.eea2ec","name":"Get Device SEQN","x":384.16668701171875,"y":232.75,"wires":[["108630b.69050cf","33e20204.5cbc66"]]},{"id":"108630b.69050cf","type":"function","z":"ee002ffe.ffd9e8","name":"Encrypt response","func":"// Import the global Crypto-js module defined on Node-Red settings.js file\nvar cryptojs =;\ntry {\n    // Get the key and the sequence number.\n    var AESKey = msg.payload[0].deviceKey;\n    var SEQNum = msg.payload[0].deviceSQN;\n\n    // Build the new payload. Just the sequence number\n    msg.payload = '{\"SEQN\":' + SEQNum + '}';\n\n    // Move data to base64\n    var bdata = new Buffer(msg.payload).toString('base64');\n\n    // Encrypt the data with the device key.\n    var ciphertext = cryptojs.AES.encrypt(bdata, AESKey );\n\n    // The payload is now the encrypted data\n    msg.payload = ciphertext.toString();\n} catch (err) {\n    msg.payload = \"\";\n    node.log(\"Invalid deviceID request for GET SQN Operation\");\n}\nreturn msg;\n","outputs":1,"noerr":0,"x":650.1666870117188,"y":233.58334350585938,"wires":[["313d3819.34686","33e20204.5cbc66"]]},{"id":"e9c38500.a29dc","type":"function","z":"ee002ffe.ffd9e8","name":"Build Query","func":"var deviceId =;\nmsg.topic=\"Select * from Devices where deviceID='\" + deviceId +\"'\";\nreturn msg;","outputs":1,"noerr":0,"x":182.1666717529297,"y":232.58334350585938,"wires":[["aec88434.e67e98"]]},{"id":"9d945f85.62b8a","type":"catch","z":"ee002ffe.ffd9e8","name":"","scope":null,"x":471.16668701171875,"y":93.0833511352539,"wires":[["313d3819.34686"]]},{"id":"33e20204.5cbc66","type":"debug","z":"ee002ffe.ffd9e8","name":"","active":false,"console":"false","complete":"payload","x":635.1666870117188,"y":323.66668701171875,"wires":[]},{"id":"9a0c49f6.f84f","type":"comment","z":"ee002ffe.ffd9e8","name":"REST API - GET operation","info":"Obtains the current Sequence Number for the device","x":144.1666717529297,"y":36.91667175292969,"wires":[]}]

Just copy the above Node-Red flow and use the Import function on Node-Red to paste the code.

We can test the API through wget or curl:

 wget -qO- http://localhost:1880/wsn/12FA
 curl -X GET  http://localhost:1880/wsn/12FA

Setting the Sequence Number (Case 2 and 3):

For this case the device defines what sequence number it should use, zero or other random number, encrypts with device key the JSON object { SEQN: # } where # is the sequence number, and wraps it on a JSON POST request to our backend:
For example:

 { msg:"U2FsdGVkX1+VRlCORJjs2mxxTljfcdu6Z7G8JVyFx7b+jaqKMLeBx4ecLQnjUOYp" }

The above encrypted data is: {“SEQN”:120}.

As we’ve referred earlier we wont protect this call from replay attacks, so calling the API with same message over and over will just reset the sequence.

The flow to process the request for reseting the sequence is as follow:
Set Sequence flow

and the code is:

[{"id":"501864bd.eea2ec","type":"sqlitedb","z":"ee002ffe.ffd9e8","db":"/home/odroid/Databases/wsn.db"},{"id":"60f28b66.30c0b4","type":"http in","z":"ee002ffe.ffd9e8","name":"setSensorSEQN","url":"/wsn/:id","method":"post","swaggerDoc":"","x":166.1666717529297,"y":467.0833740234375,"wires":[["78ba9614.8925f"]]},{"id":"78ba9614.8925f","type":"function","z":"ee002ffe.ffd9e8","name":"Build Query","func":"// Get the device id\nvar deviceId =;\n\n// Build the query. The SQLITE node requires the query in msg.topic\nmsg.topic=\"Select * from Devices where deviceID='\" + deviceId +\"'\";\n\n// Let's pass these parameters forward on its on variables:\nmsg.deviceid = deviceId;\nmsg.rawdata  = msg.payload;\n\nreturn msg;\n","outputs":1,"noerr":0,"x":373.16668701171875,"y":467.2499694824219,"wires":[["76185c6b.95edbc"]]},{"id":"76185c6b.95edbc","type":"sqlite","z":"ee002ffe.ffd9e8","mydb":"501864bd.eea2ec","name":"Get Device AES Key","x":188.1666717529297,"y":532.0833740234375,"wires":[["7fd8ec4.4d1e114"]]},{"id":"7fd8ec4.4d1e114","type":"function","z":"ee002ffe.ffd9e8","name":"Decrypt Request","func":"var cryptojs =;\ntry {\n    // Get the key and the raw encrypted data\n    var AESKey = msg.payload[0].deviceKey;\n    var rawdata= msg.rawdata.msg;\n\n    // Decrypt the payload data with the device key. \n    // It returns a string sequence of bytes.\n    var bytes = cryptojs.AES.decrypt( rawdata, AESKey );\n    \n    // Convert bytes to an UTF8 plain string\n    var plaintext = bytes.toString(cryptojs.enc.Utf8);\n   \n    // Convert from base64 to string\n    msg.payload  = new Buffer(plaintext , 'base64').toString('ascii');\n\n    return [ null , msg ];  // Exit the function at output 2.\n    \n} catch (err) {\n    msg.payload = \"\";\n    msg.statusCode=500;     // Set internal server error\n    node.log(\"Invalid deviceID request for GET SQN Operation\");\n    return [ msg , null ];  // Exit the function at output 1\n}\nreturn msg;","outputs":"2","noerr":0,"x":176.1666717529297,"y":597.25,"wires":[["4c3674c1.cdde1c"],["8eba7bdc.d8358"]]},{"id":"4c3674c1.cdde1c","type":"http response","z":"ee002ffe.ffd9e8","name":"","x":596.1666870117188,"y":588.8334045410156,"wires":[]},{"id":"6a9430f9.542d08","type":"sqlite","z":"ee002ffe.ffd9e8","mydb":"501864bd.eea2ec","name":"Update SQN","x":435.16668701171875,"y":679.0833740234375,"wires":[["4c3674c1.cdde1c"]]},{"id":"8eba7bdc.d8358","type":"function","z":"ee002ffe.ffd9e8","name":"Build UPD Query","func":"// At this point we should have:\n// msg.payload with the sequence number in JSON: { SEQN: 200}\n// msg.deviceid with the device id\nvar seqnObj = JSON.parse(msg.payload);\nvar seqn = seqnObj.SEQN;\n\nnode.log(\"Seq load: \" + seqn);\n\nmsg.topic = \"update Devices set deviceSQN = \" + seqn +\" where deviceId = '\" + msg.deviceid + \"'\";\n\nreturn msg;","outputs":1,"noerr":0,"x":179.1666717529297,"y":678.75,"wires":[["6a9430f9.542d08"]]}]

For testing we can use sqlite to reset the sequence:

sqlite3 wsn.db 
SQLite version 3.8.2 2013-12-06 14:53:30
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> update Devices set deviceSQN=30;
sqlite> select * from Devices;

and do the encrypted request:

curl -H "Content-Type: application/json" -X POST -d     '{"msg":"U2FsdGVkX1+VRlCORJjs2mxxTljfcdu6Z7G8JVyFx7b+jaqKMLeBx4ecLQnjUOYp"}' http://localhost:1880/wsn/12FA

and see the result:

sqlite> select * from Devices;

We can repeat the request over and over again, and it will be accepted every time since we have no mechanism to stop that.

We can see that the most secure way to use the Sequence Number is to only use Case 1, requesting it, and not Case 2 and Case 3 where the devices set’s the sequence and is vulnerable to replay attacks.

So this is already a long post and there are still several things to do:
– The data storage request handler so we can finally transmit securely our data.
– The device code on the ESP8266 to execute the requests.

Comming up in the next posts!

One thought on “ESP8266 – Logging data in a backend with end to end encryption

  1. Pingback: ESP8266 – Logging data in a backend with end to end encryption – Storing Data | Primal Cortex's Weblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s