Enter the terms you wish to search for.

Protecting Against SQL Injection

April 29, 2024

In March of 2024 CISA issued the following advisory related to SQL injection (SQLi): Secure by Design Alert - Eliminating SQL Injection Vulnerabilities in Software. SQL Injection is one of the most pervasive and damaging vulnerability types database administrators and developers are tasked with defending against. According to CISA “SQL injection vulnerabilities involve the insertion of user-supplied input directly into a SQL command, allowing threat actors to execute arbitrary queries. SQLi vulnerabilities are caused by software developers’ inattention to security best practices, resulting in the co-mingling of database queries and user-supplied data.” When exploited, attackers can gain unauthorized access to sensitive data, modify database contents, or even execute arbitrary commands on the underlying server. The consequences of a successful SQL injection attack can be catastrophic, leading to compromised customer information, financial loss, damaged reputation and regulatory penalties.

The good news is well-established and supported best practices can be applied to help protect against this attack type. Along with the recommendations in CISA’s advisory, the Open Web Application Security Project (OWASP) provides consistently updated guidance that can be applied. Input validation, escaping untrusted data, using prepared statements, stored procedures and code review can work together to help prevent SQLi and these damaging outcomes. Additionally, leveraging the principle of least privilege by restricting permissions on any service accounts or applications sending requests to the database can reduce the impact in the event that a SQL injection attack slips through. 

For customers using  EDBs Postgres Advanced Server (EPAS), EDB has a tool called SQL Protect, and a recently released Privilege Analysis feature which, when combined with the above described best practices, can help create a robust and well rounded defense against SQLi attacks.

Input Validation

While input validation shouldn't be the primary method used to prevent SQLi, it can contribute to reducing risk as early as possible in the data flow, essentially as soon as the data has been received from the untrusted source. OWASP covers many strategies for input validation, splitting them into two primary categories, syntactic and semantic. According to OWASP, “syntactic validation should enforce correct syntax of structured fields” and “semantic validation should enforce correctness of their values in the specific business context.” 

PostgreSQL Specific Examples

In addition to language-specific input validation, using constraints when designing a database, specifically check constraints, can help as another line of defense against SQLi. From the PostgreSQL documentation regarding check constraints “A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. For instance, to require positive product prices, you could use:”

 


/* Adding a check that requires positive product prices */

CREATE TABLE products (

    productnum integer,

    name text,

    price numeric CHECK (price > 0)

);

 

This can also be accomplished on existing databases using ALTER TABLE. Subcommands such as ADD table_constraint, ALTER CONSTRAINT, and DROP CONSTRAINT also exist to help facilitate this.

Escape Untrusted Data

OWASP has a helpful guide called the Encode and Escape Data Checklist. Escaping data is a defensive technique that helps to stop a few types of attacks, including injection attacks. It does this by ensuring the data is formatted in such a way that it will not be interpreted as a command, or as code within the application or the database. In general, it is best practice to use escaping functions included in trusted libraries instead of attempting to write a function from scratch. 

PostgreSQL Specific Examples

For example, the PostgreSQL C library (libpq) has built-in escaping functions that can be used in conjunction with the command execution functions. PQescapeLiteral is used for escaping strings for inclusion in SQL commands. As stated in the official libpq documentation, “this is useful when inserting data values as literal constants in SQL commands. Certain characters (such as quotes and backslashes) must be escaped to prevent them from being interpreted specially by the SQL parser.”. The library also includes other useful escaping functions, such as PQescapeByteaConn (used to escape binary data), and PQescapeIdentifier (used to escape strings used as SQL identifiers such as table names). Like input validation, it is best practice to use these types of built in functions, instead of “rolling your own”.

Prepared Statements, Parameterized Queries and Stored Procedures

The CISA advisory recommends parameterized queries as a primary approach to defending against SQLi. They state that “during the design and development of a software product, developers should use parameterized queries with prepared statements to separate SQL code from user-supplied data to prevent this class of vulnerability. This separation ensures
the system treats user input as data and not executable code, thereby eliminating the risk of malicious user input being interpreted as a SQL statement.”. 

