PHP中的SQL出错

Question

Hey guys so I have a form with two text inputs, when users fill it in, the data is inserted to a database like this..

$sql = "INSERT INTO $user (note_name, note_body, creation_date)
        VALUES ('$name','$note','$date')";

However I am having a problem where when an apostrophe is entered as part of the text input, I get "Error in SQL syntax".. I believe that it is taking the apostrophe as part of the SQL query, right? So say if I enter "Bob's Computer" for the $note variable, the apostrophe in "Bob's" is closing of the apostrophe's around the variable?

Is there any way to resolve this?

Answer 1

You might need to sanitise your data before putting it as query. The sanitisation will avoid such issues, even if the input is malicious. You need to use mysqli_real_escape_string on the variables this way:

$name = mysqli_real_escape_string($conn, $name);
$note = mysqli_real_escape_string($conn, $note);
$date = mysqli_real_escape_string($conn, $date);
$sql = "INSERT INTO `user` (`note_name`, `note_body`, `creation_date`) VALUES ('$name','$note','$date')";

Also, it is always good to put your SQL query like above way, inside the backticks. I also feel that there is an issue with the table being user and not $user?

Note: Prepared statements are really better than using this function. Since I am not sure about the usage, I am not adding it in my answer.