HTTPS POST to mySQL on localhost

Hi,

I have one thing to ask regarding HTTPS post method.

I used httpclient community library to send sensor data from Photon to mySQL hosted on localhost (http) using Apache and phpmyadmin.

But I am concerning about security because Photon sends sensor data in plain text just like other HTTP post methods.

Firstly, I have configured http://localhost to https://localhost using self-assigned cert.

But I donā€™t know how to HTTPS post data from Photo to mySQL.

I have searched on this forum about HTTPS post and I found HTTPS client by Glowfi.sh team.
But that library has limited examples.

There are only two examples available on that library and timeapi-test.ino is one of them.

But the timeapi-test code caused Photon to have memory issues and made Photon disconnected from Wifi after running for a few minutes.

Can someone enlighten me how to do HTTPS POST or other ways to securely upload data from Photon to mySQL using httpsclient library or webhooks or other methods?

Thanks in advance.

Let me know if this older post helps you. Ping me if it doesn't and I'll see what else I can find.

1 Like

Doing https requests from a microcontroller isnā€™t an easy task. Thatā€™s why Particle implemented webhooks in the cloud api. Everything there is encrypted and can post to your server using https. I would recommend that route if you can.

https://docs.particle.io/guide/tools-and-features/webhooks/

5 Likes

This is what I have read[ here][1] that webhooks needs api.

Is webhooks only specifically meant to publish data to other 3rd party servers such as ubidots & thingspeak?

I cannot find any example of webhooks to link to private localhost server. My apology. :disappointed_relieved:

According to this quote, webhooks seem not ideal for my purpose. :worried:
[1]: Put data to my own database - #4 by johnyrobot5

Thank you @KyleG . I have read the whole thread and I found this.

That's why, I assume the native HTTPS support in Photon is not coming any time soon.

I saw about HTTPS client by glowfish in that thread as well.

But they don't have working any example to post to private " htttps : // localhost " server which is self signed.

The closest that I can find is timeapi-test code, not fully functional and yet it provides very few comments for me to understand what is going on.

What I need is a secure way to upload to mySQL and doesn't necessarily have to be HTTPS..

But I guess I am running out of options. :disappointed_relieved:

As far as webhooks are concerned, you're a third party as well. Webhooks are being sent from the Particle cloud, and as such have to access your server from the outside. If you haven't exposed an API, how's the webhook supposed to reach you? It's what @kennethlimcp explained as well :smile:

1 Like

As long as the amount of data you have to store is less than about 256 bytes per second, and you have the ability to run node.js on a computer somewhere, it could be a regular Windows, Mac, or Linux computer, or even a Raspberry Pi, you can easily do this. Hereā€™s a quick sample I wrote that shows how to store data generated by Particle.publish on the Photon/Electron in a MySQL database using node.js.

3 Likes

@rickkas7
Thanks for your help.

I have followed all the comments, however I ran into this problem.

Random data are successfully published from Photon according to this photo.

But all the data input into mySQL database are happened to be null. :disappointed_relieved:

Is there any chance that I may do wrongly some settings?

Thank you so much.

The thing is that I have no clue how to set up api for localhost assuming that external network can ping to apache local server that I established. :grin:

Sorry, there was a bug in my code. The github is updated, but the call to storeData should looks like this instead:

storeData(JSON.parse(data.data));
5 Likes

@rickkas7

Appreciate your help, Sir. :blush:

I changed that line and it is now working.

Can you please explain the general flow of the mysqlsample-master?

I get the part where Photon is publishing the random using Particle.publish.

But normally when we use Particle.publish, it directly publishes to particle dashboard. Isnā€™t it?

You also helped me before on dealing with Photon deep sleeping mode like 3 months ago. :grinning:

Again, thanks for your help, Sir.

1 Like

Particle.publish sends data to the Particle cloud. One place where you can see it is the console/dashboard in the event log. But itā€™s also sent to subscribers, which include not only Particle devices like Photons and Electrons that use Particle.subscribe but also code that uses the event stream. This includes the phone apps, web server software, and web browsers using the Javascript API.

In this case, itā€™s a server written in node.js that uses the particle-api-js to monitor this event stream for events of a particular type and then send the data into the SQL database. The main advantage of doing this over webhooks is that the server running node.js doesnā€™t have to be accessible to incoming connections from the Internet. Instead, the server makes an outgoing connection to the Particle cloud servers and receives event notifications as they occur over this connection. This allows it to work from home networks without fixed address and using NAT (network address translation), as well.

