When to sanitize PHP & MySQL code before being stored in the database or when its being displayed?

Question

Okay I was wondering when should I sanitize my code, when I add store it in the database or when I have it displayed on my web page or both?

I ask this question because I sanitize my code before it gets stored in the database but I never sanitize when its displayed for the user.

Here is an example of how I sanitize my code before its stored in the database.

$title = mysqli_real_escape_string($mysqli, $purifier->purify(strip_tags($_POST['title'])));
$content = mysqli_real_escape_string($mysqli, $purifier->purify($_POST['content']));

Answer 1

There are distinct threats you are (probably) talking about here:

• You need to sanitize data that's being inserted into the database to avoid SQL injections.
• You also need to be careful with the data that's being displayed to the user, as it might contain malicious scripts (if it's been submitted by other users). See Wikipedia's entry for cross-site scripting (aka XSS)

What's harmful to your database is not necessarily harmful to the users (and vice versa). You have to take care of both threats accordingly.

In your example:

• Use mysqli::real_escape_string() on the data being inserted into your db (sanitizing)

You probably want to use the purifier prior to data insertion - just ensure it's "purified" by the time the user gets it.

You might need to use striplashes() on data retrieved from the db to display it correctly to the user if magic_quotes are on

Answer 2

Rule is thumb is to sanitize ALL user input. Never trust the user.

Answer 3

When you are putting something in the database, you make sure it's safe to put in the database.

When you are about to display something in a browser, you make sure it's safe to display it in the browser.

If you make something browser-safe before you put it in the database, then you are now picking up the habit of trusting that things will be browser-safe when they come out of the database. It's not a good habit to trust user data, even if you're pretty sure you cleaned it previously. Also makes it easy to forget to sanitize before output if you're using someone else's database or code.

Answer 4

I think you would want to escape the input (to avoid SQL injections) and sanitize (to avoid scripting attacks) at the same time, as you're inserting into the database.
This way, you only need to run the sanitizer once on insertion, rather than (potentially) millions of times on display.

Answer 5

You should always encode data when you display it. This way your application can do no wrong. This will protect you from bad data no matter how it came to be.