PHP Insert into MySQL syntax error [closed]

问题:

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);
?>

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

回答1:

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?

回答2:

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

回答3:

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 `