PHP Insert into MySQL syntax error

Question

I'm having trouble submitting some data to MySQL via php, i get the following error:

"Error: 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 ''student' (UID, FirstName, LastName, DOB, EmergencyContact, Address, City, State' at line 1"

I'm not sure where i've gone wrong, any help would be great.

<?php
$firstname = $_POST['firstname'];
$lastname = $_POST['lastname'];
$dob = $_POST['dob'];
$emergencycontact = $_POST['emergencycontactperson'];
$address = $_POST['addressline1'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$homephone = $_POST['homephone'];
$cellphone = $_POST['cellphone'];
$guardian = $_POST['guardian'];
$inneighborhood = 0;
if ($zip == "49503")
    $inneighborhood = 1;

$con = mysqli_connect("localhost", "cookarts_root", "password", "cookarts_database");

// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$sql = "INSERT INTO 'student' (FirstName, LastName, DOB, EmergencyContact, Address,       City, State, ZIP, CellPhone, HomePhone, Guardian, InNeighborhood)
VALUES
($firstname', '$lastname', '$dob', '$emergencycontact', '$address', '$city', '$state', '$zip', '$cellphone', '$homephone', '$guardian', '$inneighborhood')";

if ($con->query($sql) === TRUE) {
      echo 'users entry saved successfully';
}   
else {
  echo 'Error: '. $con->error;
}

mysqli_close($con);
?>

enter image description here

If you need more info i'd be happy to provide it, thanks again for the help.

There are SQL injection vulnerabilities in your code, use `mysqli_real_escape_string` before concatenating POST variables to your query or, better, use parametrized queries. — Marcel Korpel, Mar 23 '13 at 16:15

score 3 · Accepted Answer · edited May 23 '17 at 12:21

3

TableNames are Identifiers so they should not be quoted with single quotes. Since the tableName you've used is not a reserved keyword, you can simply remove those wrapping quotes around,

INSERT INTO student (FirstName, LastName....

When you wrap something with single quotes, it forces that object to become a string literal. So when identifiers are wrap with single quotes, it means that they are not identifier anymore.

The server throws an exception because INSERT INTO expects an identifier not string literal.

When you have accidentally used a table name which is a MySQL Reserved Keyword, don't use single quote to delimit the identifier but with backticks.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:21

Community

1
1

answered Mar 23 '13 at 16:14

John Woo

258,903
69
498
492

1

True, if he wan's to quote it he should use the `\`` – Niels Mar 23 '13 at 16:14
1

And you missed a quote at VALUES ($firstname' – chill0r Mar 23 '13 at 16:15
oops... you have already answered that – alwaysLearn Mar 23 '13 at 16:17
what error message you get? – John Woo Mar 23 '13 at 16:19
read @chill0r 's comment. You have also missed to wrap `$firstname` with quotes. – John Woo Mar 23 '13 at 16:24
1

Thank you everyone for your help, after making the syntax changes from above: don't put quotes around table names and the missing "'" before $firstname i was still getting a similar error. After investigation i found that the UID on the table didn't have the auto increment attribute so my sql query was technically missing data. Thanks you again for the help, and my next task is to prevent sql injection. – Staleyr Mar 23 '13 at 16:45

score 2 · Answer 2 · answered Mar 23 '13 at 16:16

change your query to

$sql = "INSERT INTO student (FirstName, LastName, DOB, EmergencyContact, Address,                City, State, ZIP, CellPhone, HomePhone, Guardian, InNeighborhood)
    VALUES
   ('$firstname', '$lastname', '$dob', '$emergencycontact', '$address', '$city', '$state', '$zip', '$cellphone', '$homephone', '$guardian', '$inneighborhood')";

dont use quotes around table names

score 1 · Answer 3 · answered Mar 23 '13 at 16:18

1

Looks like you are missing a few things too:

You Have:
($firstname',

Should be: (missing ' at beginning)
('$firstname',

But even then, you may want to use " (quotes) and escape them like so:

(\"$firstname\",

You also have a lot of space between address and city on your INSERT INTO line... Address, City

And yes, you should escape your mysql field names with a `

answered Mar 23 '13 at 16:18

rckehoe

1,198
15
16

Why would you want to use double quotes and backslashes? – Dan Bracuk Mar 23 '13 at 16:32

PHP Insert into MySQL syntax error

3 Answers3

Linked