ORDER BY SQL Injection

Based on a CTF challenge scenario

This was an intresting and good challenge from the latest Nahamcon CTF , The reason i choose to explain it is because it also appeared on HTB Cyber Apocalypse . It can be categorized under Blind SQL Injection with conditional responses , so let's dive in .

Code Analysis

We can see that there is a code within the challenge [ Flask Web Application ], I will not dispay all of the code but the only important snippets .

First Snippet :

if request.method == "POST":
        search = ""
        order = None
        if "search" in request.form:
            search = request.form["search"]
        if "order" in request.form:
            order = request.form["order"]
        if order is None:
            metals = Metal.query.filter(Metal.name.like("%{}%".format(search)))
        else:
            metals = Metal.query.filter(
                Metal.name.like("%{}%".format(search))
            ).order_by(text(order))

We can see that it accepts POST method and 2 post parameteres which are search and order . The order parameter is executed within a SQL query , but we realize that it isn't validated to any source of injection which makes it a potentiall point of injection .

Second Snippet :

class Metal(Base):
    __tablename__ = "metals"
    atomic_number = Column(Integer, primary_key=True)
    symbol = Column(String(3), unique=True, nullable=False)
    name = Column(String(40), unique=True, nullable=False)

    def __init__(self, atomic_number=None, symbol=None, name=None):
        self.atomic_number = atomic_number
        self.symbol = symbol
        self.name = name


class Flag(Base):
    __tablename__ = "flag"
    flag = Column(String(40), primary_key=True)

    def __init__(self, flag=None):
        self.flag = flag

We can see that there is two tables which are metals and flag , the flag table has a column called flag , the metal table has 3 columns atomic_number , symbol & name .

Web Application Analysis

If we choose to order by or to sort by symbol :

And if we choose to sort by atomic_number :

So let's try to manipulate the order parameter, if we injected the following query :

order=CASE WHEN(1=1) THEN symbol ELSE atomic_number END

What would it do ?

First the query will check for the condition ( 1=1 => True ) and will sort depending on the condition , Since it is true so it will order by symbol as we can see :

If we changes the condition to false then it will order by atomic_number :

Nice , so now we got the key which we will solve the challenge upon. We wil give the query a condiition for the flag characters if it is true then we will see the Ac symbol if it is fase we wil see the Li symbol .

Exploitation

In Sqlite there is a function called substr() which we will use to exploit the SQL Injection .

We know that the first character of the flag is"f" so we can test for it :

CASE WHEN((select substr(flag,1,1) from flag)= 'f') THEN symbol ELSE atomic_number END

We now expect to see the Ac character :

We are on the right path !

I've created a simple python script that helps me to find the flag , It can be found here : https://github.com/khaled1000emad/CTFs-With-Python-Scripts/blob/main/order-by.py

Final Output

John Hammond Solves a challeneg like it : https://youtu.be/WmCioU9F2_8

Youtube video for our challenge : https://youtu.be/ttsFRYkL8wQ [ from 11:49 ]

Last updated 3 years ago