douziqian2871
douziqian2871
2014-01-28 00:07

php mysqli sql语法中的转义字符串错误[关闭]

已采纳

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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • douji8033 douji8033 7年前

    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.

    点赞 评论 复制链接分享
  • dongzhanlu0658 dongzhanlu0658 7年前

    I'm not sure if MySQL works with double-quotes. You should use single-quotes. But the more glaring issue is that you need to call mysqli_real_escape_string() on every variable, not the entire query string.

    To simplify the problem, say your query was as follows:

    $query = "INSERT INTO tbl (Name) VALUES ('". $_POST['name'] ."')";
    $query = mysqli_real_escape_string($connect, $query);
    

    And then say I pass in a value, Michael O'Connor. What does your query become?

    INSERT INTO tbl (Name) VALUES (\'Michael O\'Connor\')
    

    Notice that not only did the ' in the actual name get escaped, but the quotes to surround that name also got escaped. If you called mysqli_real_escape_string() on the entire compiled query string, it has no way to distinguish a ' in the value vs. the ones that are supposed to surround the value.

    点赞 评论 复制链接分享