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:


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.

  • 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
    Commented Dec 24, 2014 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
    Commented Dec 25, 2014 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
    Commented Dec 25, 2014 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. Commented Dec 27, 2014 at 7:34
  • UNION is not the only concern I would have. What about DROP TABLE or TRUNCATE TABLE?
    – Brandon
    Commented Dec 27, 2014 at 21:32

6 Answers 6


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.

  • 1
    Would this be possible if there was an ORDER BY statement before the limit?
    – Ali
    Commented Dec 24, 2014 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 ..... Commented Jul 6, 2020 at 6:06

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.

  • 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
    Commented Dec 23, 2014 at 18:02

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.

  • 1
    Client libraries have deliberately removed multiple-statement support for security reasons.
    – Mark
    Commented Dec 24, 2014 at 4:55
  • @Mark Citation? Perl's DBD::mysql has it off by default, but easily available. PHP's mysqli requires a special call. .NET's ADO appears to require no special code.
    – Schwern
    Commented Dec 24, 2014 at 6:08

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:

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

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.


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
    Commented Dec 23, 2014 at 12:30
  • -1 a real exploit would be nice.
    – rook
    Commented Dec 23, 2014 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
    – user45139
    Commented Dec 23, 2014 at 15:34
  • 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)
    – user45139
    Commented Dec 23, 2014 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
    Commented Dec 23, 2014 at 15:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .