TryHackMe: ORM Injection

What is ORM?

Object-relational Mapping (ORM) is a programming technique that facilitates data conversion between incompatible systems using object-oriented programming languages, serving as a bridge between the object-oriented programming model and the relational database model.

If a programmer is already familliar with PHP for example, with the help of Laravel's Eloqeunt ORM, we can convert the PHP query into SQL seamlessly.

So where('email', '[e-mail]')->get() is equivalent to SELECT * FROM users WHERE email = '[e-mail]'

---

Commonly Used ORM Frameworks

Several ORM frameworks are widely used, each designed for different programming languages and environments.

Doctrine (PHP)

• Popular in Symfony framework but can be used independently.
• Provides a query builder, schema management, and an object-oriented query language.
• Maps complex object structures to database schemas.

Hibernate (Java)

• Simplifies Java class-to-database mapping.
• Uses Hibernate Query Language (HQL) for data retrieval and manipulation.
• Supports multiple databases with caching and lazy loading for performance optimization.

SQLAlchemy (Python)

• Provides an SQL toolkit and ORM.
• Supports both raw SQL and ORM functionality.
• Modular and flexible, suitable for small to large-scale applications.

Entity Framework (C#)

• ORM for .NET applications by Microsoft.
• Enables working with relational data using domain-specific objects.
• Supports multiple database providers and integrates with .NET technologies.

Active Record (Ruby on Rails)

• Default ORM for Ruby on Rails.
• Uses the Active Record pattern where tables map to classes and rows to objects.
• Provides a rich set of methods for querying and manipulating data.

---

Configuring Laravel's Eloquent ORM with PHP

1. Installing Laravel

To set up Laravel with Eloquent ORM, install Laravel using Composer:

composer create-project --prefer-dist laravel/laravel thm-project

Here, thm-project is the project name.

2. Configure Database Credentials

Laravel uses the .env file to store database credentials. Open .env and update:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database_name
DB_USERNAME=your_database_user
DB_PASSWORD=your_database_password

3. Setting up Migrations

Create a Migration

Use Artisan CLI to generate a migration file for the users table:

php artisan make:migration create_users_table --create=users

4. Define Table Structure

Open the generated migration file and define the table schema:

<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateUsersTable extends Migration
{
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::dropIfExists('users');
    }
}

5. Run Migrations

Apply the migration to create the table in the database:

php artisan migrate

This command executes the up() method, creating the users table.

6. ORM Security Considerations

• Migrations help map database tables to Laravel models efficiently.
• Improperly configured migrations can lead to ORM injection vulnerabilities.
• Ensure secure ORM configurations to prevent unauthorized access to sensitive data.

---

Identifying ORM Injection

Techniques for Testing ORM Injection

1. Manual Code Review

• Inspect source code for raw query methods (whereRaw(), DB::raw() in Laravel).
• Identify concatenated strings or unescaped user inputs in ORM queries.

2. Automated Scanning

• Use security scanning tools to detect ORM injection vulnerabilities.
• Identify dynamic query construction and improper input handling patterns.

3. Input Validation Testing

• Manually test by injecting SQL control characters or keywords.
• Observe if input manipulation alters ORM query execution.

4. Error-Based Testing

• Enter malformed data to trigger error messages.
• Analyze error responses for insights into query structure and vulnerabilities.

Frameworks and ORM Injection Testing

Framework	ORM Library	Common Vulnerable Methods
Laravel	Eloquent ORM	whereRaw(), DB::raw()
Ruby on Rails	Active Record	where("name = '#{input}'")
Django	Django ORM	extra(), raw()
Spring	Hibernate	createQuery() with concatenation
Node.js	Sequelize	sequelize.query()

Key Security Measures

• Use parameterized queries (where() in Laravel, filter() in Django).
• Avoid raw queries or dynamic concatenation of user inputs.
• Ensure proper input validation and sanitization.

Understanding ORM injection risks and following secure coding practices is essential to safeguarding web applications.

Techniques to Identify the Framework

• Verifying cookies: Examine the cookies set by the application. Frameworks often use unique naming conventions or formats for their session cookies, which can provide clues about the underlying technology.

• Reviewing source code: Look through the HTML source code for comments, meta tags, or any embedded scripts that might reveal framework-specific signatures. However, this method may only sometimes be conclusive.

