dfarrelly dfarrelly - 2 months ago 7
MySQL Question

Error with SQL in PHP

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

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.