40

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.

Jens Erat
  • 23,446
  • 12
  • 72
  • 96
Ali
  • 519
  • 1
  • 4
  • 8
  • Things changed a little bit. I'm posting an update in a new question and not changing this one considering all the attention it got. – Ali Dec 24 '14 at 17:22
  • 1
    My recommendation when finding such security holes is to fix them regardless of whether they are exploitable or not. Of course that doesn't make your question irrelevant. It can be very educational to see why a seemingly un-exploitable security hole can be exploited anyway. Should you ever manage to find one which is truly un-exploitable, chances are you spent more time analyzing the exploitability than you would have simply fixing it. – kasperd Dec 25 '14 at 10:18
  • Any time you have ambiguity where the *data* has the potential to be confused with the *command*, you have the potential for trouble, even if it is difficult to find. – Brad Dec 25 '14 at 20:08
  • Cast it as an int, and make sure it is within acceptable bounds. Or, if your client has the ability to bind by type (int type), do that. – superultranova Dec 27 '14 at 07:34
  • `UNION` is not the only concern I would have. What about `DROP TABLE` or `TRUNCATE TABLE`? – Brandon Dec 27 '14 at 21:32
  • SQL injection is not just about extracting restricted data, but also about damaging data. – Brandon Dec 27 '14 at 21:33

6 Answers6

75

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.

Peter Mortensen
  • 877
  • 5
  • 10
PiTheNumber
  • 5,394
  • 4
  • 19
  • 36
  • 1
    Would this be possible if there was an ORDER BY statement before the limit? – Ali Dec 24 '14 at 16:46
  • This won't work in MySQL 5.7+ because you cannot use `UNION` after `LIMIT` unless the whole query before `UNION` is surrounded in parenthesis. Something like `(SELECT * FROM table LIMIT 1) UNION SELECT ....`. – Mukarram Khalid Jul 06 '20 at 06:06
3

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.

Dillinur
  • 468
  • 3
  • 7
  • You can put an integral expression and extract a couple more bits at a time, provided the query generates a list you'll see, which it seems to. – Jan Hudec Dec 23 '14 at 18:02
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.

Schwern
  • 1,549
  • 8
  • 17
  • 1
    Client libraries have *deliberately removed* multiple-statement support for security reasons. – Mark Dec 24 '14 at 04:55
  • @Mark Citation? Perl's DBD::mysql has it off by default, but easily available. PHP's [mysqli](http://php.net/manual/en/mysqli.multi-query.php) requires a special call. .NET's [ADO](http://www.codeproject.com/Articles/306722/Executing-multiple-SQL-statements-as-one-against-S) appears to require no special code. – Schwern Dec 24 '14 at 06:08
1

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.

  • 1
    While some mysql clients can be constrained to allow only a single statement per invocation (this preventing the attack described above) I would consider it bad practice to rely on this as the only protection mechanism - similarly while currently the mysqld expects a literal integer after LIMIT it is possible that it may allow a function or sub-query in future. – symcbean Dec 23 '14 at 12:30
  • -1 a real exploit would be nice. – rook Dec 23 '14 at 15:30
  • 1
    @Rook He said the input of `LIMIT` is not sanitized, so he can run that command on the interface of the website since he succeeded to run something. Also, why do you ask for a 100% real exploit ? I do not even know what website is concerned by this –  Dec 23 '14 at 15:34
  • Query stacking? In MySQL? Have you ever exploited SQL Injection on MySQL??? PiTheNumber got he right answer. – rook Dec 23 '14 at 15:37
  • 1
    @Rook the person who asked the question already knows he can use `UNION SELECT something` (read his question). If I did SQL injections before ? As you know already, never (but I love your website) –  Dec 23 '14 at 15:43
  • 1
    Sorry but, this is a huge pet peeve of mine. Mainly because I really wish query stacking was supported by majority of SQL databases, but in the real world it is very uncommon (MS-SQL, Access, SQLite). I see posts talking about query stacking as providing false hope. – rook Dec 23 '14 at 15:56
1

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");
Community
  • 1
Damian Yerrick
  • 540
  • 3
  • 15
1

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.

Rick
  • 11
  • 1