Securing Azure Logic Apps: Preventing SQL Injection in SQL Server CRUD Operations
SQL Injection has been featured prominently on the OWASP Top 10 list for decades; we have seen a lot of movement where it has gone from #1 to #3 in the past five years, which is promising. The reason that SQL infection is featured so prominently is because it is an attack vector that is easy for threat agents to detect and, thanks to tools such as SQLMap, easy to perform as well. Unfortunately, SQL Injection is also one of those attacks that, when successful, has a massive impact on the business and technical foundation of the company. SQL injection allows attackers to retrieve data that they shouldn’t be able to access, modify login information for users to allow them to login and perform actions as that user and take down entire systems by deleting data or dropping tables in the database. Ultimately, SQL Injection is one of those vulnerabilities that we do not want to see in our production applications. Fortunately, it is one of the easiest vulnerabilities to mitigate as well. This article will go through simple create, read, update and delete (CRUD) scenarios where SQL Injection can happen within the Azure Logic Apps and its usage of the SQL Server connector.
Why does SQL injection happen
We are receiving input from a source that we are leveraging in a query against a database. We need to query to retrieve an order by using the orderId or retrieve account information using an accountId. The problem lies with using data we are being passed from another source without performing any sanitization against it first. By using this data without sanitizing, we are trusting that the source is not going to be malicious. Whenever we implicitly trust a source of data, we open ourselves up to attacks from a threat agent that can manipulate or pass data to us. As such, we need to take proper measures to protect our Logic App workflows when working with data from other sources. We need to take the untrusted input data and treat it in such a way that we can trust it.
A vulnerable endpoint using “Execute a SQL query” action
Let’s say we are hosting an endpoint that allows the user to pass in an “id” to retrieve an account. We use an “Execute a SQL query” action to retrieve the data and respond with it back to the caller. We would have an action that looks like the image below to make our query.
We are expecting the caller to provide whole number identifiers. In the happy path case, the user will pass in “1” or “2”. We are implicitly trusting that the caller is not going to try any kind of injection. A threat agent who is attempting an injection attack will modify the payload to complete our query and pass in their own query using SQL Chaining. The gif below shows a success SQL injection attack against an Azure Logic App that uses the execute SQL query action.
Mitigate CRUD Queries with Parameterization
The SQL Server connector in Azure Logic Apps provides actions to create, read, update and delete data within a SQL Server database. These actions are:
The main way to prevent SQL Injection is to use parameterization. The actions above will let you pass in parameters to perform actions. The code underneath that runs within these actions will parameterize your input, so that it is sanitized, and is therefore protected against SQL Injection.
The one action where parameterization is not possible is “Execute a SQL query”. This action allows us to write and execute raw SQL against a SQL Server database. It should raise red flags in a developers head as soon as they start using this action. It is likely that in a lot of cases, we do not need to write raw SQL and can instead leverage one of the actions to perform what we need and pass data to that action. The table below shows the method of using parameterization over executing raw queries to prevent SQL injection.
When to use execute a query
This should only be used to do direct queries against the database without any outside inputs. Queries like “SELECT FROM ACCOUNT WHERE ACTIVE = 1” to retrieve all active accounts. Or we could do “SELECT ACTIVE, COUNT() FROM ACCOUNT GROUP BY ACTIVE” to get the total number of active and inactive accounts. The key with both of these queries is that we are not introducing any dynamic inputs from outside of our logic app workflow.
Implement parameterization in your Logic App workflows
Understanding and utilizing parameterization is crucial for safeguarding against SQL Injection attacks. Azure Developers must be cautious when tempted to execute raw SQL queries and always consider alternative methods that allow for safe parameter passing. By adhering to best practices and leveraging pre-built Logic Apps actions that inherently sanitize inputs, we can significantly reduce the risk of SQL Injection vulnerabilities. The use of direct SQL queries should be reserved for scenarios without external inputs, ensuring that our applications remain robust and secure. Implement parameterization in your Logic App workflows that use the SQL Server connector and protect your systems from potential security threats.