Subject: [CODE4LIB] Thomson Reuters's lawsuit against Zotero

From: Chris Keene <[email-masked]>
Date: Thu, 4 Jun 2009 20:47:44 +0100

I've just seen via the Zotero twitter feed the Thomson lawsuit against 
The Center for History and New Media at George Mason University has been 
dropped

http://quintessenceofham.org/2009/06/04/thomson-reuters-lawsuit-dismissed/

http://twitter.com/zotero/statuses/2033217181

Good news! Thought others may be interested

Chris

---

From: "Kenneth R. Irwin" <[email-masked]>
Date: Fri, 5 Jun 2009 10:30:59 -0400

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.

I.e., don't let this happen to you: http://xkcd.com/327/

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

Any advice would be appreciated.

Thanks!
Ken

---

From: Joe Atzberger <[email-masked]>
Date: Fri, 5 Jun 2009 10:47:09 -0400

Sounds like you might need something like a SQL version of HTML::Scrubber.

The more important thing is to use prepared statements with placeholders, so
that you can't get server execution injected on.  Then worry about javacript
or html scrubbing.

-- 
Joe Atzberger
LibLime - Open Source Library Solutions

On Fri, Jun 5, 2009 at 10:30 AM, Kenneth R. Irwin <[email-masked]>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.
>
> Is this whole-file sanitization any different than the sort of thing you
> might use for individual pieces of data? E.g.

---

From: Joe Hourcle <[email-masked]>
Date: Fri, 5 Jun 2009 11:00:02 -0400

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

> 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.

> 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