What's the best, Escape then store Or store then escape the output?

Question

Hi, After doing a long search on stackoverflow i didn't find any one talked about this even if it's a big choice, the Question is what's the best in order to prevent both of XSS and SQL injection, Escaping the data then store it in the DB or Store it as it is and escape when output it?

Note: it is better if you give some examples of practics if possible.

Thanks

Answer 1

Escape input, store, then escape output.

---

If you store without escaping, you're vulnerable to SQL injection.

Example: You have a query:

mysql_query("SELECT * FROM `table` WHERE `abc`= '{$_POST['def']}';

Let's say that $_POST['def'] is equal to

blah'; DROP TABLE `table`; SELECT * FROM `table` WHERE 'abc' = '123

That will cause your table to be dropped if it's not escaped.

---

If you output without escaping, you're vulnerable to XSS.

Otherwise, users can inject harmful Javascript into pages other users can view.

Answer 2

The data must be properly SQL-escaped (or sent separately from the SQL, as others suggest) for storage, and HTML-escaped for display.

Answer 3

Your question doesn't make much sense, because the very act of trying to store data containing an SQL injection is what causes the SQL injection.

Either way, you should be using Parameterized queries to prevent SQL injection.

For XSS/HTML escaping, I'd personally rather do it at insertion-time, because then you only have to do that processing once, instead of every time it's displayed. A small optimization, but an easy one.

Answer 4

In order, you should do the following -

1. Validate the input to see if it meets your expectation. If it doesn't, reject the input and stop. If it meets, continue to next step without altering the input.

2. Bind the input to a parameterized query, or escape the input as you are forming the query. Note that escaping the input does not alter the input. The database will always contain the exact string the user entered.

3. When displaying to the user, you have to escape it according to the context. There are around 5 distinct ways in which the same string can be escaped - depending on whether you are displaying it in HTML element, HTML attribute, Javascript, CSS, or as a URL. See http://www.owasp.org/index.php/XSS_%28Cross_Site_Scripting%29_Prevention_Cheat_Sheet. Again, remember that escaping doesn't alter the string. The user must always see the exact string he had entered.

You may be tempted to store a modified string in the database - but please don't do so. If you escape it for HTML, you can never use the string in javascript. If you have to do back-end processing, you'd have to de-escape the string. You will soon reach a stage where you can't do the right thing anymore.

Remember that escaping is just a way to transport data from one layer to another. At rest (database or screen), the data should look exactly the way the user entered it.