> 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

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

![First symbol with letter A](https://1086039081-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Ftc75wIiq8rxVPvDPzBYk%2Fuploads%2Fia1sfsRxysq2nsvO8g1B%2Fimage.png?alt=media\&token=0a3aa027-c679-4e5d-ab7e-fca97d94decc)

And if we choose to sort by `atomic_number` : 

![The first number is 3](https://1086039081-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Ftc75wIiq8rxVPvDPzBYk%2Fuploads%2FgByEQE3Yt59b0vvfb8Jm%2Fimage.png?alt=media\&token=41730532-ec6f-4233-b3b2-21814e14308a)

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 : 

![](https://1086039081-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Ftc75wIiq8rxVPvDPzBYk%2Fuploads%2Fqjz0NRYPA2LBdBWcoGJO%2Fimage.png?alt=media\&token=ac5aa4f6-ec76-4fcc-8722-7bd993b10b6a)

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

![](https://1086039081-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Ftc75wIiq8rxVPvDPzBYk%2Fuploads%2FrPOCOgRdyX80qqDvjTy8%2Fimage.png?alt=media\&token=b711a115-0f6c-4266-a30e-2f8888fc9d48)

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 : 

![](https://1086039081-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Ftc75wIiq8rxVPvDPzBYk%2Fuploads%2FTAhAEUlBIqpQxMJurbkd%2Fimage.png?alt=media\&token=dcf01769-5d3e-4a61-8817-ae508448198e)

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

![](https://1086039081-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Ftc75wIiq8rxVPvDPzBYk%2Fuploads%2Fcdblu4Fs1GP7fT8Yi2pM%2Fimage.png?alt=media\&token=129399ef-f79b-4408-a66c-b35242402b11)

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

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