It’s time to look at SQL injection. For a long time, it was the undisputed king of the OWASP Top 10, we’re talking years in a row. Despite being so old (like over 20 years), and while it’s fallen slightly from the top spot on that list, it’s still an incredibly popular and dangerous vulnerability.
Being a web security vulnerability, SQL injection (SQLi) is still one of the most common ‘hacking’ techniques used by attackers, as it allows them to manipulate a database and extract crucial information from it. On an even more alarming note, an attacker can make themselves the administrator of the database server and do some really devastating things like destroying databases, manipulating transactions, disclosing data, and making it vulnerable to more issues.
SQL (or Structured Query Language) is the language used to communicate with relational databases; it’s the query language used by developers, database administrators and applications to manage the huge amounts of data being generated every day.
Within an application, two contexts exist: one for data, the other for code. The code context tells computers what to execute and separates it from the data to be processed. SQL injection occurs when an attacker enters data that is mistakenly treated as code by the SQL interpreter, allowing them to gather valuable information from the application.
An SQL injection can be extremely harmful to any web application and has been the preferred technique behind so many high-profile breaches because it provides attackers with unauthorized access to critical data. They can see so much information, from things like usernames and passwords, to credit card details and personal identification numbers.
After gaining access to this data, attackers can take over accounts, reset passwords, go on an extended online shopping spree, or commit other (much worse) types of fraud.
But perhaps the most alarming thing about SQLi is that an attacker can, if undetected, maintain a backdoor into the system for long periods of time. As you can imagine, that would lead to repeat data breaches for however long that backdoor is kept open. Scary stuff.
Let’s look at a few examples to better understand how this looks in action.
SQLi includes various vulnerability techniques that can tackle different situations. What follows below are just some of the most common SQLi examples:
Okay, now let’s look at the three different SQLi types.
This is one of the most common, simple, and efficient types of SQL injection. In this type of attack, the same communication channel ise used to attack and retrieve the outcome or results.
Following are the two types of in-band SQLi attacks:
Inferential or blind SQLi attacks are more complicated and can take more time to exploit. On top of that, the attacker doesn’t actually get the attack results right away, which is what makes it a blind attack.
The attacker sends the payloads via HTTP requests to the database server to restructure the user’s database, then they observe the application’s response and behavior to see whether or not the attack succeeded.
These are two types of inferential SQLi attack:
This one is a more rare type of SQLi attack that depends on the database server’s enabled features. It happens in cases where the attacker can’t really use the other attack types.
For instance, if they can’t use the same communication channel for the in-band attack, or the HTTP response isn’t clear enough for them to work out the query results.
Moreover, it’s not that common because of its massive reliance on the database server’s ability to make HTTP or DNS requests to send the required data to the attacker.
Thankfully, the silver lining to SQL injection being so old and so common is that there are ways to prevent it from happening. Using these kinds of prevention techniques is not only a good coding habit, it’ll really bolster an organization’s security against SQLi.
There are multiple ways to secure database servers from these kinds of attacks, such as input validation, using a web application firewall (WAF), securing databases, employing third-party security teams or systems, and writing fool-proof SQL queries.
Let’s look at an example of preventing SQL injections in Python by employing one of the above mentioned security measures.
In this example, the attacker will be using a boolean-based blind SQL injection to grab important information from the system.
Assume there’s a table called “sample_data” in the database. This table stores usernames and passwords for the application's users.
Now allow the user to find a value from this database table by following commands:
Here, if the user enters a name in the search, for instance, Alicia, there will be no problem with the output.
However, if the user enters something like Alicia’; DROP TABLE sample_data; it will affect the database significantly.
The SQL statement should be changed to the following to prevent the attack from happening:
Now, the system will treat the user input as a string, even if the user tries to inject any SQL queries into it, and treat the user input as the name’s value only.
This simple change can prevent malicious activity in future queries and secure the system from user input attacks.
For this example, we’ll also use a database table named “sample_data” that stores the application’s user data.
A basic login page takes a username and password and the java file, which is a servlet (LoginServlet), validates them against the database to allow the login operation.
Using the “sample_data” table in the database, the system allows users to perform login operations by taking their credentials as the input.
There is a query in the LoginServlet file to accommodate the login operation, which is:
Following is the query for user login:
The system will work perfectly if the input is valid. For example, we’ll say the username is Alicia again, and the password is secret.
The system will return the data of the user with these credentials. However, an attacker can manipulate the user request using Postman and cURL for SQL injection.
For example, the hacker can send a dummy username ( Alicia) and the password ‘or ‘1’=’1’.
In this case, the username and password won’t match, but the condition ‘1’=’1’ will always be true so the login operation will be successful.
For prevention, we need to modify the LoginValidation code and use PreparedStatement instead of Statement for query execution. This change will prevent concatenating the username and password in the query and treat them as setter data to avoid SQL injection.
Below is the modified code for LoginValidation:
In this case, the PreparedStatement, the setters, and the underlying JDBC API will take care of the user input and prevent the SQL injection.
Now we’ll look at a few more examples in various languages to better understand what this looks like in action.
This example is insecure due to its use of `FromRawSql`. This method does not bind the parameters, or attempt to escape them. As such, this method should be avoided at all cost.
This example is secure due to the `FromSqlInterpolated`, which takes the interpolated values and parameterizes them.
While this is generally secure, it runs the risk of being very similar to `FromRawSql` which is not secure.
Hibernate offers two method for consturcting queries in a safe way through its `Native Query`, and `Named Query`. Both allow for specifying locations for parameters.
By annotating a `Query` attribute on a jplq repository interface, They can take multiple forms, and are parameterized.
When using the `pg` library, the `query` method allows for parameterization by providing parameter values through its second parameter.
The `sequelize` library provides a way to parameterize a query through its second argument, which takes settings for the query. This includes a list of values to bind to the query as a parameter, either by name or index.
We secure software through developer-driven security at the start of the software development lifecycle.Visit Secure Code Warrior