Query parameterization and protection against SQL injections

In this section, we will look at how to effectively use query parameterization in my ORM system to protect against SQL injections and ensure data security.

Query Parameterization

Query parameterization is critical for protecting against SQL injection. It allows passing values into queries as parameters instead of embedding them directly in the SQL query. This not only protects against unwanted modifications in queries but also improves code readability and maintainability.

Example of Query Parameterization

Here's how you can implement query parameterization in your ORM system:

const parametrizeQuery = await databaseManager.query(`
    INSERT INTO users (user_id, email, password, is_active)
    VALUES ($1, $2, $3, $4)
    RETURNING user_id;
`, [3, '[e-mail]', 'sedgdbdfb', true]);

In this example, the query to insert a new user into the users table uses parameters instead of directly embedding values into the SQL query. Parameter values are provided as an array [3, '[e-mail]', 'sedgdbdfb', true], which replaces the corresponding placeholders $1, $2, $3, and $4 in the query.

How This Protects Against SQL Injection

• Prevention of Unwanted Data Insertion: Instead of including data directly in the SQL query, parameterization allows passing them separately. This means that parameter values are processed as data, not as part of the SQL code, making it impossible to use them to execute unwanted SQL commands.

• Query Cleanliness: Parameterization reduces the risk of errors related to incorrect SQL query formatting. It ensures a cleaner and more understandable code that is easier to maintain and check for vulnerabilities.

• Automatic Data Escaping: Many modern databases and ORM systems automatically escape data passed through parameterization, providing an additional layer of protection.

Automatic Parameterization in QueryBuilder

In my ORM system, the QueryBuilder automatically parameterizes queries, providing an extra level of security. For example, consider the following query:

const exampleOfInsertQueryWithParametrize = databaseManager.queryBuilder()
    .insert({
        task_id: 1,
        title: 'Нове завдання',
        description: 'Опис нового завдання',
        is_completed: false,
        due_date: '2024-08-31',
        price: 1480
    }, 'tasks')
    .build();

This code forms an SQL query that, when outputted, will look like this:

INSERT INTO tasks (task_id, title, description, is_completed, due_date, price)
VALUES ($1, $2, $3, $4, $5, $6);

Thanks to automatic parameterization, QueryBuilder ensures that all transferred data will be protected from potential SQL injections, making queries safe and efficient. Query parameterization is a key SQL injection protection mechanism that ensures code security, reliability, and maintainability. Automatic parameterization implemented in my ORM system through QueryBuilder significantly reduces the risks associated with executing malicious SQL queries. Thanks to this approach, developers can focus on the logic of the application without worrying about vulnerabilities related to SQL injections.