The chapter on SQL Injection from the book Guide to PHP Security by Ilia Alshanetsky is available in PDF format. This can be a rather fun topic so I downloaded a copy of the PDF and started reading. I’d read less two pages before I was ready to toss this chapter out the window.
The first example showed a very simple inject that terminated the original query with a ; and appended another query that deleted some data. The example used MySQL functions and the author noted the following after explaining the injection:
Fortunately, if you use MySQL, the mysql_query() function does not permit query stacking, or executing multiple queries in a single function call. If you try to stack queries, the call fails.
However, other PHP database extensions, such as SQLite and PostgreSQL, happily perform stacked queries, executing all of the queries provided in one string and creating a serious security problem.
The emphasis is mine. I couldn’t believe that the author was basically claiming that MySQL is more secure than SQLite and PostgreSQL because they allowed you to run multiple SQL statements in one string. Then something else came to mind, didn’t MySQL add this feature not too long ago? So I went hunting around and came up with the C API Handling of Multiple Query Execution in MySQL, which was introduced in version 4.1. Although still correct, the mysql_query() function in PHP doesn’t allow for multiple statements, the underlying C libraries do. I’d expect that at somepoint in the future PHP will catch up and support that feature for MySQL 4.1 and above.
But back to my first point, being able to run multiple queries in a single string is not a security threat. Not proplerly filtering data before using in an SQL statement is. If you allow unfiltered data to be injected into your SQL statement then you’ve got problems, even if you database doesn’t support multiple statements in a single string.
I hope that this isn’t typical of the rest of the book.
6 replies on “Guide to PHP Security”
I think misunderstand the point I was trying to make there. MySQL is no more secure against SQL injection then let’s say PostgreSQL. However, the fact that MySQL interface in PHP does not support query chaining makes exploitation of the problem more difficult. Certainly not impossible, just more difficult.
I think that certain types of injection attacks may be harder depending on support for query chaining. If you are open to injection attacks, degrees of difficulty aren’t what I’d consider an additional layer of security though. I believe that this is especially true given the amount of attention SQL injection attacks have received over the years. For the bad guys the degrees in difficulty aren’t much.
Wow, Ilia didn’t waste any time replying!
i think there is a mistake in the first example of the chapter:
// supposed input
$name = “ilia’; DELETE FROM users;”;
mysql_query(“SELECT * FROM users WHERE name=’{$name}’”);
in page 74 you said that:
“mysql_query() function does not permit query stacking, or executing
multiple queries in a single function call.”
but in the example you make multiple queries in a single function call.
Joseph I disagree, I think there are definately ‘levels’ of sql injection – being able to see all the information in a database and being able to update is different.
The fact that mysql_query() does not support stacked queries can make it impossible for a hacker to insert data into a database through a weak point.
For example take the following query:
‘SELECT * FROM users where id = ‘. $_GET[‘unsanitised_variable’]
because of this a user can now construct a union select and view anything he or she likes in the database, however there is no way for the user to insert data into the database.
Obviously not sanitising user input is a fools mistake, however it must be noted that allowing multiple queries increases the risk of damage via sql injection.
@Mike –
This post is now nearly 3 years old, in that time MySQL versions that support nested queries are common. So now your argument looks like MySQL is inherently less secure now.