12 replies to this topic

[skarphedinnos]

Hi guys. I have been searching forums for couple of days now with limited success. Can any of you give me an example how I can send data from netduino plus to a SQL server? I have read some threads where people are giving some hints and tips but what most of them have in common is that people are often "guessing" that this or that will work. I have not seen a working example yet. P.s. I have limited programming knowledge and I'm new to netduino/arduino. -Skarphedinn.

[Mario Vernari]

Hello Skarphedinn.
Well, I mean that your Netduino is collecting data from some sensor, then a PC hosting a database has to get these data and store into it.
There are many ways to do it, and before all you should decide how to connect the Netduino with the PC.
Here is an example of moving data from the Netduino, but also to it. It uses a phone, but it's very easy to interface with a database. By the way, the phone itself could host a SQL database.
http://highfieldtale...-windows-phone/

Note that you should have a decent skill of programming for creating your project.
Cheers

[hanzibal]

Hello, thinking there is no SqlClient classes in MF (could be though I doubt it), I suggest you write a simple web service to run on the sql server machine (or one adjacent to that). Netduino would then make http request containing the data for the webservice to be stored in the sql database using conventional methods, i.e ADO.NET or what have, by the webservice. There are examples of how to call a webservice from a Netduino plus here on the forum, just search for it. Good luck!

[skarphedinnos]

Hello Skarphedinn.
Well, I mean that your Netduino is collecting data from some sensor, then a PC hosting a database has to get these data and store into it.
There are many ways to do it, and before all you should decide how to connect the Netduino with the PC.
Here is an example of moving data from the Netduino, but also to it. It uses a phone, but it's very easy to interface with a database. By the way, the phone itself could host a SQL database.
http://highfieldtale...-windows-phone/

Note that you should have a decent skill of programming for creating your project.
Cheers

Thanks Mario. I'll look into this one Eventually this would be data from a sensor that is displayed on a webpage. The storage of the data is so it is possible to see data back in time. Can a phone really host a SQL data base? I thought there would be limitations there like there are in microcontrollers... Well, thanks again

[skarphedinnos]

Hello, thinking there is no SqlClient classes in MF (could be though I doubt it), I suggest you write a simple web service to run on the sql server machine (or one adjacent to that). Netduino would then make http request containing the data for the webservice to be stored in the sql database using conventional methods, i.e ADO.NET or what have, by the webservice.

There are examples of how to call a webservice from a Netduino plus here on the forum, just search for it.

Good luck!

Hi Hanzibal.

I have read there is no sqlClients available for MF. When you are talking about http request, are you referring to situation where the data is sent to the database via url (called injection, i think)? If so, it would work for me in this case, but I have another project in the future where users have to log in to their account to save data from the netduino. So for securiti reasons that would not be a good choice.

Skarphedinn.

[hanzibal]

When you are talking about http request, are you referring to situation where the data is sent to the database via url (called injection, i think)? If so, it would work for me in this case, but I have another project in the future where users have to log in to their account to save data from the netduino. So for securiti reasons that would not be a good choice.

What I suggested has nothing to do with so called "sql injection" which is a method of hacking into an sql database.

Instead, I meant for you to implement one or more well defined web services that you can call from the Netduino. This will not prevent you from enforcing authentication on your Netduino web server.

Depending on what kind of searching, aggregations etc you want do on the historic data, you could settle for storing the readings on a simple SD card attached directly to the Netduino. You could then periodically harvest your data by attaching the SD card to the SQL machine and have it store it for safe keeping.

[icecold]

This works but as people already wrote its a bit dangerous for SQL-injections on my database but since i still havent seen a better solution.

Get Toolbox from: http://netmftoolbox.codeplex.com
Add references to: Toolbox.NETMF.NET.dll and Toolbox.NETMF.NET.Integrated.dll

netduino code:

using Toolbox.NETMF.NET;

Microsoft.SPOT.Net.NetworkInformation.NetworkInterface.GetAllNetworkInterfaces()[0].EnableDhcp(); // For DHCP

HTTP_Client WebSession = new HTTP_Client(new IntegratedSocket("server", 80)); // creates a socket to the server.