• Analysing HTTP headers: HTTP headers can sometimes contain information about the server and framework. Tools like Burp Suite or browser developer tools can be used to inspect these headers.
• URL structure: The structure of URLs can give hints about the framework. For instance, certain routing patterns are unique to specific frameworks.
• Login and error pages: Authentication pages and error messages can sometimes reveal the framework. Some frameworks have distinctive error pages or login form structures.

We can supply an inpuy of 1' and see what happens next.

We get this page, which displays an error such as SQLSTATE[42000]: Syntax error or access violation that indicates that the input has been improperly handled and concatenated into an SQL query, resulting in a syntax error.

The output shows that it uses Laravel's Eloquent ORM, as the error messages and query patterns are characteristic of Eloquent's query builder.

---

ORM Injection: Weak Implementation

Vulnerable

Below shows the source coe of the Email(Vulnerable) input field:

public function searchBlindVulnerable(Request $request)
{
    $users = [];
    $email = $request->input('email');
    $users = Admins::whereRaw("email = '$email'")->get();
    if ($users) {
        return view('user', ['users' => $users]);
    } else {
        return view('user', ['message' => 'User not found']);
    }
}

Breakdown of the function

• Retrieve input: The function captures the email parameter from the HTTP request. This is done using the input method of the $request object.
• Construct query: The function then constructs a raw SQL query using Laravel's whereRaw() method, which directly incorporates the email value into the SQL statement.
• Execute query: This query is executed, storing the result in the $users variable.
• Return view: Finally, the function returns a view. If the $users array is not empty, it passes the user data to the view; otherwise, it passes a "User not found" message.

The critical issue in this function lies in the direct use of whereRaw() with user input. This method of query construction is vulnerable to injection attacks because it does not sanitise the input.

An attacker can exploit this vulnerability by manipulating the email parameter. If an attacker inputs 1' OR '1'='1, the resulting query becomes:

$users = User::whereRaw("email = '1' OR '1'='1'")->get();

which translates to(in SQL):

SELECT * FROM users WHERE email = '1' OR '1'='1';

As a result, all user data in the table are retrieved.

Secure

Now let us take a look at the source code of Email(Secure) input field:

public function searchBlindSecure(Request $request)
{
    $email = $request->input('email');
    $users = User::where('email', $email)->get();
    if (isset($users) && count($users) > 0) {
        return view('user', ['users' => $users]);
    } else {
        return view('user', ['message' => 'User not found']);
    }
}

Breakdown of Secure Implementation

• Retrieving input: The function captures the email parameter from the HTTP request using the input method of the $request object. This is the same as in the vulnerable version.
• Constructing the query securely: Instead of using whereRaw(), the secure version uses Eloquent's where() method. This method automatically escapes the input, thus preventing SQL injection. The where() method constructs a parameterised query behind the scenes, ensuring that user input is not directly included in the SQL statement.
• Executing the query: The query is executed, and the result is stored in the $users variable. Because the query is parameterised, the input is sanitised, which means it cannot break the SQL query structure.
• Returning the view: Finally, the function returns a view. If the $users array contains data, it passes the user data to the view; otherwise, it shows an error message. This logic ensures that only valid data is processed and displayed.

Using the same malicious payload 1' OR '1'='1, it does not return all user data.

Why This Is Secure

• Parameterised queries: Parameterised queries ensure that the input values are treated as data only, not executable code. This prevents any injected SQL from being executed.
• Automatic escaping: Eloquent automatically escapes the input values, negating any special characters that could be used for SQL injection.
• Consistent query logic: By using Eloquent methods like where(), the query logic remains consistent and clear, making it easier to maintain and audit for security.

---

ORM Injection: Vulnerable Implementation

Laravel Query Builder SQL Injection Vulnerability (Pre 1.17.1)

Overview: In versions prior to 1.17.1, the Laravel query builder package had a significant vulnerability due to unsanitized query parameters. Specifically, it mishandled sorting parameters directly from user input, which allowed attackers to inject SQL.

The example uses the Spatie Query Builder, which relies on Laravel's query builder. The vulnerability demonstrates how an attacker could manipulate the query to retrieve more data than intended.

Steps to Reproduce the Vulnerability:

1. Access the vulnerable endpoint:

- Navigate to the query endpoint to fetch users sorted by `name`:  

