Avoiding SQL injections

SQL injections are unpleasant but can be avoided

SQL injection is a serious vulnerability that almost always has horrifying consequences. Unfortunately, the vulnerability is also quite common in codebases where database queries are constructed as raw strings.

The good thing is that it is possible to build your code so that the risk of SQL injection can be almost eliminated. In addition, there are a couple of hardenings that can be done as a precaution to reduce the risk.

First things first

Before we proceed any further, let's make one thing clear. In order to avoid SQL injections, the code must be built (or refactored if it is already built) so that raw SQL queries are not parameterized at all.

The code must not contain anything like this:

var query = 'SELECT * FROM users WHERE id=' + '"' + sanitizeSqlParameter(req.params['id']) + '"';

Otherwise, it is only a matter of time before there is a slip in one part of the code that compromises the entire application.

So let's stick to safe programming practices the whole time. Let's go through these next.

Best solution: ORM (Object Relational Mapper) library

ORM (Object-Relational Mapping) provides developers with the possibility to handle database operations using code and objects instead of raw SQL. The name comes from the fact that the data structures of a relational database are "mapped" to classes in the program code.

Example of vulnerable code

Let's assume that we have an application where we can search for users in a database based on their username.

Here is an example of the wrong way (code has SQL injection vulnerability):

def get_user(username):
    query = f"SELECT * FROM users WHERE username = '{username}'"
    cursor.execute(query)
    user = cursor.fetchone()
    return user

Example of Secure Code

If an ORM is used, the database structure is described in the code, for example, in the following way:

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True)
    email = Column(String)

The same function (which retrieves the user by username) can then be implemented in this way:

def get_user(username):
    user = session.query(User).filter_by(username=username).first()
    return user

Note that this time the developer didn't even have to know SQL, let alone write it. The database structure is clear and visible immediately from the code, and the classes are typed so that the development environment would report an error if the code tried to handle an invalid column or set incorrect data type to a column.

There are many benefits. But above all, now there is no longer a risk of SQL injection. The ORM library knows how to handle the parameter safely and formulates optimized SQL queries for the search.

Popular ORM libraries

Here are some ORM libraries worth trying:

Python:

• SQLAlchemy: https://github.com/sqlalchemy/sqlalchemy
• Django ORM (ORM that comes with the Django library): https://docs.djangoproject.com/en/5.0/topics/db/queries/
• Peewee: https://github.com/coleifer/peewee
• Pony ORM: https://github.com/ponyorm/pony

Java:

• Hibernate: https://github.com/hibernate/hibernate-orm
• MyBatis: https://github.com/mybatis/mybatis-3
• JOOQ (Java Object Oriented Querying): https://github.com/jOOQ/jOOQ

Ruby:

• ActiveRecord (ORM included with Rails library): https://guides.rubyonrails.org/active_record_basics.html
• Sequel: https://github.com/jeremyevans/sequel

JavaScript (Node.js):

• Prisma: https://github.com/prisma/prisma
• Sequelize: https://github.com/sequelize/sequelize
• TypeORM: https://github.com/typeorm/typeorm
• Bookshelf.js: https://github.com/bookshelf/bookshelf

C#:

• Entity Framework (EF): https://learn.microsoft.com/en-us/ef/
• Dapper: https://github.com/DapperLib/Dapper

PHP:

• Doctrine ORM: https://github.com/doctrine/orm
• Eloquent (ORM included with Laravel): https://laravel.com/docs/10.x/eloquent

Go:

• GORM: https://github.com/go-gorm/gorm

Second option: Parameterized queries

In addition to the ORM library, there is another way to securely build SQL queries - parameterized queries (prepared statements). However, this approach does not include the other advantages of ORM and is not as secure either because you always have to be slightly cautious to avoid accidental omissions where parameterized queries are not used correctly.

Parameterized queries look like this.

def fetch_user_from_database(username):
    query = "SELECT * FROM users WHERE username = ?"
    cursor.execute(query, (username,))
    user_data = cursor.fetchone()
    return user_data

In this example, the sqlite3 module is used and a parameterized query is formed using ? marks to reserve spaces for later input. This avoids directly including input into the SQL statement and thus protects the application from SQL injections. The SQL library itself knows how to safely replace the question marks with the given parameter values.

Hardening

Next, let's go through a couple of hardenings that you can possibly do (depending on the technical environment) to detect an attack and reduce the resulting damage. These do not replace the use of secure programming methods (such as ORM), but are good additional protections.

WAF Product

WAF (Web application firewall) products are usually quite good at blocking or at least detecting and hindering SQL injection attacks. It is therefore advisable to set up such a product between the Internet and the application with sufficiently strict rule sets.

In addition, the observations of the WAF product should be connected to an automatic alarm system so that you receive real-time information as soon as someone starts searching for vulnerabilities in your application.

False positives can be effectively reduced by making alerts only when a malicious HTTP message is targeted towards the application based on the host-header and not just a random automatic scan directed towards a random IP address from the Internet.

Minimization of database user rights, especially access to metadata

When a SQL injection occurs, the attacker gains access to the database with the privileges that the application has used for the query. That's why you should never give the application any extra privileges to the database.

All unnecessary operations such as reading or writing files, establishing network connections, or executing operating system commands should be prevented, of course.

The most important is, however, preventing the reading of metadata, which unfortunately is easier said than done in many databases. Database design does not always prioritize security.

Metadata refers to information such as the "information_schema" table from which one can determine the structure of the database (tables, columns, etc.). Without this information, it can often be difficult for an attacker to steal exactly the data that the attacker would want.

Database Error Monitoring

If a SQL syntax error occurs in the production environment of an application, it may most likely be a SQL injection vulnerability that has either been discovered accidentally by the user entering unexpected characters into the application or it may be an attacker who has just found the vulnerability and intends to automate its exploitation and extract the entire application database.

Connecting these errors to real-time security monitoring is highly recommended.

Summary

In conclusion: The correct way to avoid SQL injections is to use an ORM library, which completely eliminates the risk of developers accidentally causing SQL injection vulnerabilities in the application.

The option of using parameterized queries was presented. It is technically possible to construct SQL queries securely with them, but they do not offer other benefits of an ORM library, such as a pre-defined typed database structure in code. Additionally, it is noteworthy that it is easy to accidentally slip into constructing raw SQL as text when using parameterized queries, as ultimately parameterized queries are still raw SQL constructed in code.

Finally, a couple of hardenings were discussed that may help in detecting the attack and minimizing the damage if a vulnerability were to make its way into the application.