Preventing SQL Injection Attacks in Postgres

More and more frequently, customers are being given access to company databases for purposes of account management, receiving customer support, or placing and tracking an order. Although this provides great convenience for the end user, it also opens the database up to certain vulnerabilities. Any feature that allows a user to search or edit content within a database runs the risk of an attacker exploiting this feature to obtain additional access to company information. When a user subverts the original intent of your SQL statements, this is known as a SQL injection attack.

What are SQL injection attacks?

SQL injection attacks are malicious attacks in which data is “injected” into your SQL query using certain destructive phrases or unescaped parameters. Hackers use this technique to gain access to business data and personal information, as well as modify or delete the content within your database. One of the most recent examples of a SQLi attack is the “Meow” attack, which wiped out nearly 4,000 unsecured databases in July 2020.

Typically, attacks occur through a web interface, such as a web page or application, that allows users to search the database or log in to an account. Many times this is done through automated scripts. If inputs on your web application are not properly validated and escaped, attackers can use these features to alter your database with destructive phrases such as DROP TABLE, DELETE FROM, INSERT, UPDATE, a double-dashed sequence ‘--’, or a semicolon ;, or download your entire database using SELECT statements.

Needless to say, the fallout from these kinds of attacks can be devastating, not only to the contents of your database, but to the integrity and reputation of your company as well.

Types of SQL injection Attacks

SQL injection attacks fall under three main categories: In-band (also known as “classic” or “simple” attacks), inferential (or “blind”), and out-of-band attacks.

In-band Attacks

In a simple, or in-band attack, commands are sent to the database in order to extract content and return results directly to the end user. Even error messages from invalid inputs can be used to determine the underlying structure of the database, which can then be exploited to extract data from other tables in the database through restructuring the original query. For example, if a website lets customers to track their orders online by calling the database with a SQL statement such as the following:

SELECT * FROM orders WHERE order_id= ‘ user + input ’

A hacker could exploit this statement to extract additional information from the database by appending a simple UNION SELECT statement onto an order number. Something like:

SELECT * FROM orders WHERE order_id= ‘123 UNION SELECT username, password from customers --’

would return information about order number 123, along with the usernames and passwords of all customers in the database.

With inferential, or blind attacks, an attacker sends commands to the database but does not receive any content back. Instead, this technique is used to learn as much about the system’s behavior as possible. There are two main types of inferential attacks, boolean based and time based. Boolean based attacks use true or false statements to extract information based on whether the web browser reloads with either an empty or non-empty response. Time based attacks can use functions like pg_sleep, or be used in conjunction with boolean based attacks to gather information from the database based on the time it takes the web browser to reload.

Out-of-band Attacks

Out-of-band attacks are reserved for cases when the data cannot be extracted through the same channel that is gathering information about the system, such as when dealing with slow, unstable systems, or systems under heavy loads. Hackers use this technique to send data to an external server that the hacker controls using DNS requests or UTL_HTTP packages.

Prevention

Now that we know what SQL injection attacks are, the question is, how do you prevent them from happening? Luckily, there are some simple steps you can take to protect yourself and your system from attacks.

Validating inputs

First and foremost, inputs from SQL statements should be validated, or “sanitized”, before being sent to the database. This can be done by making use of parameterized SQL statements, which separate the query statements from the data. Parameterized statements use stored queries that have markers, known as parameters, to represent the input data. Instead of parsing the query and the data as a single string, the database reads only the stored query as query language, allowing user inputs to be sent as a list of parameters that the database can treat solely as data.

As an added precaution, parameterized statements can be set to deny the caller the right to execute certain commands such as DROP TABLE. You can also run all inputs through a filter to weed out certain destructive phrases such as DROP TABLE, DELETE FROM, SELECT * FROM, a double-dashed sequence ‘--’, or a semicolon ;.

Proactive monitoring

The best way to find suspicious activity is to be proactive in looking for suspicious activity. Database audits, along with regularly reviewing log statements, will help identify any potential threats to your system.

Audit your database

Regular auditing and vacuuming will help maintain your PostgreSQL environment. Extensions like pg_audit provide a deeper level of detailed session and object logging than the standard logging found in PostgreSQL. This level of detail can help pinpoint any unusual or irregular queries; for example, queries to system tables. System tables, like those found under the information_schema, are not regularly accessed by users and should be treated with suspicion. Auditing also provides information about which tables, views, procedures, and functions are no longer in use. Removing these items reduces the chances of an attacker injecting them with malicious code which can go undetected for years. Check out this blog post for more information about auditing your database operations.

Adjust default settings in PostgreSQL

Consider adjusting the default PostgreSQL settings to maximize information captured in your log files. A good place to start is with the log_statement and log_min_error_statement parameters in your postgresql.conf file. (Note: these changes must be set by a superuser.)

log_statement = all
log_min_error_statement = ERROR

As these settings will increase the size of your log files, you will need to adjust your storage settings and log backup procedures accordingly. By setting the log_statement parameter to “all”, log files will capture all SQL statements executed in the database. The only exception would be statements that contain simple syntax errors because these statements fail before the execute phase. These statements can be captured by setting the log_min_error_statement parameter to “ERROR”.

More information on logging can be found in the PostgreSQL documentation.

Securing your system

While monitoring your system and validating inputs are the best ways to guard against attacks, there are a few additional steps you can take to secure your system.

Turn off external customer user visibility of database errors on live production sites to avoid revealing information about system structure and behavior.
Make sure any applications which connect to the database do not use accounts with administrator privileges, especially web applications.
Keep your OS and PostgreSQL up to date with the latest security patches.
Use vulnerability scanners and web application firewalls (WAFs) to make sure your applications are secure before being released to production servers.

Detecting Attacks

While the steps above will help protect your system against attacks, nothing is infallible. Hackers are relentlessly refining their techniques, and all systems are vulnerable to attack.

If you suspect your database has been compromised, the first thing you should do is shut down your system. Then, go through and analyze your logs, to find any potential holes that may have been exploited. Some key things to look for are:

SQL errors. Attacks almost always generate SQL errors so this is a good place to start. It’s also the best way to detect an attack while it’s happening.
Permissions errors. Any time an unauthorized user tries to access or modify the database, it could indicate an attack.
References to system tables. Attackers don’t always know your specific table names, so queries to system tables should raise a red flag.

Once you have identified and patched any potential points of entry, it is a good idea to go back and review your application code. Using version control when writing new code and procedures will allow you to detect where SQL injection holes originated. On this note, it always helps to have another application developer check your code to point out any potential areas that could be exploited.

Conclusion

SQL injection attacks can be detrimental not only to your data, but to your company’s reputation and integrity as well. Unfortunately, there is no silver bullet solution to prevent these attacks, but by implementing simple safeguards, such as sanitizing SQL inputs and proactive monitoring, you can mitigate your chances of an attack, while detailed logging can provide you with a roadmap for identifying potential vulnerabilities within your system.

Latest Articles