https://10-10-65-94.p.thmlabs.com/query_users?sort=name
- This query corresponds to the following SQL query:

    `SELECT * FROM users ORDER BY name ASC LIMIT 2`

1. Inject the name parameter:

- An attacker tries to inject `'` into the `sort` parameter (`name'`). The system returns an error because it can't find the `name'` column.

![Injection attempt](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1719479019501)

1. Objective:

- The goal is to manipulate the query to **retrieve more data** than intended (bypass `LIMIT`).

1. Challenge:

- The query does not allow a simple injection using basic techniques. The key challenge is to **break out of the `ORDER BY` clause** to inject additional SQL.

1. Exploit with json_extract:

- To bypass the limitation of the query, the attacker uses the `->` operator, which in MySQL is used to navigate and extract values from JSON data. It serves as an alias for `json_extract()`.

1. Craft the payload:

- Inject the following payload to break the query and allow further SQL injection:

    `name->"%27)) SQL INJECTION QUERY #`

    - **Explanation**:
        - `name->`: The `->` is interpreted by Laravel and replaced with a MySQL `json_extract` function.
        - `"%27))`: Ends the existing string and condition.
        - `SQL INJECTION QUERY`: The attacker's custom SQL code.
        - `#`: Comments out the rest of the query to prevent errors.

1. Final Payload:

- The crafted payload to bypass the limit and fetch 10 rows from the `users` table:

   `https://TARGET_URL/query_users?sort=name-%3E%22%27))%20LIMIT%2010%23`
- The SQL query generated by the system:

   ```SELECT * FROM `users` ORDER BY json_unquote(json_extract(`name`, '$.""')) LIMIT 10#"')) ASC LIMIT 2```

- This bypasses the `LIMIT 2` restriction and fetches 10 rows.

1. Successful Exploitation:

- After injection, the attacker is able to retrieve more rows than intended, demonstrating the success of the SQL injection.

![Successful injection](https://tryhackme-images.s3.amazonaws.com/user-uploads/62a7685ca6e7ce005d3f3afe/room-content/62a7685ca6e7ce005d3f3afe-1719171665479)

Key Takeaways:

• This vulnerability highlights the importance of input validation and sanitization to prevent SQL injection in web applications.
• Special functions like json_extract can be exploited if user input is not properly validated, allowing attackers to bypass restrictions and manipulate queries.

---

Best Practices

Few Important Practices to Prevent SQL Injection

1. Input Validation:

- Always validate user inputs on both client and server sides.
- Ensure data conforms to the expected format, type, and length.
- Use regular expressions and built-in validation functions for strong input validation.

1. Parameterised Queries:

- Use parameterised queries (prepared statements) to interact with the database.
- This ensures user inputs are treated as data, not executable code.
- Avoid concatenating user inputs directly into SQL queries.

1. ORM Usage:

- Utilise ORM built-in tools to interact with the database.
- ORMs abstract SQL queries and help prevent SQL injection by handling user inputs securely.
- Ensure that the ORM is configured correctly and that any custom SQL queries are parameterised.

1. Escaping and Sanitisation:

- Escape user inputs to remove any special characters used for injection attacks.
- Sanitise inputs to remove potentially harmful data before processing or storing it.

1. Allowlist Input:

- Implement an allowlist approach for input validation.
- Only allow specific, expected values and reject everything else.
- This method is more secure than blocklisting known bad values, which can be incomplete.

Application in Popular Frameworks

Doctrine (PHP)

Use prepared statements with parameterised queries to prevent SQL injection attacks.

$query = $entityManager->createQuery('SELECT u FROM User u WHERE u.username = :username');
$query->setParameter('username', $username);
$users = $query->getResult();

SQLAlchemy (Python)

Leverage SQLAlchemy's ORM and Query API to use parameterised queries, which automatically handle escaping and parameter binding.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
user = session.query(User).filter_by(username=username).first()

Hibernate (Java)

Use named parameters with Hibernate's Query API to ensure inputs are adequately bound and escaped.

String hql = "FROM User WHERE username = :username";
Query query = session.createQuery(hql);
query.setParameter("username", username);
List results = query.list();

Entity Framework (.NET)

Employ parameterised queries in Entity Framework to secure database interactions and mitigate the risk of SQL injection vulnerabilities.

var user = context.Users.FirstOrDefault(u => u.Username == username);