Prevent SQL Injection attacks on your PostgreSQL servers

SQL injections are one of the most common and popular application attack vectors used with the goal of retrieving sensitive data from companies. When you hear about stolen financial information, defaced web sites or even systems takeover, they often happen through complex hacking attempt, which in many cases starts with common SQL injection vulnerabilities being exploited.  Fortunately, you can follow some very easy techniques to prevent SQL injection affecting your system with PostgreSQL backend.

What is SQL Injection?

SQL injection (SQLi) is a common cybersecurity exploit that targets commercial and open-source relational databases using specifically crafted SQL statements to trick the systems into doing unexpected and undesired things. SQL injection attacks allow penetrators to spoof identity, tamper with existing data stored in databases, cause repudiation issues such as voiding transactions or changing balances and ultimately quite often to become administrators of the database server.  SQL Injection is more common with older web development platforms, such as PHP, ASP, JSP, CGI due to the prevalence of older data access interfaces, but can occur with newer platforms as well, when not taking advantage of available methods to reduce this vulnerability. 

Pic 1. Typical SQL Injection diagram courtesy of Cloudflare 

Within the last 20 years, many SQL injection attacks have targeted large websites, businesses, and social media marketing platforms. Many of these attacks resulted in serious data breaches. A couple of notable examples are listed below:

• 7-Eleven breach. a group of attackers used SQL injection to penetrate corporate systems at several companies, primarily the 7-Eleven retail chain, stealing 130 million charge card numbers.
• Ghost Shell attack. Hackers from APT group Team GhostShell targeted 53 universities using the SQL injection and stole and published 36,000 personal records owned by students, faculty, and staff.
• Freepik breach. In 2020, Freepik, one of the largest online graphic resources sites in the world with 18 million monthly unique users, says that hackers were able to steal emails and password hashes for 8.3M Freepik and Flaticon users in an SQL injection attack against the company's Flaticon website.

Types of SQL Injection attack 

SQL Injection (SQLi) is commonly classified to several types:

• Union-based SQL Injection– Union-based SQL Injection represents the most popular type of SQL injection and uses the UNION operator in SQL.  The UNION operator is used to combine the result-set of two or more SELECT statements to retrieve data from the database. 
• Error Based SQL Injection– this method is usually deployed against Microsoft SQL Server databases. In this attack, the malicious actor causes an application to display an error. originating from database.  It manipulates the database into generating an error that informs the malicious actor of the database’s structure.
• Blind SQL Injection – in this attack, no error messages are received from the database; We extract the data by submitting queries to the database. Blind SQL injections can be divided into Boolean-based SQL Injection and time-based SQL Injection.

Example of SQL Injection

This example shows how an attacker can use SQL injection to circumvent an application’s SQL Based authentication and gain administrator privileges. 

Consider a simple authentication system using a database table with usernames and passwords. A user’s POST request will provide the variables user and password, and these are inserted into a SQL statement:

sql = "SELECT id FROM users WHERE username='" + user + "' AND password='" + password + "'"

The problem here is that the SQL statement uses string concatenation to combine data. The attacker can provide a string like this instead of the password string variable:

password' OR 5=5

Finally, we have a resulting SQL query that will be run against the database:

SELECT id FROM users WHERE username='user' AND password='pass' OR 1=1'

Because 1=1 is a condition that always evaluates to true, the entire WHERE statement will be true, regardless of the username or password provided. Moreover, WHERE statement will return the first ID from the users table, which is commonly the administrator. This means the attacker can access the application without authentication, and also has administrator privileges. 

Preventing SQL Injection attacks

There are number of methods for reducing the risk of SQL injection. As a best practice, several strategies should be utilized. Let’s look at most popular implementations:

• Using Parameterized Queries.  

  At its core, this method separates SQL logic from the data being passed. Using placeholders instead of directly embedding user input into queries, the database strictly recognizes the input as data.  This means that even if an attacker tries to insert malicious code, the database won’t execute it as a command. As a developer, adopting parameterized queries is not just a best practice; it’s a fundamental shift in how user input is processed, ensuring a higher level of security.

• Escape All User Supplied Input.  When writing SQL, specific characters or words have particular meaning. For example, the ‘*’ character means “any” and the words “OR” is a conditional. To circumvent users who enter these characters either accidentally or more likely maliciously into an API request to the database, user supplied input can be escaped. Escaping a character is the way of telling the database not to parse it as a command or conditional but instead treat it as literal input, aka string.
• Enforce Least Privilege. As a general rule, in all instances where a website needs to use dynamic SQL, it is important to reduce the exposure to SQL injection by limiting permissions of your application login\user to the most marrow scope required to execute the relevant query. This means that an administrative account should never be application login\user and executing SQL commands as a result of the API call from an unauthorized request. Enforcing least privilege can help reduce the risks of dynamic SQL queries.

Resources

For more information on SQL injection, as well as security best practices with Postgres Flexible Server see following:

1. Security - Azure Database for PostgreSQL - Flexible Server | Microsoft Learn
2. SQl Injection: example of SQL Injections and Recommendations to avoid it. - Microsoft Community Hub
3. Postgres SQL Injection Cheat Sheet | pentestmonkey
4. Investigation: A Pentesting PostgreSQL with SQL Injections (onsecurity.io)

To learn more about our Flexible Server managed service, see the Azure Database for PostgreSQL service page. We’re always eager to hear customer feedback, so please reach out to us at Ask Azure DB for PostgreSQL.