Output won't display if it exists more than once in the DB

Question

Alright so I'm testing with some SQLi on localhost and I stumbled upon a really wierd "error" that I've never seen before.

I'm trying to get information from a database called "game" with a table called "users" (15 columns)

Here's the first part of the table:

http://i.gyazo.com/7e6c81ef2b235a778dec1fd343e8c3bf.png enter image description here

The hashes are "test" and "test1".

So I'll get the first hash with

'+union+select+concat(password),2,3,4,5,6,7,8,9,10,11,12,13,14,15+from+game.users limit 0,1--+-

a94a8fe5ccb19ba61c4c0873d391e987982fbbd3

Let's continue, we move to the other row with

'+union+select+concat(password),2,3,4,5,6,7,8,9,10,11,12,13,14,15+from+game.users limit 1,1--+-

and it gets b444ac06613fc8d63795be9ad0beaf55011936ac

So everything's working correctly, HOWEVER if I would change the users hashes so they have the same as

http://i.gyazo.com/df1154a02cae5e5bbde04d7e47256899.png enter image description here

then our second injection doesn't work anymore

'+union+select+concat(password),2,3,4,5,6,7,8,9,10,11,12,13,14,15+from+game.users limit 1,1--+-

it returns nothing how and why is this possible and most importantly how would I fix this? I only want it to get 1 result at a time, not more.

I'm testing on a Windows 7 machine using Wamp.

The query being executed is

SELECT *
FROM users
WHERE id='-1'
UNION
SELECT concat(password),2,3,4,5,6,7,8,9,10,11,12,13,14,15
from game.users
limit 1,1

This issue nothing to do with SQL Injection as SQL Inection is just a means of altering the original query shape. Rewrite - and look at - the queries plainly to see. (It will also make the question more readable.) — user2864740, Jul 28 '14 at 17:36
Can you please elaborate? There's nothing really in the PHP code that would cause this malfunction if that's what you mean. http://i.gyazo.com/2cc2b41b4e37486f0111d0a95d1f8381.png — user3885139, Jul 28 '14 at 17:39
The PHP code *allows* the SQL injection due to improper coding, but such is irrelevant to the question. Look at the *actual* SQL executed (independently of any SQL injection). — user2864740, Jul 28 '14 at 17:41
I've looked at the actual SQL query but I failed to see a solution to the problem. I updated the thread with it also. — user3885139, Jul 28 '14 at 17:46
NOTE: in most SQL DBMS's, `UNION..` means `UNION [DISTINCT]`. In other words, `UNION` implicitly does a `DISTINCT` on its result set rows. The usual fix is to change it to `UNION ALL`. — RBarryYoung, Jul 28 '14 at 18:46
possible duplicate of [Combining UNION and LIMIT operations in MySQL query](http://stackoverflow.com/questions/1415328/combining-union-and-limit-operations-in-mysql-query) — user2864740, Jul 28 '14 at 18:56
Whoops, it's a simply parsing issue. Add parenthesis as shown in the duplicate question. — user2864740, Jul 28 '14 at 18:56

Mike Brant · Answer 1 · 2014-07-28T22:08:08.313

From MySQL Manual:

The default behavior for UNION is that duplicate rows are removed from the result.

http://dev.mysql.com/doc/refman/5.6/en/union.html

If you changed the password field to be the same for those two rows, then you will also two identical rows in the second part of the UNION that will be de-duped.

What you are doing however makes basically no sense. Why are you using CONCAT, but then not concatenating anything?

Also, why would you use an offset of 1 if you only wanted one row? I would think your offset should be 0 (i.e. LIMIT 0, 1).

Finally, why are you even doing this union to begin with?

What is probably happening is there is no record meeting the criteria of user id = -1 then because of the de-dupe, there is only one row returned in the pre-limit result set, which you are skipping over by specifying an offset of 1. Thus you get an empty result set.

Before when the password fields were different, you had two different rows in the pre-limit result set, thus there was a row at offset 1.

Output won't display if it exists more than once in the DB

1 Answers1