You receive a call in the middle of the night from the SRE team:
All production data has been deleted from your company's various relational databases.
Many of the company's internal services are therefore down. You absolutely need to fix the problem immediately and identify the problem.
Once you log into the company's system you find that much of the information in your databases has been lost.
Fortunately, you have a daily data backup so you can quickly restore the database to its previous state. The day's data was lost, but it's better than nothing!
You had restricted access to the databases and set permissions for the various users.
Was it a mistake by one of the administrators? Was it a failure of your servers? A malicious act?
What has happened?
At first glance, there has been no unusual activity or connection...
Digging a little deeper, you find that the service account used by one of your applications is the cause of many data deletions. In particular, this account is used by a GraphQL client that needs to perform queries on your internal databases.
This is strange since this application is only supposed to allow access to product data from your online sales site. Instead of simple queries, you find the following:
SELECT * FROM Products WHERE ProductId = 15; DROP TABLE Suppliers; SELECT * FROM Orders WHERE OrderId = 16 AND 1=1; SELECT * FROM Users WHERE Login = 'Administrator' -- ' AND Password = ' '
These queries have in common that they were not anticipated when developing the API.
It seems that users have managed to insert additional commands or alter the SQL queries originally programmed into your site.
What is going on?
This type of vulnerability is called SQL code injection.
It allows a user to modify an unprotected query to perform operations that they would not normally have access to. These injections are enabled by the need to integrate user-supplied information into dynamic queries: the resulting query is different depending on what is requested by the user.
For instance, on a site with a product search bar, the SQL query created could have the following form:
sqlRequest = "SELECT * FROM Products WHERE Name CONTAINS '" + USER_INPUT + "' ";
This query will work fine if the user types "Table", but what if they type "Table' OR 1=1 --" in the search box?
The query would then become :
SELECT * FROM Products WHERE Name CONTAINS 'Table' OR 1=1 --';
The impact here is limited, but the query would return all existing data in that table of the database. For other more sensitive tables, this may be problematic.
Try again, this time with "Table'; DROP TABLE Users --". This time the impact may be even greater, as the user may follow up with a query that has nothing to do with the original query, within the limits of the actions that the SQL client used by GraphQL is allowed to perform on the database.
If you want to catch injection vulnerabilities and 100+ other GraphQL security vulnerabilities before it's too late, checkout Escape. Run hundreds of security scans in your CI/CD 🚀
To begin with, a first principle to keep in mind is to never trust the user and their inputs.
Whatever the user's intentions, it is essential to check what they type. One field is expecting a date, is it really a number that is being entered? Does the query contain apostrophes or inverted commas?
Depending on the type of relational database you use, there are many ways to inject SQL code into a query. Fortunately, most of these can be rendered ineffective by simple changes in the way you build your "dynamic" queries.
Here are a few recommendations that can be applied to a wide range of databases:
Use Prepared statements
Instead of concatenating variables that come from the user with pieces of SQL query, it is possible to write the queries on one side and then pass the arguments to the database engine. Depending on the engine and driver, this can be done with a query like
SELECT * FROM Users WHERE Login = :login or
SELECT * FROM Users WHERE Login = ?, and then the login argument (
?) will be passed and cannot affect the query.
Use Stored Procedures
Stored Procedures are quite similar in their behaviour to Prepared Statements. The main difference is that Stored Procedures are sets of queries or actions directly stored as objects in the database. They can then be invoked by providing arguments, which again cannot be interpreted as code.
Implement validation of user-entered arguments
It is possible to set up validation mechanisms for each of your requests. They can check, for instance, the type of data expected, an authorised list of keywords or, on the contrary, a list of blocked words, or regular expressions (e-mail address, date of birth, etc.).
Clean up user input
This consists of removing a whole set of specific characters or character strings from the user request. This method is more radical but generally does not allow to counter all possible forms of injection (and there exist many!).
Monitor the content returned by the database before it is displayed to the user
This check can also be effective. For each query, you can check if what is returned by the database has the right form (number of columns, number of rows, data types, ...). If not, you can block the query for example.
In addition, other general principles apply:
Apply the Least Privilege principle
For each of your user accounts that have access to a database, make sure that they have rights that match only what they should be able to do. For example, a database client who only needs to consult data will only need read rights and only on the tables that concern him.
Separate your data according to their applications
It can be dangerous to connect public interfaces to data sources containing internal data. A good idea may be to create separate databases and set up regular one-way synchronisation mechanisms for example.
Set up a monitoring system for the queries made
Here again, it is interesting to monitor which queries are carried out and to set up alerts if unusual behaviour is detected (a large number of data returned, a large number of queries carried out, large deletion of data, etc.).
In conclusion, SQL injection attacks can have a strong impact but can be easily mitigated. It is therefore important to audit your applications regularly and try to follow the above guidelines.
- About input validation: https://cheatsheetseries.owasp.org/cheatsheets/Input_Validation_Cheat_Sheet.html
- About SQL Injection in general: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html
- Applied example with sqlite3: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html