4 Likes

Thanks for explanation.

This is quite deep. :grinning:

I have a couple of questions regarding running node mysqlsample.js in terminal.

Does running of ā€œnode mysqlsample.jsā€ have to be on the same machine which is hosting localhost?

In my scenario, localserver is being hosted on Pi.

When I run mysqlsample.js on Pi terminal, it works.

But when I tried running on mac, I keep getting this error regardless the fact that Macbook and Pi are under the same Wifi network. :disappointed_relieved:

In this case, localhost is still hosted on Pi.

Another question is that " Is it theoretically possible to run mysqlsample.js to be running on webserver hosting software such as mamp,xampp,lamp? or somewhere else?

My concern is that if someone accidentally or intentionally closes the terminal which is running mysqlsample.js, it will completely stop the process. :flushed:

The node and database do not need to be on the same host. In fact, thatā€™s how I tested it. The most common cause of problems is that by default, mysql only binds to the localhost interface (127.0.0.1) so it will only accept connections from the same host.

Typically, you need to tell it to bind to all address in your my.cnf file and restart the mysql server:

bind-address = 0.0.0.0

Make sure you have your root database password set to a non-empty string before doing that!

As for the other problem, you should google ā€œnode as daemonā€ and it will provide a number of solutions so your node program will run at startup always, in the background.

1 Like

The should also be a package 'forever' that'll keep your application running, or restart it, if it fails for whatever reason. Might be worthwhile looking into as well :smile:

2 Likes

@phyo_tz,

Hereā€™s what I do; it may sound a little involved but it maintains security all through the process, and is pretty flexible.

I use webtasks. These are standalone chunks of javascript code that can be executed in a secure sandbox, linked to a URI. They are hosted by Auth0, which is a pretty interesting company that focuses solely on authentication.

Hereā€™s what you do:

========================

FIRST: Use Particle.publish to send your data out of the particle and into the cloud. This is done securely with the existing Particle infrastructure and minimal coding - easy peasy. Letā€™s say you had something like this in your firmware:

temperature_data = analogRead(tempPin) * temp_conversion_factor; humidity_data = analogRead(pressPin) * humid_conversion_factor; Particle.publish('mySQLDataEvent', String::format("%.1f,%.2f", temperature_data, humidity_data), 60, PRIVATE);

This will generate an event that has a format like this:

{"name":"mySQLDataEvent","data":"21.4, 65.32","ttl":"60","published_at":"2016-09-06T00:45:08.376Z","coreid":"0123456789ABCDEFGHIJKLMN"}

========================

SECOND: Go to Auth0 and sign up for a free account. You get free webtasks up to some limit - info in on their website. (No, I donā€™t work for these guys or have any connection. They just got a good thing going on, thatā€™s all.)

========================

THIRD: Download the webtask CLI. Itā€™s a pretty simple system. Documentation is available at webtask.io. Once youā€™ve installed the CLI, you do a one-time initialization - this is where they make sure youā€™re you by sending by email or cell an authentication code.

========================

FOURTH: Create the webtask code. Here is the code scaffold I use:

[CODE]
var mysql = require(ā€˜mysql@2.7.0ā€™);
var uuid = require(ā€˜uuid@2.0.1ā€™);

