How can you query a SQL database for malicious or suspicious data?

Lately I have been doing a security pass on a PHP application and I've already found and开发者_JAVA技巧 fixed one XSS vulnerability (both in validating input and encoding the output).

How can I query the database to make sure there isn't any malicious data still residing in it? The fields in question should be text with allowable symbols (-, #, spaces) but shouldn't have any special html characters (<, ", ', >, etc).

I assume I should use regular expressions in the query; does anyone have prebuilt regexes especially for this purpose?

If you only care about non-alphanumerics and it's SQL Server you can use:

SELECT *
FROM MyTable
WHERE MyField LIKE '%[^a-z0-9]%'

This will show you any row where MyField has anything except a-z and 0-9.

EDIT:

Updated pattern would be: LIKE '%[^a-z0-9!-# ]%' ESCAPE '!'

I had to add the ESCAPE char since you want to allow dashes -.

For the same reason that you shouldn't be validating input against a black-list (i.e. list of illegal characters), I'd try to avoid doing the same in your search. I'm commenting without knowing the intent of the fields holding the data (i.e. name, address, "about me", etc.), but my suggestion would be to construct your query to identify what you do want in your database then identify the exceptions.

Reason being there are just simply so many different character patterns used in XSS. Take a look at the XSS Cheat Sheet and you'll start to get an idea. Particularly when you get into character encoding, just looking for things like angle brackets and quotes is not going to get you too far.