CODEWITHSUNDEEP
oracle-databaseapache-nifi
user1637000
user1637000

Reputation: 162

Using Nifi ExecuteSQLRecord with parameterized SQL statements?

Can someone explain or show how Nifi's ExecuteSQLRecord would work with parameters? The documentation says:

If it is triggered by an incoming FlowFile, then attributes of that FlowFile will be available when evaluating the select query, and the query 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.

I've been able to use the HandleHttpRequest, ExtractText, to make this query work. curl -d "select * from MY_TABLE WHERE NAME = '1234'" http://localhost:5555

I'm unsure how I would update the ExecuteSQLRecord to make it work with parameters to avoid a sql injections.

enter image description here

Would I replace the 'test' with a ? and extract the attributes with another processor? I wish there was an example.

Upvotes: 0

Views: 4583

Answers (1)

Andy
Andy

Reputation: 14184

The query should be select * from MY_TABLE where NAME = '?', and then incoming flowfiles will need to have the following attributes (from your example):

  • sql.args.1.type: varchar
  • sql.args.1.value: 1234

For multiple parameters, it would follow this general pattern:

Query: select * from MY_TABLE where NAME = '?' and OTHER_COL = '?' ...

Flowfile attributes:

  • sql.args.1.type: varchar
  • sql.args.1.value: First Last
  • sql.args.2.type: integer
  • sql.args.2.value: 1234

...

Upvotes: 2

Related Questions