0

I have looked at a bunch of different sites and I really don't want to spam y'all. But I'm really desperate and have nowhere else to turn. So here goes...

I am trying to let people post things on my website. And the tutorial I watched told me to use htmlspecialchars() so that people can post things such as greater/less than signs and special symbols and such without breaking anything. But whenever my mysql_query() encounters a single or double quote it throws this error:

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 't do this. I won' at line 1

Here is my corresponding code:

$sql = mysql_query("INSERT INTO stories (title, content, username, date_posted) VALUES ('$title', '$content', '$un', now())") or die(mysql_error());

And here's where I used the htmlspecialchars():

$content = $_POST['content'];
$content = nl2br(htmlspecialchars($content));

What am I supposed to do to fix this? Please help me.

-Sam

nitrous
  • 1,677
  • 4
  • 15
  • 18
  • it seems like the quotes from your content could be causing the error – BuddhistBeast Jan 04 '14 at 21:05
  • 1
    `htmlspecialchars()` is not used for database escaping. You need to [review this question on preventing SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). It will solve 3 problems for you. 1) deprecation of `mysql_*()`, 2) SQL injection vulnerability, 3) correct escaping. – Michael Berkowski Jan 04 '14 at 21:05
  • By the way, whatever tutorial you used which advised `htmlspecialchars()` in a database query, you should abandon it. That is not good information. Its purpose is to escape values on _output to HTML_, but input to a database should always be in its original form. – Michael Berkowski Jan 04 '14 at 21:07
  • `mysql` is outdated, please consider using `mysqli` or `pdo` instead. – CodeTrooper Jan 04 '14 at 21:07

1 Answers1

1

htmlspecialchars() encodes HTML entities in strings like:

  • '&' (ampersand) becomes & (taken from PHP wiki)

However, SQL may not like some of the characters that are encoded, therefore you must mysql_real_escape_string() the $content as well.

Keep in mind however that MySQL is deprecated as of PHP 5.5.0, thus I suggest you to use MySQLi or PDO.

For more information: http://php.net/mysql_real_escape_string

In order to have you stuff again written in proper HTML once you load it, you must use htmlspecialchars_decode().

GiamPy
  • 3,543
  • 3
  • 30
  • 51