Error with SQL in PHP

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?

[`mysqli_real_escape_string`](http://php.net/manual/en/mysqli.real-escape-string.php) — Praveen Kumar Purushothaman, Sep 21 '16 at 23:02
@PraveenKumar that's not really good advice. Prepared statements would be a much better alternative — Phil, Sep 21 '16 at 23:05

score 0 · Accepted Answer · answered Sep 21 '16 at 23:05

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.

Thank you Praveen :) I will read up on prepared statements as well.. also it is $user on purpose, when the user logs in I am saving their username as $user, the table name is the same as the username :) — Dfarrelly, Sep 21 '16 at 23:16

Error with SQL in PHP

1 Answers1