Is it good to use htmlspecialchars() before Inserting into MySQL?

Question

I am a little confused on this. I have been reading about htmlspecialchars() and I am planning to use this for the textareas POST to prevent XSS attack. I understand that usually htmlspecialchars() are used to generate the HTML output that is sent to the browser. But what I am not sure is:

1) Is it a safe practice to use htmlspecialchars() to the user input data before I insert it into MySQL? I am already using PDO prepared statement with parameterized values to prevent SQL Injection.

2) Or, I really dont need to worry about using htmlspecialchars() to inserted values (provided they are parameterized) and only use htmlspecialchars() when I fetch results from MySQL and display it to users?

Answer 1

As others have pointed out, #2 is the correct answer. Leave it "raw" until you need it, then escape appropriately.

To elaborate on why (and I will repeat/summarise the other posts), let's take scenario 1 to its logical extreme.

What happens when someone enters " ' OR 1=1 <other SQL injection> -- ". Now maybe you decide that because you use SQL you should encode for SQL (maybe because you didn't use parameterised statements). So now you have to mix (or decide on) SQL & HTML encoding.

Suddenly your boss decides he wants an XML output too. Now to keep your pattern consistent you need to encode for that as well.

Next CSV - oh no! What if there are quotes and commas in the text? More escaping!

Hey - how about a nice interactive, AJAX interface? Now you probably want to start sending JSON back to the browser so now {, [ etc. all need to be taken into consideration. HELP!!

So clearly, store the data as given (subject to domain constraints of course) and encode appropriate to your output at the time you need it. Your output is not the same as your data.

I hope this answer is not too patronising. Credit to the other respondents.