How to prevent SQL injection in Mule Applications?

Question

Are there best practices how to avoid SQL injection in case of Mule Applications?

I guess the general guide-lines "how to avoid SQL injection" would work here as well...

Primary Defenses:

Option 1: Use of Prepared Statements (with Parameterized Queries)
Option 2: Use of Stored Procedures
Option 3: White List Input Validation
Option 4: Escaping All User Supplied Input

Additional Defenses:

Also: Enforcing Least Privilege
Also: Performing White List Input Validation as a Secondary Defense

But is there any built-in support, that helps to avoid SQL injection?

Options 2 and 4 are but a hoax. You must go for #1 and #3. Note that nowhere a white list is an "additional" defense, it's a first line defense, should be used everywhere when #1 is inapplicable — Your Common Sense, Mar 12 '18 at 16:10

Attila · Accepted Answer · 2018-03-16T14:07:06.907

According to a recent blog post (October 12 2017):

There’s a brand new Mule 4 Anypoint Connector for Database (DB) that can be userd to connect to different relational Database engines (MySQL database, Microsoft SQL Server database, PostgreSQL database, Oracle Database, and more).

This new connector is only available in Mule 4.

Mule 4

The select operation introduced by the connector allows to use a parametrized SQL query, like in the following XML snippet:

<set-variable variableName="table" value="PLANET"/>
<db:select config-ref="dbConfig">
 <db:sql>#["SELECT * FROM $(vars.table) WHERE name = :name"]</db:sql>
 <db:input-parameters>
   #[{'name' : payload}]
 </db:input-parameters>
</db:select>

Using this approach the query is supposed to become immune to SQL injection attacks.

This will force the Mule Runtime to use a prepared statement, which is only compiled once. Since it is precompiled; the parameters will be handled purely as data and they can't be used to change the meaning of the SQL-query. Since the query won't be recompiled; the passed parameters can't be used to alter the SQL query.

For the details see the following StackOverflow question:

How does a PreparedStatement avoid or prevent SQL injection?

This helps to avoid some kinds of attacks, but this solution won't make the system 100% immune to SQL-injection.

For details see the following question on the secury StackExchange:

Are prepared statements 100% safe against SQL injection?

According to this; by using prepared statements with parameterized queries and white list input validation you can avoid some/most types of attacks, but don't think, that your system is 100% secure.

Next to prepared statements with parameterized queries and user input sanitization you also need to have other lines of defence in place.

Mule 3.9

In Mule 3.9 you can use parameterized-query.

This is implemented by the "SimpleQueryTemplateParser" class in the runtime.

This is going to replace the MEL expressions in your query text with question marks, creating a parametrized SQL statement.

Even if the SQL statement with the MEL expressions looks a bit suspicious, it won't be simply concatenated.

For exqample in the following case:

SELECT * from employee where name = #[flowVars.name]

The #[flowVars.name] part is going to be replaced with a question mark and it will be recognized as an SQL query parameter and the following query will be created:

SELECT * from employee where name = ?

The MEL expression will be recognized and evaluated. It will be added as a parameter.

How to prevent SQL injection in Mule Applications?

2 Answers2