10

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();
Community
  • 1
  • 1
user1938304
  • 141
  • 2
  • 8
  • That quote is wrong and dangerously misleading. – SLaks Jan 03 '13 at 23:25
  • 3
    @SLaks I have edited the Wikipedia article to remove the reference to SQL injection, as it's a completely different topic. – cdhowie Jan 03 '13 at 23:27
  • You might find this answer useful too: http://stackoverflow.com/questions/1205889/how-to-prevent-code-injection-attacks-in-php – el_pup_le Jan 03 '13 at 23:31
  • Sanitize while outputting, not while inserting into database. And Use htmlpurifier. It is written for this purpose. – itachi Jan 03 '13 at 23:47
  • @itachi, should be opposite of that. If you didn't sanitize while inputting how would you keep track of what fields need to be sanitized when you do output? Are you going to sanitize every field when outputting, regardless if it was never user supplied? – kittycat Jan 04 '13 at 02:22
  • @cryptic: that indeed is a bigger dilema here. But i prefer keeping data integrity above that. And if you maintain a good design pattern, it isn't very hard to see which fields are coming from user. The downside is ofcourse, if you left even 1 field, you are doomed. – itachi Jan 04 '13 at 04:36

2 Answers2

17

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!

cdhowie
  • 158,093
  • 24
  • 286
  • 300
  • So what should i do when storing the data? If i don't use htmlspecialchars when storing the data, i'm vunurable for XSS, or what? – user1938304 Jan 03 '13 at 23:31
  • @user1938304 When you output the data from the database is when you use it. Read more about what an XSS attack is, and how they are executed and you may understand more. – John V. Jan 03 '13 at 23:33
  • 3
    @user1938304 You don't do anything to the data when you store it (aside from SQL-escaping it, if you are not using prepared queries -- and you should **really** be using prepared queries). The data in the database should be *exactly* what the user entered. When you render that data into an HTML document, *that* is when you use `htmlspecialchars()`. When you render that data into a JSON document, *that* is when you JSON-encode it. – cdhowie Jan 03 '13 at 23:33
  • Okay, i will edit the question to give an exampel for an INSERT statement, to show if i'm doing it correct? But what you saying, i should not use htmlspecial, besides when displaying, maybe with a SELECT? – user1938304 Jan 03 '13 at 23:36
  • @user1938304 You should use `htmlspecialchars()` on the data when rendering it into an HTML document, after fetching it from the database. I'm not sure how I can be any clearer on that point. Based on your example code, you are using this function at the wrong time. Insert the data into the database unescaped. *After* fetching the data, and before you render the content into an HTML document, that is when you escape it. – cdhowie Jan 03 '13 at 23:38
  • Htmlspecial char isn't enough with implementation of different encoding standards. This function does a good job but still leaves a big hole. – itachi Jan 03 '13 at 23:39
  • @itachi It should be sufficient for ISO-5589-1 and UTF-8, I'm pretty sure. – cdhowie Jan 03 '13 at 23:48
  • cdhowie - So what you mean is I should use htmlscpecialchars when im fetching data for display'ing something? But i'm just not sure why to do it at that time? What good does it then? – user1938304 Jan 03 '13 at 23:49
  • @user1938304 Because the goal of `htmlspecialchars()` is to protect users viewing your site, the people you are sending the content to. It does not in *any way* protect your database. – cdhowie Jan 03 '13 at 23:50
  • Sorry, was just confused because of the wiki-quote. That is why wiki is never reliable :) But is protect users viewing my site because if i'm using XML or JSON? – user1938304 Jan 03 '13 at 23:56
  • Okay, but if i'm using prepared staments, is that preventing XSS also, because it will do the escaping for me? Or should i also use bindParam()? – user1938304 Jan 03 '13 at 23:57
  • 1
    Prepared statements do not protect your users from XSS, they protect your database from SQL injection. These are two different topics; dissociate them in your mind. – cdhowie Jan 03 '13 at 23:57
  • So what to do for preventing XSS? – user1938304 Jan 04 '13 at 00:05
  • 2
    @user1938304 Use `htmlspecialchars()` after you fetch the data from the database. The same thing I've told you *at least* three times now. – cdhowie Jan 04 '13 at 00:05
  • Sorry i'm just really confused about this. I'ts a whole new world for me and i just want to be sure of how it is working. But thx a lot for your help. – user1938304 Jan 04 '13 at 00:08
  • @user1938304 If you are still confused and need to talk through stuff, we can use SO chat. – cdhowie Jan 04 '13 at 15:53
  • @cdhowie Hey! Just ran across this thread and was wondering what you thought was the JSON/client side JS equivalent to using htmlspecialchars()? I found that I could use a function found in underscore.js to escape() html entities when displaying. What are your thoughts? Thanks! – user1040259 Apr 09 '14 at 21:41
  • @user1040259 Typically you don't have to worry about escaping when you have a string in JavaScript. For example, jQuery's `.text()` method does not parse HTML, so you don't have to escape anything. The same goes for the vanilla `document.createTextNode()` method. – cdhowie Aug 15 '14 at 14:45
-1

If you use PDO -- with proper used prepared statements --, you dont have to sanitize your input. But to make sure you wont get XSS attacks, I would use htmlspecialchars before ou put it in your DB.

Green Black
  • 5,037
  • 1
  • 17
  • 29
  • 3
    Not before putting it in your database, but when displaying it on your page :) – Jeffrey Jan 03 '13 at 23:27
  • Okay so to be clear: PDO prepared statements to prevent SQL-injections, and htmlspecialchars to prevent XSS? I tried to understand what XSS is it, but i'm not really sure? – user1938304 Jan 03 '13 at 23:28
  • @user1938304 XSS takes advantage of the lack of HTML escaping on sites. For example, your users could submit data that includes ` – cdhowie Jan 03 '13 at 23:30
  • Okay, but if i'm using prepared staments, is that preventing XSS, because it will do the escaping for me? Or should i also use bindParam()? – user1938304 Jan 03 '13 at 23:40