PHP Script SQL Error $1064 Problem

[santafe]

I have been trying to resolve this SQL error for longer than I care to admit.

Here is the PHP script:

<?php
$temperature = $_POST['temperature'];	// nwas $_GET[]
$humidity 	 = $_POST['humidity']; 		// nwas $_GET[]

$dbname = 'pabriles_example';
$dbuser = 'pabriles_admin';  
$dbpass = '###.###.###'; 
$dbhost = 'www.redristracards.com'; 

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

if ($connect->connect_error){

	die("Connection failed: $sql");	// my code ...
} else {
	echo "YES!!  We connnected!<br><br>";
}

$sql = "INSERT INTO iot_project(temperature, humidity) VALUES ('$temperature', '$humidity')";

if ($connect->query($sql) === TRUE) {
	echo "ADDED: ".$temperature.", ".$humidity;
	} else {
		echo "ERROR!!!: ".$sql."<br>".$conn->error;
	}
$connect->close(); 
?>

My serial monitor shows that I have indeed connected to the database as shown next:

And here is the SQL error message:

I have tried using various SQL syntax checkers and each regurgitate the same exact message which is no help. I have tweaked the ‘INSERT INTO …’ sql code line several ways and nothing helps. This leads me to believe that the real error has nothing to do with this line. I am going to delete the db table and re-install it and see if that helps. I have no idea what else to try.

ANY HELP will be greatly appreciated.

Thank you!

[mabismad]

The use of $conn->error in your query error handling logic is incorrect. The connection is not in $conn. This would be producing a php error and it is not reporting the actuall sql error you are getting when your code is running the query.

The first step you need to do is find the php.ini that php is using and set error_reporting to E_ALL and set display_errors to ON. Stop and start your web server to get any changes made to the php.ini to take effect and then use a phpinfo() statement in a .php script to check to make sure that these settings were actually changed to the intended values.

Also, don’t use @ error suppressors in any code, ever. When learning, developing, and debugging code/query(ies), you should display all php errors, so that you get immediate feedback as to any problems. When on a live/public server, you should log all errors, so that you will know they are occurring at all and can find an fix what’s causing them.

Next, simplify your life. Use exceptions for database statement errors and in most cases simply let php catch and handle the exception. This will let you remove all the error handling logic you have now, simplifying your code and preventing hackers from getting useful information when they intentionally trigger errors. The exception to this rule is when inserting/updating duplicate or out of range user submitted data. In this case, your code should catch the database exception, detect if the error number is for something that your code is designed to handle, then setup and display a message for the user telling them what was wrong with the data. For all other error numbers, simply re-throw the exception and let php handle it.

In most cases, there’s no need to free results, close prepared queries, or close database connections since php will automatically do this when your script ends.

As to the result from running the query in phpadmin/sql checkers, that’s not the query being used in your php code. It doesn’t have the single-quotes around the (empty) values.

Use a prepared query when supplying external, unknown, dynamic values to a query when it gets executed. In addition to providing protection against sql special characters in data from breaking the sql query syntax (which is how sql injection is accomplished), this actually simplifies the sql syntax, and provided you use the much simpler PDO extension, simplifies the php code.

Don’t copy variables to other variables for nothing. This is just a waste of typing.

Lastly, your code is not detecting if a post method form was submitted before referencing any of the form data and you are not validating the data before using it, which is why you are using empty data values in the sql query.

[santafe]

I cannot sufficiently express my gratitude for your in-depth post. It is going to take me some time to get my head around all that you have written.

Just now I came across some help that I think mirrors all that you have noted.

I sincerely appreciate your help. I will be sure to report back with my progress.

Thank you!

[santafe]

For the sake of clarity I would like to point out that the temperature and humidity values noted in my posted php script are coming from an Arduino microcontroller. I have read several turorials that seem to be able to insert this sensor data into a dB table with ease.

I have never been able to duplicate any of these results. My hardware is different than others so this may be part of my problem. However, the php/sql scripting must be correct and I get that.

Thanks again for your help.

[mabismad]

Having php’s error related settings set to either display or log all php errors, having useful error handling for all the database statements that can fail - connection, query, prepare, and execute, detecting if a post method form was submitted before referencing any of the form data, and validating the data before using it, will give you code that will either work or it (php, your code, database server) will tell you why it isn’t working, without producing unnecessary errors.

If you are using the msyqli extension, the following example code (untested), showing the points that were given, should work -

<?php

$dbname = 'pabriles_example';
$dbuser = 'pabriles_admin';  
$dbpass = '###.###.###'; 
$dbhost = 'www.redristracards.com'; 

// use exceptions for database statement errors
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// name the db connection variable as to what it is being used for
$mysqli = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);
// always set the character set that php is using to match your database table(s)
mysqli_set_charset($mysqli,'utf8mb4');

$errors = []; // an array to hold validation/user error messages
$post = []; // an array to hold a trimmed working copy of the form data

// post method form processing
if($_SERVER['REQUEST_METHOD'] == 'POST')
{
	//  inputs - 'temperature','humidity'

	// trim all the data at once
	$post = array_map('trim',$_POST);
	
	// validate the input data here, storing errors in $errors, using the field name as the main array index
	// at a minimum, the data should not be empty
	if($post['temperature'] === '')
	{
		$errors['temperature'] = 'The temperature is empty.';
	}

	if($post['humidity'] === '')
	{
		$errors['humidity'] = 'The humidity is empty.';
	}

	// if no errors, use the submitted data
	if(empty($errors))
	{
		$sql = "INSERT INTO iot_project (temperature, humidity) VALUES (?,?)";
		$stmt = $mysqli->prepare($sql);
		$stmt->bind_param('ss',$post['temperature'],$post['humidity']);
		$stmt->execute();		
	}
	// if you are at this point, the data was inserted
	echo "ADDED: {$post['temperature']}, {$post['temperature']}";
}

// display any errors
if(!empty($errors))
{
	echo implode('<br>',$errors);
}

[santafe]

Thank you once again for your php script.

I have used it and the error message has indeed vanished. At the moment, the sensor data is not being entered into the database. I am working on this and will get back to you.

I sincerely appreciate your help.

P.S. the

echo "ADDED: {$post['temperature']}, {$post['temperature']}";

has not executed yet so I am not at this point yet. Will report back when I discover this problem.

Thanks!

[mabismad]

The most immediate problem is most likely that a post method request is not being used to send the data.

You can display (print_r/var_dump) $_SERVER['REQUEST_METHOD'] and $_POST near the start of the php script to see what they are.

[santafe]

Here is the pertinent Arduino IDE code that I am using:

float temperature = dht.readTemperature(); // Read the sensor temperature ...
float humidity = dht.readHumidity();             // Read the sensor humidity ...

client.print(String("POST http://redristracards.com/mabismad.php") + 
                          ("&temperature=") + temperature +
                          ("&humidity=") + humidity +
                          " HTTP/1.1\r\n" +
                 "Host: " + server + "\r\n" +
                 "Connection: close\r\n\r\n");

Note the ‘mabismad.php’ in the client.print string - that is your php script file. The original line was using a GET, but I changed it to POST a few days back.

I hope this code makes some sense to you.

Thanks!

[mabismad]

@santafe

The original line was using a GET

For a GET request, the query-string (data) is concatenated on the end of the url path/file. For a POST request, the query string is sent separately, after the Connection: close is output.

Edit: also, the initial character in the query-string should be a ? not an &

[santafe]

Thanks for your post. I will make these changes and get back to you.

I sincerely appreciate your help.