Using htmlspecialchars function with PDO prepare and execute

Is converting special characters to HTML entities in form validation and database query using PHP PDO using htmlspecialchars() function really necessary?

For example, I have a website with simple login system more or less like:

$username = (string) htmlspecialchars($_POST['user']);
$password = (string) htmlspecialchars($_POST['pass']);

$query = $dbh->prepare("select id where username = ? and password = ?")
$query->execute($username, $password);

Note that I also use type casting besides the function in question.. So, is it necessary? Or I can safely use $username = $_POST['user']; ?

Solution 1:

Your confusion is quite common because information and examples in books and on the internet including php.net are misleading or ambiguous. The most important thing you can learn when developing web apps is filter input, escape output.

Filter Input This means that for any data input whether provided by a user on a form or provided by a file from some other source, filter out anything which does not belong. An example would be that if you expect a numeric value, filter out any non-numeric characters. Another example would be limit/ensure the maximum length of data. However, you don't need to get to crazy with this. For example, if you expect a line of text that can contain literally any combination of characters, then trying to come up with a filter will probably only frustrate your users.

So, you generally would store input data in your database as provided with optionally some filtering before hand.

Escape Output What is meant by escape output is to properly make safe the data for a given media. Most of the time, this media is a web page (html). But, it can also be plain text, xml, pdf, image, etc. For html, this means using htmlspecialchars() or htmlentities() (you can read up on the differences here). For other media types, you would escape/convert as appropriate (or not at all if appropriate).

Now, your question is whether or not you should use htmlspecialchars() on input data that will be used as sql query parameters. The answer is no. You should not modify the data in any way.

Yes, the data contained in $_POST should be considered dangerous. Which is why you should 1) guard against sql injection using prepared statements and bound parameters as you are doing and 2) properly escape/convert data found in $_POST if you place it in html.

There are many frameworks for PHP which handle these details for you and I recommend you pick and use one. However, if you do not, you can still build a safe and secure application. Whether you use a framework or not, I strongly suggest that you read the recommendations suggested by OWASP. Failure to do so will only result in a security nightmare for your web application.

Solution 2:

You should use htmlspecialchars when you have some plain text (such as user input, or user input that you previously stored in a database and just took out of it with a SELECT, or text fetched via HTTP from a third party, etc, etc) and you want to insert it into an HTML document. This protects you against XSS.

In general, you should not use it when inserting data into a database (a database is not an HTML document). You might want to use it in some non-HTML form later.