On Fri, 5 Jun 2009, Kenneth R. Irwin wrote:

> Hi folks,
>
> Can someone point me to some good information/how-to-guide/etc for 
> sanitizing files uploaded to a MySQL database through a web interface? 
> (This would be something much like the "Insert data from a textfile into 
> table" function in phpMyAdmin.) I want to make sure there aren't any 
> nasty queries inserted into the tab-delimited data.

Write it out to disk, and then use the 'LOAD DATA LOCAL INFILE' command, 
so you don't have to worry about escaping the values:

 	http://dev.mysql.com/doc/refman/5.1/en/load-data.html

You'll only run into problems if you're generating SQL commands as 
strings, and then sending those.  (and if you're using prepared 
statements, , you'll never need to worry about bad characters in values 
... if you're generating strings that have field or table names in them, 
check them against a list of known good values (/\A[a-zA-z0-9_]+\Z/) and 
reject any that aren't compliant.


> Is this whole-file sanitization any different than the sort of thing you 
> might use for individual pieces of data? E.g. 
> http://www.denhamcoote.com/php-howto-sanitize-database-inputs

Okay -- the issue with people trying to do XSS attacks and/or insert 
javascript can be an issue ... but the suggestions about escaping 
characters  is useless -- use prepared statemenst with placeholders.  As 
you're using MySQL and PHP, see:

 	http://dev.mysql.com/tech-resources/articles/guide-to-php-security-ch3.pdf

To deal with malicious inserted HTML, it may be slower, but I deal with it 
on output -- as there may be multiple ways for data to get in, I sanitize 
the strings before emitting them.  (and I may use different sanitizing 
depending on how it's being emitted ).

And don't use the regexes from the page you linked to -- because of the 
order they strip out the tags, they're going to screw up.  (they'll never 
match style tags as they removed them the step before; also, they need to 
SGML remove comments before removing any other tags, but their regex for 
SGML comments is flawed)

-----
Joe Hourcle