I've recently become aware of the possibility that a database can be compromised by a process called SQL Injection.

I've read a lot about it, both on php.net and mysql.com (in addition to other sites), and am aware of the basics of how it can occur. However, there seems no unified solution (using PHP) suggested by all the sites.

The article in:  http://www.sitepoint.com/article/php-anthology-3-php-mysql/7 suggests that guarding against a second query being added to your pages (by terminating your query with a ; and then the hacker writing his own query) is not neccessary as it says "No doubt many PHP developers have been saved from the worst SQL injection attacks by the limitations of MySQL, which will only allow a single SQL statement to be performed with each call to mysql_query"

However the information on php.net and mysql.com does not seem to reflect this. (See their articles on SQL Injection at http://uk.php.net/manual/en/security.database.sql-injection.php  and http://dev.mysql.com/doc/mysql/en/Security_guidelines.html ).
In fact there is no mention of MySQL being limited to only one query at a time when using mysql_query() (see http://uk.php.net/manual/en/function.mysql-query.php )

Php.net suggests using the addslashes() function to effectively escape the most dangerous characters such as  ' " NUL but does not seem to deal with the ; character, which according to php.net is a valid character to use in SQL Injection attacks. Also I am loath to fill my database with slashes when an unfortunate user has the name O'Neill, and ends up being renamed to O\'Neill when using my system.
Mysql_real_escape_string() is another PHP function often proposed as a solution, but again it does not deal with the ; character.

I'm quite confused, and can see that there may not be a quick and easy answer to this!

Cheers,  Jim





I'm sitting in an Internet cafe in London so don't have all my resources to hand BUT ...

I would personally suggest tightly filtering user inputs before you insert them into MySQL queries.  Either check them against regular expressions or use functions like strspn to look for illegal characters.   Might be far more testing that you need but, damn it, if you've asked someone to enter their name what characters do you need to accept in addition to letters, spaces, hyphens and perhaps a correctly escaped single quote for the O'Malleys of this work.

If you're going to reject a string, echo it back to tell the user and tell him why, and remember to put it through htmlentities.

Jim - have you seen the note about our Open House weekend at the end of November?  Let me know if you would like to come;  we have a MySQL chunk and a PHP chunk - I met Monty Widenius and Rasmus Lerdorf last month and and revived and re-excited.

Graham

P.S. Would could play with some practical injection rejection too!

Hi Graham,

I guess using regular expressions to check the input is a good way. What characters do you suggest I strip from input?

My own thoughts are removing any ; I see, and stripping any single quotes which are next to a space (O'Malley will be fine, but 1' or 1=1-- will not).

I must admit, I for one would find any article you decided to write on the subject very useful . I really do think that knowledge on how to defeat SQL Injection attacks would be of much value to everyone who designs online databases, and as I see it there is not a lot of information easily available at the moment.

Cheers,  Jim

P.S. Whilst I'd love to come to the Open House weekend, I'm not likely to be available. However, for those of us who can't make it, it would be nice if some mention of what went on was in the next "Of Course" newsletter.

---