Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Nifi ExecuteSQL prepared statements to prevent SQL Injection?

Highlighted

Nifi ExecuteSQL prepared statements to prevent SQL Injection?

New Contributor

In creating a Nifi flow, I noticed ExecuteSQL does not support prepared statements like PutSQL does. Is there a reason for this?

Is there a method to get around it besides creating my own processor which adds the functionality?

3 REPLIES 3

Re: Nifi ExecuteSQL prepared statements to prevent SQL Injection?

New Contributor

@Shu

For example, the PutSQL documentation states the following:

The SQL command may use the ? to escape parameters. In this case, the parameters to use must exist as FlowFile attributes with the naming convention sql.args.N.type and sql.args.N.value, where N is a positive integer. The sql.args.N.type is expected to be a number indicating the JDBC Type. The content of the FlowFile is expected to be in UTF-8 format.

This allows SQL to be escaped, preventing attacks such as SQL Injection.

Unfortunately, ExecuteSQL does not contain the same functionality. As a result, SELECT statements are vulnerable to SQL injection. I'd like to know if there is a known workaround, or if we must just wait until this functionality is implemented.

Re: Nifi ExecuteSQL prepared statements to prevent SQL Injection?

Explorer

Did you find a solution for preventing vulnerabilities in ExecuteSQL?

Can any of the escape(Json|Xml|Csv…) language expression functions be useful, or how can we make robust selects in nifi with any kind of input characters in the SQL?

Often the needed escaping is specific for the database in question, so I'm guessing a manual use of expression functions for escaping is not very suitable without indicating the database in use...

Re: Nifi ExecuteSQL prepared statements to prevent SQL Injection?

Super Guru

NIFI-978 addresses this capability, I have a Pull Request up with the improvement, perhaps it will make it into NiFi 1.6.0.

Don't have an account?
Coming from Hortonworks? Activate your account here