1

I'm sending requests via ListenHttp processor, for example

curl -X POST --data "myschema.mytable" http://localhost:44221/contentListener

how can I then use that in my DB query?

I want to execute in the ExecuteSQL processor select * from myschema.mytable

EldadT
  • 912
  • 7
  • 21
  • `myschema.mytable` should appear in flow file content. possible to use replacetext to convert content to desired query. – daggett Nov 17 '19 at 10:25

1 Answers1

4

As written by @daggett in the comments, the body of the post request is written to the FlowFile content.

You may extract the FlowFile content into an attribute using the ExtractText processor. In case the whole request body consists of the table name you may use (.+) as RegEx to store it in an FlowFile attribute, as seen below.

enter image description here

This will result in the whole content being written to the defined attribute.

enter image description here

To prevent you from being vulnerable against SQL injection you should escape any user based input in your queries. Thus instead of executing select * from myschema.mytable you should rather use select * from ? as query.

To let the ExecuteSQL processor known what to escape and insert instead of the question mark, you must add two attributes - sql.args.1.type and sql.args.1.value - to your FlowFile before sending it to the ExecuteSQL processor.

You may use an UpdateAttribute processor to do so. The type varchar is resembled by the number 12. To find out the correct mappings consult the documentation.

enter image description here enter image description here

You then may use it as part of your SQL-Query inside the ExecuteSQL processor.

enter image description here

Remarks

You should be aware of potential out-of-memory errors in case the content of the post requests gets to large. In general it is NOT recommended to store the whole content as attribute as attributes are generally stored in memory. In case you are able to alter the http request signature I recommend you to use a common format - e.g. JSON - and only extract the wanted value using a corresponding processor instead of ExtractText.

Providing the variable as HTTP header

As suggested by @mattyb in the comments, instead of using the body of a POST request you could also provide the value as HTTP header and read it from there. That way you may remove the ReplaceText processor entirely.

To do so you may alter the request to something along the lines of:

curl --request POST \
  --url http://localhost:31337/contentListener \
  --header 'databaseName: nyan.cat'

Inside the ListenHttp processor you have to set the value ^databaseName$ for the HTTP Headers to receive as Attributes (Regex) property.

This will result in the attribute databaseName being set as with the previous approach.

Endzeit
  • 4,810
  • 5
  • 29
  • 52
  • 2
    Might be easier (if possible for your use case) to send the table name as a header instead of data. If you set the `HTTP Headers to receive as Attributes (Regex)` property to `tableName` and then send `tableName: myschema.mytable` as a header in the `curl` command, then after ListenHttp it will already be in a flow file attribute so you can skip the ExtractText processor – mattyb Nov 17 '19 at 22:29
  • @mattyb I added a paragraph explaining the approach you suggested. Thanks. – Endzeit Nov 18 '19 at 18:10