function errFunc(err) {
console.error('error: ā€™ + JSON.stringify(err));
}

function parseData(obj) { // convert raw data into object to send to mysql
try { // put parsing code here to convert Particle.publish data into MySQL data object
var param = obj.data.split(ā€™,ā€™);
return {
id: uuid.v4(),
device_id: result.device_id,
timestamp: result.timestamp,
temperature: param[0],
humidity: param[0]
}; // the object keys need to match the MySQL field names
} catch (e) {
return {
parsingFailed: true
};
}
}

function sendData(connection, record) {
var queryString = ā€˜INSERT INTO [TableName] SET ?ā€™; // simple insert SQL code
connection.query(queryString, record, errFunc);
}

function storeData(context, cb, record) {
var connection = mysql.createConnection({
host: ā€˜[INSERT HOST NAME HERE - URI OR IP ADDRESS]ā€™,
user: context.secrets.USERNAME, // these secrets will be sent upon creation of the webtask
password: context.secrets.PASSWORD,
database: ā€˜[INSERT DATABASE NAME HERE]ā€™
});

  connection.connect(function(connectError) {
    if (connectError) {
      console.error('error connecting: ' + connectError.stack);
      cb(new Error('Error code: ' + connectError.code));
    } else {
      console.log('Connected as id ', connection.threadId, 'Sending data: ', record);
      sendData(connection, record);
      connection.end(function(error) {
        if (error) {
          console.error('Error: ', error);
          cb(new Error('Error: ' + error.code));
        } else {
          console.error('Disconnected - data stored');
          cb(null, {
            result: 'Complete'
          });
        }
      });
    }
  });
}

module.exports =
  function(context, cb) {
    var record = parseData(context.data);
    if (record.parsingFailed) {
      cb(new Error('Parsing failed'));
    }

    storeData(context, cb, record);
  };

[/CODE]

Itā€™s pretty simple: first you parse the string that comes from Particle.publish into an object that can be sent to MySQL, then you save that object by connecting with the MySQL server and sending an INSERT query. I save this in a file called something like webtask_put_data_to_mysql.js.

========================

FIFTH: Create the webtask. This is done in the webtask CLI. This is the command I use:

wt create webtask_put_data_to_mysql.js --secret USERNAME=username_string --secret PASSWORD=password_string

This command passes the username and password to the webtask code. It is encrypted and embedded in the webtask, so that anyone who stumbles across the code wonā€™t get your credentials. Thatā€™s a crucial security feature that is a beautiful thing.

When you create the webtask, you get a confirmation and a URI address:

[CODE]
Webtask created

You can access your webtask at the following url:

https://webtask.it.auth0.com/api/run/YOUR-WEBTASK-CONTAINER/webtask_put_data_to_mysql?webtask_no_cache=1
[/CODE]

Copy that URI to the clipboard; youā€™ll need it for the last step.

========================

FINALLY: Integrate your webtask with the Particle webhook. Go to console.particle.io and sign into your account. Go to the integrations section, and click on New Integration, then click on Webhook. A form will appear; under Event name put ā€œmySQLDataEventā€ and in URL paste the location you just copied in step 5. Click Create Webhook, and voila! the data streaming out of your Particle device will be sent to a MySQL server and stored in a table for you to use however you see fit.

========================

Iā€™ve been using this system quite a bit lately, and it works really well. The webtask doesnā€™t require a server; it just sits around until that URI gets called, it starts up, executes in a sandbox, and then goes away. And it is secure; your credentials are nowhere to be found in the code itself.

And the architecture is flexible; I originally sent data to a MySQL server, but recently started using RethinkDB and I just had to change the webtask code; everything else works great. And thereā€™s no reason you canā€™t have several webtasks reading the same data stream, doing separate things.

Hope this helps. Cheers,
L3

1 Like

Thanks. I will try to do the adjustments and update here later on. :grinning:

@leo3linbeck
Thanks you for all the details.

I will do try your suggested way as well. :grinning:

May I ping you when I face problem later? Hope you donā€™t mind. :grin:

good day

sorry but i need to revive this thread. i did the same mysqlsample and all its ok. now i want to do it with data from sensors with decimals. i have tried and sometimes i get that cant be null valor or in the command line i get the value to store but only the first one and it doesnt store.

i know that should be a simple detail, but i cant find it. hope someone can help me. at least with a working example and i can compare with my code.

Hi, this is a great thread! thanks so much for your help @rickkas7! I want to do this with 3 sensors that I am pulling from the particle cloud. I am able to get the values from the cloud from each server but am not able to insert them all into my sql database. Iā€™m not sure how to format the last two functions of the .js file. Here is what they look like now:
(my database table sensors has ā€œpulseā€, ā€œemgā€, ā€œgsrā€ as columns, however the events are named ā€œBPMā€, ā€œEMGā€, and ā€œGSRā€)

cloudEventHandler = function(data) {
	// console.log("Event", data);

	if (data.name == 'BPM') {
		storeData(JSON.parse(data.data));
	}
        if (data.name == 'EMG') {
		storeData(JSON.parse(data.data));
	}
       if (data.name == 'GSR') {
		storeData(JSON.parse(data.data));
	}
}

function storeData(data) {
	console.log("storeData", data);
	
	connection.query('INSERT INTO sensors (pulse, emg, gsr) ?', [{'pulse': data.pulse}, {'emg': data.emg}, {'gsr': data.gsr}], function(err, result) {
		if (err) throw err;

		console.log("id=" + result.insertId);
	});
}