Is SQL injection possible with LIMIT?

Question

A friend of mine built a web application that I'm testing for fun. I noticed that he allows a user to set the limit of a certain query, and that limit is not sanitized.

For example, I can choose any number or string I like as a limit. I realize that this is SQL injection, and I can easily inject SQL commands, but is it really possible to extract any data or do any damage with a LIMIT?

Example of the query:

SELECT * FROM messages WHERE unread = 1 LIMIT **USER INPUT HERE** 

I understand that if the injection was in the WHERE clause I could've easily done a UNION SELECT to extract any information, but is that really possible if the user input was after the limit?

For more information, my friend is using the MySQL DBMS, so you can't really execute two queries such as:

SELECT * FROM messages WHERE unread = 1 LIMIT 10;DROP TABLE messages-- 

It is not possible.

Answer 1

You can make a UNION SELECT here. The only problem is to match the columns from messages, but you can guess those by adding columns until it fits:

SELECT * FROM messages WHERE unread = 1 LIMIT 1 UNION SELECT mail,password,1,1,1 FROM users 

Just keep adding ,1 until you get the correct column count. Also, you need to match the column type. Try null instead of 1.

If you can see MySQL errors that would help big time here. Otherwise you got a lot of trying.

Also see Testing for SQL Injection at owasp.org for some details.

Answer 2

Either there is almost no constraints, and you can do it the usual way (UNION or '; XXX ---')

Sometimes it's not possible, and you'll have to fallback on putting a boolean expression in the LIMIT. That's a blind SQL injection, and it'll allow you to dump the whole database one bit at a time.

Answer 3

MySQL does support multiple statements and has since at least 4.1 ten years ago. For example, you can turn this on in the Perl mysql module using mysql_multi_statements.

While the client library may not support multiple statements right now, you're just an upgrade or configuration change away from a security hole.

Answer 4

Setting a limit of 1 UNION SELECT ... will allow the attacker to pull information out of any other table, so long as the columns have (or can be CAST to have) the same number and type as the columns in the first SELECT. Setting a limit as a value produced from a sub-SELECT will let the attacker read any other information in the database from the number of returned rows. For example, another user's password hash can be extracted 4 bits at a time by repeatedly doing a sub-query returning a number between 0 and 15 at different substring offsets and then counting rows in each result.

But both these attacks can be thwarted. Many database drivers allow a parameterized LIMIT. If a particular driver interferes with a parameterized LIMIT, you can sanitize the input by converting the input to an integer before string-substituting it into the query. This will also help you make sure not to return more records than your application can handle from one query (such as LIMIT 12345678). In PHP, it might look like this:

<?php //... $limit = intval($_REQUEST['numresults']); $limit = min(max($limit, 10), 100); $stmt = $dbh->prepare("SELECT ... FROM ... WHERE ... LIMIT $limit"); 

Answer 5

This a good time to use PDO prepared statements and bindParam that variable as an integer-only input. That should negate any attempt at what you are trying to do by injecting SQL.

I would also consider letting the user choose from a preset list of numbers in a dropdown. Unless the results page is set to paginate you can potentially have a nice mess on your hands trying to load 100,000 records to a page.

Answer 6

The query of your friend would not be of much use today, but it may be later but as technologies evolve, and MySQL DBMS is under constant improvements as many other technologies, your friend must sanitize the input in the case of the LIMIT statement would be changed so that it would possible to use UNION just after it. Your friend may also consider the case where the architecture of MySQL DBMS would be modified in a way that it would be possible to run multiple SQL statements unlike as it is now.

What I want to say, is that your friend must sanitize the input: it is the best practice: you may not see that too important today, but in the future you never know, and remember there are always people who may be more experienced than your friend.