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.
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:
SQL Injection (SQLi) is commonly classified to several types:
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.
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:
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.
For more information on SQL injection, as well as security best practices with Postgres Flexible Server see following:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.