Is using htmlspecialchars() for input/output HTML sanitization, for MySQL database bad design? [duplicate]

Question

Possible Duplicate:
What are the best PHP input sanitizing functions?

Is using htmlspecialchars() for input/output HTML sanitization, for MySQL database bad design?

Should you instead just not allow these "dangerous" signs because it still will show b-tags,i-tags and others? And how to do so?

I'm asking because it says on wiki http://en.wikipedia.org/wiki/HTML_sanitization

"HTML sanitization can be used to protect against cross-site scripting and SQL injection attacks by sanitizing any HTML code submitted by a user."

So besides using PDO prepared statements, to prevent SQL-injections, i want to use this htmlspecialchars for all input and output. But maybe I should use something else?

Is this a good way to do an insert statement for instance?:

$type= htmlspecialchars($_POST['animaltype']);
$name= htmlspecialchars($_POST['animalname']);
$age= htmlspecialchars($_POST['animalage']);        
$descr= htmlspecialchars($_POST['animaldescription']);
$foto= htmlspecialchars($_POST['animalfotourl']);
$date=htmlspecialchars($_POST['animalhomelessdate']);



$sqlquery  = "INSERT INTO animals_tbl(animaltype, animalname, animalage, animaldescription, animalfotourl, animalhomelesssince) VALUES (':type',':name',':age',':descr', ':foto', ':date')";


$stmt = $conn->prepare($sqlquery);
$stmt->bindParam(':type',$type, PDO::PARAM_STR);
$stmt->bindParam(':name',$name, PDO::PARAM_STR);
$stmt->bindParam(':age',$age, PDO::PARAM_INT);
$stmt->bindParam(':descr',$descr, PDO::PARAM_STR);
$stmt->bindParam(':foto',$foto, PDO::PARAM_STR);
$stmt->bindParam(':date',$date, PDO::PARAM_STR);

$stmt->execute();

Answer 1

htmlspecialchars() is sufficient to escape text for browsers. This will protect other site users from XSS attacks.

However, I would only run this function when displaying data. Storing escaped content in a database seems like poor design to me. The database should store actual content, not munged content. Escape things as necessary at each layer, and no sooner.

---

To illustrate why this is a bad idea, consider a web site that is working on implementing a JSON-driven API. If they are storing HTML-encoded data in their database, they have two choices: (a) have HTML-encoded data in their JSON responses (which makes no sense), or (b) decode the HTML back to its original form before JSON-encoding it. Both choices are sub-optimal.

Data goes in the database, JSON strings go in JSON documents, and HTML-encoded data goes in HTML documents. Don't mix them!