I am getting a Syntax Error in SQL

Question

Having trouble submitting data to a database because of syntax error.

Database Structure

database: red_fungi
username: fungi_47
password: *******

Table Structure:

columns > type

id          > int(11)       
first_name  > text  
last_name   > text
email       > text
phone       > text
website     > text
description > text

As well as the php code:

<?php
$servername = "localhost";
$username = "fungi_47";
$password = "********";
$dbname = "red_fungi";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Escape user inputs for security

$first_name = mysqli_real_escape_string($link, $_POST['first_name']);
$last_name = mysqli_real_escape_string($link, $_POST['last_name']);
$email = mysqli_real_escape_string($link, $_POST['email']);
$phone = mysqli_real_escape_string($link, $_POST['phone']);
$website = mysqli_real_escape_string($link, $_POST['website']);
$comment = mysqli_real_escape_string($link, $_POST['comment']);
$hosting = mysqli_real_escape_string($link, $_POST['hosting']);


$sql = "INSERT INTO contact (id, first_name, last_name, email, phone, website, description, hosting)
VALUES (NULL, $first_name, $last_name, $email, $phone, $website, $comment, $hosting)";


if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

When submitting, I see that the post has been successful:

first_name=Bill&last_name=Nye&email=bill%40nye.com&phone=8888888888&website=billnyefungi.com&comment=help%20me%20make%20a%20fungi%20website&hosting=yes

but the post response shows the following error:

Error: INSERT INTO contact (id, first_name, last_name, email, phone, website, description, hosting) VALUES (NULL, , , , , , , )
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , , , , )' at line 2

However I've checked the syntax and can't see anything wrong with it. Any ideas what's going wrong?

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Nov 21 '16 at 22:28
at the very least, you need to add quotes around your variables in the query. Just echo out your query and you can see it isn't valid. — Jonathan Kuhn, Nov 21 '16 at 22:30
If you insist on doing your queries this way you will need quotes around each value: `(NULL, '$first_name', '$last_name',...` but I wouldn't do it that way if I were you. — Jay Blanchard, Nov 21 '16 at 22:30
Thanks everyone. I thought you could just escape the strings and it would be safe. I'll take a look at the links! — strasbal, Nov 21 '16 at 22:41

Sarah K · Answer 1 · 2016-11-21T22:56:23.740

Your sql statement needs to look more like this:

$sql = "INSERT INTO `contact` (`id`, `first_name`, `last_name`, `email`, `phone`, `website`, `description`, `hosting`)
VALUES (NULL, '{$first_name}', '{$last_name}', '{$email}', '{$phone}', '{$website}', '{$comment}', '{$hosting}')";

The first thing I do when I have a problem like this is echo out the sql and see if there are obvious problems

and follow up on all the data validation & security points made by other users.

score 1 · Accepted Answer · answered Nov 21 '16 at 22:42

1

Your code is assuming that $_POST['XXX'] will be populated, and it isn't. Thats what all those ,,,,,,,, mean in the error.

Instead, first check if $_POST['XXX'] is created, and has a value prior to using it.

if ((isset($_POST['first_name'])) && (!empty( $_POST['first_name'])) ) {
  //do query and rest of your script

} else { die('Need form input');}

answered Nov 21 '16 at 22:42

Duane Lortie

1,285
1
12
16

Ok, so it wasn't even passing content in. Looking back on the php that makes sense since I didn't pass the forms query in: first_name=Bill&last_name=Nye&email=bill%40nye.com&phone=8888888888&website=billnyefungi.com&comment=help%20me%20make%20a%20fungi%20website&hosting=yes I think I'll have to create a variable in the php to get that data and then parse through it and save it to the database. – strasbal Nov 21 '16 at 22:48
wait.. the URL you (added to your question ?) suggests that your form uses GET method not POST.. If your form uses GET (meaning all values are in the URL) you will need to change all $_POST['X'] to $_GET['X'].. or you could handle both by changing them to $_REQUEST['X'] – Duane Lortie Nov 22 '16 at 00:04
It should be using get but I'll have to double check. Thanks for pointing that out. – strasbal Nov 22 '16 at 20:22

I am getting a Syntax Error in SQL

2 Answers2