// Builds an url like: http://server/directory/netduino_upload.php?temp=1&pressure1=1&pressure2=1&light=1&pass=12345
string sendURL = "/directory/netduino_upload.php?temp=" + "1";
sendURL += "&pressure1=" + "1";
sendURL += "&pressure2=" + "1";
sendURL += "&light=" + "1";
sendURL += "&pass=" + "12345";

            try
            {
                HTTP_Client.HTTP_Response Response = WebSession.Get(sendURL);   // Requests the URL.

                if (Response.ResponseCode != 200)      
                   <insert code> // error
                else
                    <insert code> // success
                Response = null;                        
            }

            catch (SocketException se)
            {
                Debug.Print("Socket Exception! Cant connect?");
                Debug.Print(se.StackTrace);
            }

upload.php

<?php
error_reporting (E_ALL ^ E_NOTICE);

$temp = filter_var($_GET['temp'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$pressure1 = filter_var($_GET['pressure1'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$pressure2 = filter_var($_GET['pressure2'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$light = filter_var($_GET['light'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);
$pass = filter_var($_GET['pass'], FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION);

if ($pass == 12345) {
	mysql_connect("host","login","password") or
	die ('I cannot connect to database because : '. mysql_error());
	mysql_select_db("database");

	$query="INSERT INTO Netduino(temp, pressure1, pressure2, light) values('$temp', '$pressure1', '$pressure2', '$light');";

	mysql_query($query) or die("ERROR ".mysql_error());

	echo "Database Updated ";
}

else {
	echo '<img src="accessdenied.jpg" />';
}

?>

[Stefan]

Hi icecold,

Nice write-up!! I want to add one small detail, which makes it even a tiny bit better:

string sendURL = "/directory/netduino_upload.php?temp=" + 1;
sendURL += "&pressure1=" + 1;
sendURL += "&pressure2=" + 1;
sendURL += "&light=" + 1;
sendURL += "&pass=" + Tools.RawUrlEncode("XXX");

I added the RawUrlEncode method.
In PHP it's important to escape all values before it's added into the query, because characters like quotes have a function in a query.
But also, in the URL some characters could have a function, like & and ?
The RawUrlEncode method replaces this correctly, so all values can be used safely in a URL.

[icecold]

Hi icecold,

Nice write-up!! I want to add one small detail, which makes it even a tiny bit better:

string sendURL = "/directory/netduino_upload.php?temp=" + 1;
sendURL += "&pressure1=" + 1;
sendURL += "&pressure2=" + 1;
sendURL += "&light=" + 1;
sendURL += "&pass=" + Tools.RawUrlEncode("XXX");

I added the RawUrlEncode method.
In PHP it's important to escape all values before it's added into the query, because characters like quotes have a function in a query.
But also, in the URL some characters could have a function, like & and ?
The RawUrlEncode method replaces this correctly, so all values can be used safely in a URL.

Thanks Stefan!

I used "XXX" as an example, it should be numbers to pass the filter.
I changed my post to make that clear.

[hanzibal]

Btw, this is sql injection attempted on toll cameras:


;-)

[skarphedinnos]

Thanks for the replies and ideas guys

I think I'll be able to get started now...

What I suggested has nothing to do with so called "sql injection" which is a method of hacking into an sql database.

I know that Hanzibal I am not clear enough at times since English is not my first language. What I meant is exactly what Icecold said

This works but as people already wrote its a bit dangerous for SQL-injections on my database

. But I guess there isn't that much of security issue since it is possible to use Tools.RawUrlEncode like Stefan pointed out.

Again, thanks a bunch!

Skarphedinn.

[Stefan]

But I guess there isn't that much of security issue since it is possible to use Tools.RawUrlEncode like Stefan pointed out.

That only fixes data transfer issues, no injection issues
URL encoding is a clientside thing. Security issues should -always- be checked serverside.

Although I think you probably know that, I just wanted to point it out, to be sure

[hanzibal]

To address any security issue, you could use digital signage. A simple approach would be to hash the URL and add the hash code as an additional parameter. After you have assembled the complete URL on the Netduino side, you concatenate a secret phrase to it and then apply Utility.ComputeCRC metod to get a 32bit crc number nnnn (cyclic redundancy check). You then remove the secret from the URL and instead add the crc number "&crc=nnnn" lastly. You now have the final URL to be used with the request. The secret phrase is known on the server side too so in order to validate an incoming request, you simply perform the same crc computation in your PHP code and compare that number to the one supplied in the request query string. Also, the crc algorithm must be the same on both sides and I think that the Utility.ComputeCRC method uses the standard crc algorithm. This way you get both integrity check and security but wont prevent duplicate insertions.