A parameterized query is one made in code such that values are passed in alongside the SQL statement, which has placeholder values denoting where the provided values will be inserted. A prepared statement is a reference to a pre-compiled query that is ready to accept parameters. Stored procedures are a sequence of instructions that let you store a series of queries frequently used by the application. Prepared statements and stored procedures can be combined to defend against SQLi.

When used properly, stored procedures have additional benefits. Their use can improve performance as they are compiled once, then stored in executable form. This executable code is cached, reducing memory requirements. Procedures also support reusable code, easier maintenance, and improved security. Procedures can control the processes and activities performed, protecting the database object and simplifying security. It is harder for attackers to insert commands into SQL statements inside procedures. 

PostgreSQL Specific Examples

 


/* Inserting data using a procedure */

procedure_demo=# CREATE OR REPLACE PROCEDURE genre_insert_data(GenreId integer, Name character varying)

procedure_demo-# LANGUAGE SQL

procedure_demo-# AS $$

procedure_demo$# INSERT INTO public.Genre VALUES (GenreId, Name);

procedure_demo$# $$;

CREATE PROCEDURE

procedure_demo=# CALL genre_insert_data(26,'Pop');

CALL procedure_demo=# select * from public.Genre where GenreId = 26;

 

Code Review

There are many methodologies that can be followed for code review. Whether you have chosen solo review, pair programming, small team review, cross-functional review or any of the other flavors, enforcing secure code requirements through code review can help to ensure the above practices are used and implemented consistently. Ultimately, the goal of code review is to improve code quality, foster collaboration within the team, and ensure that ownership of security practices is distributed among all team members.

Principle of Least Privilege

Least privilege is a security principle that advocates for granting only the minimum level of access or permissions necessary for users or processes to perform their required tasks. Applying the principle of least privilege to limit the impact of SQLi involves ensuring that database users and applications have only the necessary permissions to execute SQL queries and access data. This can be accomplished by assigning database users the minimum necessary permissions required for their specific tasks. For example, if a user or service account only needs to read data from certain tables, granting them read-only access to those specific tables, rather than full read-write access to the entire database can reduce the blast radius of a SQL injection attack by limiting or preventing the attacker’s ability to modify or delete data. 

Least privilege also works in conjunction with the prior mentioned stored procedures. Utilizing stored procedures to encapsulate database operations and enforce access controls by defining specific stored procedures for common database operations and granting execute permissions only on these procedures, restricts direct access to underlying tables, thus preventing SQL injection vulnerabilities.

EDB’s SQL Protect and Privilege Analysis 

For customers using EDBs Postgres Advanced Server (EPAS), EDB offers tools and features which can be combined with the previously described best practices. The first, SQL Protect, guards against different types of SQL injection attacks by examining incoming queries, identifying potential attacks using signatures, monitoring queries based on protected roles and providing comprehensive statistics and records of attack attempts. SQL Protect can also be configured to send alerts when malicious queries are detected, which can help enable a timely response to attack attempts.

The second, Privilege Analysis, is a newly available feature in EPAS v16 and is designed to assist in identifying unnecessarily granted privileges. EDB has created a tutorial called “How to Use Privilege Analysis to Identify [Un]Used Privileges”. This feature can help when trying to apply the principle of least privilege by “examining used and unused privileges”, allowing administrators to verify that users only have “the privilege and access needed to perform their work”. 

Conclusion

While SQLi is still one of the most prevalent and damaging types of vulnerabilities, there are effective ways to prevent it. Implementing common, and well supported secure code practices, combined with code review can help keep software safe for users. In most cases, implementing these defensive measures provide additional benefits, like improving performance, making code maintenance easier, and helping to spread secure coding knowledge across development teams. The CISA advisory also notes the importance of building an organizational structure that supports achieving security goals, including transparency and accountability for the security of software. To support this call for transparency, in future posts we will share more information about the challenges and importance of Governance in supporting security practices, share more details about our own practices and continue to stay up to date on emerging security advisories and regulations. 

References

Share this

More Blogs