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?
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.
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...