MySQL sanitation functions

[msaz87]

I'm looking to build a library of data sanitation functions for going into a MySQL database and was looking to see if anyone had suggestions on where to start or if there were any prepackaged functions out there. I've done some searching and I know you need a multitude of functions depending on the kind of data you're sanitizing and what you're doing with it, but I didn't have a lot of luck finding a good starting point.

 

Any suggestions appreciated -- thanks.

[scootstah]

What are you sanitizing for? First ask yourself if sanitizing input is really what you want to do. If you sanitize input, it will forever be in that format. If you decide to change your sanitation methods later on or want to use the data in a different format you may run into problems.

 

Sanitizing output is much more flexible, because the raw data is always kept in the database. For example if you sanitize HTML and save it to the database then you are stuck with that. If you ever wanted to display the HTML you can't.

 

With that being said, what are you sanitizing against? HTML Purifier is probably the best library out there for sanitizing HTML.

[msaz87]

I'm going to be sanitizing various user inputs that will later be displayed and or manipulated against each other and displayed. Some of it will be used to query, so I want to make sure it's safe also. I guess from your perspective I should only sanitize the data coming in that will be used in a query and then sanitize the rest when it's coming out?

[scootstah]

What I usually do is make the data safe to store (so cleanse SQL injections, basically) and that's all the sanitation I do on input. Actually, I don't even do that - because I use prepared statements.

 

Then if I don't want HTML to be displayed on pages, for example, then I can either run it through HTML purifier or just throw htmlspecialchars() at it.

[msaz87]

So my hope was to basically set up two functions, lets call them dataInSanitize() and dataOutSanitize(), and run those on every input/output from my database by the user. Would you say that for the In it's enough to just use something like mysql_real_escape_string() and for the out something like htmlspecialchars()?

[scootstah]

If that's the case, why even bother? Just use those two functions.

 

If you plan to only sanitize input to the point that it is safe to store and query with, then mysql_real_escape_string() is enough (or better yet: prepared statements and you don't even need to sanitize anything on input).

 

There is no magical end-all function for sanitation. htmlspecialchars() will sanitize HTML and make it safe to display HTML from the database to a website without worrying about XSS attacks or people messing with your layout.

 

If that's all you care about, then cool - that's all you need to do.

 

But there is much more to sanitation than that, that you can't solve with 3 lines of code.

[msaz87]

Well the point of those functions is to then stack all the sanitation methods within them so I'm not going back and changing anything outside of the functions. But I also was trying to gauge how much was going to be packed into each function and what considerations to include in it (since not every piece of data will have to sanitize the same way)

[kicken]

Sanitizing and escaping data is always context dependent.  If you have a context that requires a few steps to sanitize something you may want to consider creating a nice function to wrap it all up in one call, but from what you've said so far all you'd really be doing is aliasing mysql_real_escape_string and htmlentities.  Those are about all you need to ensure data is safe for a query and safe for display on a web page.  The only other output sanitation you may need to use is urlencode, but only when putting data into a link.

 

I really see no point to creating a library of functions.  Everything you need is already made in about the simplest api as one could have.

[msaz87]

Sanitizing and escaping data is always context dependent.  If you have a context that requires a few steps to sanitize something you may want to consider creating a nice function to wrap it all up in one call, but from what you've said so far all you'd really be doing is aliasing mysql_real_escape_string and htmlentities.  Those are about all you need to ensure data is safe for a query and safe for display on a web page.  The only other output sanitation you may need to use is urlencode, but only when putting data into a link.

 

I really see no point to creating a library of functions.  Everything you need is already made in about the simplest api as one could have.

 

Thanks -- I think that sounds reasonable. My main concern was to make sure I didn't miss anything before I got too far into the project and give myself the easiest way to add more protection down the road.