Do I need htmlentities() or htmlspecialchars() in prepared statements?

Question

In an article http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html, it says the followings:

There are numerous advantages to using prepared statements in your applications, both for security and performance reasons.

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack.

Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters.

This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

Does this mean I don't need htmlentities() or htmlspecialchars()? But I assume I need to add strip_tags() to user input data? Am I right?

Answer 1

Nothing changes for htmlspecialchars(), because that's for HTML, not SQL. You still need to escape HTML properly, and it's best to do it when you actually generate the HTML, rather than tying it to the database somehow.

If you use prepared statements, then you don't need mysql_[real_]escape_string() anymore (assuming you stick to prepared statements' placeholders and resist temptation to bypass it with string manipulation).

If you want to get rid of htmlspecialchars(), then there are HTML templating engines that work similarily to prepared statements in SQL and free you from escaping everything manually, for example PHPTAL.

Answer 2

htmlentities and htmlspecialchars are used to generate the HTML output that is sent to the browser.

Prepared statements are used to generate/send queries to the Database engine.

Both allow escaping of data; but they don't escape for the same usage.
So, no, prepared statements (for SQL queries) don't prevent you from properly using htmlspecialchars/htmlentities (for HTML generation)

About strip_tags: it will remove tags from a string, where htmlspecialchars will transform them to HTML entities.
Those two functions don't do the same thing; you should choose which one to use depending on your needs / what you want to get.

For instance, with this piece of code:

$str = 'this is a <strong>test</strong>';
var_dump(strip_tags($str));
var_dump(htmlspecialchars($str));

You'll get this kind of output:

string 'this is a test' (length=14)
string 'this is a <strong>test</strong>' (length=43)

In the first case, no tag; in the second, properly escaped ones.

And, with an HTML output:

$str = 'this is a <strong>test</strong>';
echo strip_tags($str);
echo '<br />';
echo htmlspecialchars($str);

You'll get:

this is a test
this is a <strong>test</strong>

Which one of those do you want? That is the important question ;-)

Answer 3

I would still be inclined to encode HTML. If you're building some form of CMS or web application, it's easier to store it as encoded HTML, and then re-encode it as required.

For example, when bringing information into a TextArea modified by TinyMCE, they reccomend that the HTML should be encoded - since the HTML spec does not allow for HTML inside a text area.

I would also strip_tags() from anywhere you don't want HTML code.

Answer 4

You don't need htmlentities() or htmlspecialchars() when inserting stuff in the database, nothing bad will happen, you will not be vulnerable to SQL injection if you're using prepared statements. The good thing is you'll now store the pristine user input in your database.

You DO need to escape stuff on output and sending it back to a client, - when you pull stuff out of the database else you'll be vulnerable to cross site scripting attacks, and other bad things. You'll need to escape them for the output format you need, like html, so you'll still need htmlentities etc.

For that reason you could just escape things as you put them into the database, not when you output it - however you'll lose the original formatting of the user, and you'll escape the data for html use which might not pay off if you're using the data in different output formats.