0

Hello fellow programmers! I am in the middle of making a small project - it is about sending data from arduino sensors to the database via PHP script. I have my own website as a host and my php script that use GET method to send the data. I tested it multiple times, it sends data properly if i put the raw number into sql query (database is gaining a record), but the problem starts when i want to put variable from arduino into query - it puts "0" inside the table. I watched a lot of tutorials and looked for solutions here - to no avail. Here is my PHP script:

<html>
<body>

<?php

require_once "sql_connect.php"; 

$connect = @mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);

if(!$connect){
    echo "Error: " . mysqli_connect_error();
    exit();
}

echo "Connection Success!<br><br>";

$waterlevel = $_GET["waterlevel"];
echo $waterlevel;

$query = "INSERT INTO waterLevelTable (waterlevel) VALUES ('".$waterlevel."')";  //sends 0 
//$query = "INSERT INTO waterLevelTable (waterlevel) VALUES ('20')"; sends 20 to table
$result = mysqli_query($connect,$query);
echo "Insertion Success!<br>";

?>
</body>
</html>

sql_connect.php is where i have me credentials.

Now onto the Arduino code. I "borrowed" fragment of code from arduino examples, DHCPAdressPrint to connect to internet, wrote code for water sensor and then to tried to send sensor value to database via GET method. And this is where i struggle... Here is the code:

#include <SPI.h>
#include <Ethernet.h>
#define POWER_PIN_WATER_SENS 7
#define SIGNAL_PIN_WATER_SENS A5
#define SOUND_PIN A0
byte mac[] = {0x00, 0xAA, 0xBB, 0xCC, 0xDE, 0x02};
int waterLevel = 0;
String waterLevelString;     
EthernetClient client;
void setup() {

  Serial.begin(9600);
  pinMode(POWER_PIN_WATER_SENS,OUTPUT);
  digitalWrite(POWER_PIN_WATER_SENS, LOW);  
  Serial.println("Initialize Ethernet with DHCP:");
  if (Ethernet.begin(mac) == 0) {
    Serial.println("Failed to configure Ethernet using DHCP");
    if (Ethernet.hardwareStatus() == EthernetNoHardware) {
      Serial.println("Ethernet shield was not found.  Sorry, can't run without hardware. :(");
    } else if (Ethernet.linkStatus() == LinkOFF) {
      Serial.println("Ethernet cable is not connected.");
    }
    while (true) {
      delay(1);
    }
  }
  Serial.print("My IP address: ");
  Serial.println(Ethernet.localIP());
}

void loop() {
  switch (Ethernet.maintain()) {
    case 1:
      //renewed fail
      Serial.println("Error: renewed fail");
      break;

    case 2:
      //renewed success
      Serial.println("Renewed success");
      //print your local IP address:
      Serial.print("My IP address: ");
      Serial.println(Ethernet.localIP());
      break;

    case 3:
      //rebind fail
      Serial.println("Error: rebind fail");
      break;

    case 4:
      //rebind success
      Serial.println("Rebind success");
      //print your local IP address:
      Serial.print("My IP address: ");
      Serial.println(Ethernet.localIP());
      break;

    default:
      //nothing happened
      break;
  }
  digitalWrite(POWER_PIN_WATER_SENS, HIGH);
  delay(100);
  waterLevel = analogRead(SIGNAL_PIN_WATER_SENS);
  digitalWrite(POWER_PIN_WATER_SENS, LOW);
  Serial.print("Water: ");
  waterLevel = 1; //debug value
  Serial.println(waterLevel);
  waterLevelString = "waterlevel=" + (String)waterLevel;


    if (client.connect("<snip>", 80)) {
    client.print("GET /arduino_connect_to_db.php HTTP/1.1");
    client.println("Host: <snip>");
    client.println("Content-Type: application/x-www-form-urlencoded");
    client.print("Content-Length: ");
    client.println(waterLevelString.length());
    client.println();
    client.print(waterLevelString);
  }
    if (client.connected()) { 
    client.stop();
  }
    delay(3000);
  }

I believe the problem is in the connection between arduino and php script - There is error i am unaware of. Can you please help me locate it and give solution?

Jacob Mulquin
  • 3,458
  • 1
  • 19
  • 22
Blan_C
  • 27
  • 2
  • I think you can add the waterlevel string to the `GET /arduino_to_db.php` line and remove the headers about Content-Type and Content-Length. – Jacob Mulquin Oct 22 '21 at 20:12
  • 1
    `Content-Type: application/x-www-form-urlencoded` is required for POST requests, not for GET requests – user1597430 Oct 22 '21 at 20:13
  • 1
    you script is **vulnerable** to **sql injection** so use only **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – nbk Oct 22 '21 at 20:18
  • ... or just cast your variable to int. This script is too simple for any long term solution. – user1597430 Oct 22 '21 at 20:29

1 Answers1

0

It looks like you are building the HTTP request incorrectly, try this:

if (client.connect("<snip>", 80)) {
    client.print("GET /arduino_connect_to_db.php?");
    client.print(waterLevelString);
    client.println(" HTTP/1.1");
    client.println("Host: <snip>");
    client.println("User-Agent: arduino");
    client.println("Accept: */*");
}
Jacob Mulquin
  • 3,458
  • 1
  • 19
  • 22