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.
Is my GraphQL application vulnerable to SQL Injections?
This is now a question you should ask yourself… but figuring out the answer remains a challenge! Indeed, GraphQL is new and lacks just lacked the proper tooling, many development teams are just skipping security...
That's why at Escape, we created the first GraphQL Security scanner that allows developers to find and fix vulnerabilities in GraphQL applications during the development lifecycle before they even reach production!
It understands the business logic of GraphQL APIs and looks for more than 50 kinds of vulnerabilities so that you never have to worry again about:
- Injections (SQL, NoSQL, XSS…) and requests forgery
- Resolver performance (GraphQL bombs, N+1 issues, cyclic queries, query complexity DOS…)
- Tenant isolation (access control, data segregation between users…)
- Sensitive data leaks (personally identifiable information, tokens, stack traces, secrets…)
- … and more than 50+ advanced security issues!
We constantly update our engine with state-of-the-art GraphQL Security research so that you never have to worry about the security of your GraphQL application again!
Start monitoring the security of your endpoints today with a 7-day free trial.
Get results in one minute – no credit card required.
How to remediate?
To begin with, the 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 behavior 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 authorized 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 synchronization 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 behavior 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