# 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 ]


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://khalid-emad.gitbook.io/order-by-sql-injection/order-by-sql-injection.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.