SQL injection can bypass login but not drop table

Question

I've setup a test environment for running some SQL Injection against my code and learning how to defend against it. I can bypass the login form using the following in the password field:

' OR username = 'admin

Which gives me the query:

SELECT * FROM customer_data WHERE username = '' AND password = '' OR username = 'admin'

This works fine but I'm having trouble with dropping a table. I've tried various attempts along the lines of inserting something like this in the password field:

OR '1' = '1'; DROP TABLE temp; --

I created a table called temp just to try it against but nothing I can think of has worked. If I login to the PHP MyAdmin (same credentials as the page is using) I can manually insert the query that would be constructed above and it works just fine.

Answer 1

Answering your question

mysql_query() doesn't support multiple queries as documented:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.

Which means that DROP TABLE temp; -- is never executed.
It is although possible if you use mysqli::multi_query or using PDO.

Best practice

Warning ?
You see that big red warning box ? The mysql extension is in process of deprecation and will throw warnings as of PHP 5.5.0. There are even plans to drop it completely in PHP 5.6 or PHP 6.0^source. Note that mysql_ isn't broken as Ángel González stated:

The extension is not broken. The problem is the bad usage. It can be used safely, and good developers have been doing so for ages, by creating php wrappers. In magic quotes, the work has been the opposite. The developers had been detecting the feature in php and disabling it.

The new standard
The new standard is MySQLi or PDO. I won't compare the two extensions here but there are really a bunch of good features especially from PHP 5.3+ which will save you time and efforts. Note that by using MySQLi/PDO won't protect you per se from SQL injections. The best option would be to use prepared statements. The data is sent separately from the query thus making it impossible to inject values. This is well explained by Anthony Ferrara in this video.

Be careful
But wait "impossible" ? That sounds just too great :)
Say for example we have two groups: group1 & group2. There is a certain php file deleteUser.php getting an id from $_GET.
The prepared statement looks like this: DELETE FROM users WHERE id = ?. When the query is made with $_GET['id'] the user with ID = $_GET['id'] will get deleted. Hey, but that means that users from group1 could delete users from group2 by using that ID which isn't intended. So we may edit the query in something like DELETE FROM users WHERE id = ? AND group = ? and sending the user's group name he's in along with the query.
Short: prepared statements won't protect you from logic flaws.

Multi query ?
You don't need mutli queries. If you want to do two things then do it separately, it will give you more control over your queries and make your code more readable.

On another note:
If you're dynamically making and droping tables you're most likely doing it wrong. You should design your database in such a way that this isn't needed. Of course there may be exceptions but they are mostly busted since you may then look for a NoSQL solution (other db engine).

TL;DR

• Stop using mysql_ functions and step over to MySQLi or PDO
• Use prepared statements, note that it will not protect you from attacks if you don't secure the logic behind it
• Don't use multi queries
• Make a good DB design
• If you want to train your hacking skills, you may check Vulnerable OS's?

Answer 2

It might be that the function you use in PHP does not allow stacked queries or because the user which is configured to perform the queries does not have drop table privileges. I suggest you log all the output and exceptions thrown by the application. It could give a clue where your problem lies.

EDIT 1: Instead of executing the query, print it to the screen.

Answer 3

This was an oversight on my part after some late night reading of the documentation!

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.