> For the complete documentation index, see [llms.txt](https://khalid-emad.gitbook.io/order-by-sql-injection/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://khalid-emad.gitbook.io/order-by-sql-injection/order-by-sql-injection.md).

# ORDER BY SQL Injection

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` : 

![First symbol with letter A](/files/Ve5qJZIL3dxe5RqeArac)

And if we choose to sort by `atomic_number` : 

![The first number is 3](/files/PSYoJGv3S20iH7JRqdBE)

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 : 

![](/files/J20flfsRHScpLCqhJFaB)

If we changes the condition to false then it will order by atomic\_number : 

![](/files/Bzlio0UHJz34cy0RghrN)

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 : 

![](/files/SbMSNGlIJEV4Q6UqLPss)

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

![](/files/WPu6rqbiX8vwrgZFi35h)

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

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