Search code examples
phpmysqlsqlmysqlimysql-real-escape-string

php mysqli escape string error in sql syntax

I am trying to escape fields posted from a form. I can successfully insert into the SQL database by commenting out the code that escapes the string.

The error received is:

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 '\"test\",\"0123456789\",\"test@test.com\",\"1\",\"1\",\"fgsdfdfndfndfndfndfndfn\' at line 1

Here is the code I am using:

$Name= $_POST['fullname'];
$Phone = $_POST['phone'];
$email = $_POST['email'];
$inBuilding = $_POST['inbuilding'];
$floor = $_POST['floor'];
$inRoom = $_POST['inroom'];
$majorDescription = $_POST['majorcategory'];
$description = $_POST['desc'];

$query = "INSERT INTO `problem`.`reports` (`Name`, `PhoneNumber`, `EmailAddress`, `inBuilding`, `inRoom`, `Description`, `MajorDescription`) VALUES (";
$query .= '"' . $Name. '","' . $Phone . '","' . $email . '","' . $inBuilding . '","' . $inRoom . '","' . $description . '","' . $majorDescription . '");';


$query = mysqli_real_escape_string($connect, $query);

I have also tried:

$query = mysqli_escape_string($connect, $query);

with the same error.

According to other examples on stack overflow I changed the INSERT INTO code to the following:

$query = "INSERT INTO `problem`.`reports` (Name, PhoneNumber, EmailAddress, inBuilding, inRoom, Description, MajorDescription) VALUES ('$Name', '$Phone', '$email', '$inBuilding', '$inRoom', '$description', '$majorDescription')");

This code gave server 500 error.

MySQL is fully updated.

Any assistance appreciated!

MikeW's solution worked. Also realized I was trying to escape the string before I had opened the database making mysqli_real_escape_string return null. Connecting to the database first, ($connect= new connect("server","user","password");) solved this problem. Hopefully this will help anyone else with the same problems.

Solution

  • You should be using single quotes, not double quotes. Also, mysqli_real_escape_string() should be called on each variable, not on the query as a whole. You should get something like this:

    $Name= mysqli_real_escape_string($connect, $_POST['fullname']);
// more variables, similarly escaped.

$query = "INSERT INTO `problem`.`reports` (`Name`, `PhoneNumber`, `EmailAddress`, `inBuilding`, `inRoom`, `Description`, `MajorDescription`) VALUES (";
$query .= "'$Name','$Phone','$email','$inBuilding','$inRoom','$description','$majorDescription')";

    However, for this sort of query you should consider using prepared statements.