- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Replace single quote with two single quotes in a string
- Labels:
-
Apache Kafka
-
Apache NiFi
Created on 11-25-2018 01:40 AM - edited 09-16-2022 06:55 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am trying to consume data from kafka and insert into sql server. The kafka value has single quotes in it eg: "O'Keefe". PutSQL processor is failing to insert the string value into SQL server varchar column. I would like to replace the single quote with two single quotes in nifi so that I can insert the kafka value with single quote into sql server. Can you please give an example of replacetext processor how to achieve this.
Input: O'Keefe
Output: O''Keefe
Thanks
Created on 11-25-2018 07:14 PM - edited 08-17-2019 04:27 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for updating all details regards to the flow :).
Configure ReplaceText processor as
Search Value
(?s)(^.*$)
Replacement Value
Insert into test_schema.table1(topic, partition, offset, key, value) values ('${topic}','${partition}','${offset}','${key}','${'$1':replace("'","\"")}')
Character Set
UTF-8
Maximum Buffer Size
10 MB //change this value as per your flowfile size
Replacement Strategy
Regex Replace
Evaluation Mode
Entire text
In replacement value we are replacing all single quotes(') with double quotes(") in the captured group $1.
Created 11-25-2018 06:08 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is the current Nifi flow
ConsumeKafka -> UpdateAttribute -> ReplaceText -> PutSQL
*In UpdateAttribute I am capturing kafka.topic, kafka.offset,kafka.partition, kafka.key and assigning to variables.
topic=${kakfa.topic}
partition=${kakfa.partition}
offset=${kakfa.offset}
key=${kakfa.key}
*In ReplaceText processor I am replacing the flowfile content with insert statement.
searchvalue = (?s)(^.*$)
replacementvalue = Insert into test_schema.table1(topic, partition, offset, key, value)
values ('${topic}','${partition}','${offset}','${key}','$1')
But the nifi workflow is failing since the kafka value has single quotes in it as mentioned in initial post and sql server wont allow single quotes into the varchar column unless we double the single quote. I want to add one more ReplaceText processor and search the kafka value string and replace all the single quote occurrences with two single quotes. Please let me know what should be the search value and replacement value in the Replacetext processor. Please let me know if there is a better way to handle this scenario.
Thanks
Created on 11-25-2018 07:14 PM - edited 08-17-2019 04:27 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for updating all details regards to the flow :).
Configure ReplaceText processor as
Search Value
(?s)(^.*$)
Replacement Value
Insert into test_schema.table1(topic, partition, offset, key, value) values ('${topic}','${partition}','${offset}','${key}','${'$1':replace("'","\"")}')
Character Set
UTF-8
Maximum Buffer Size
10 MB //change this value as per your flowfile size
Replacement Strategy
Regex Replace
Evaluation Mode
Entire text
In replacement value we are replacing all single quotes(') with double quotes(") in the captured group $1.
Created 11-25-2018 09:57 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Shu, it worked 🙂
Created 11-26-2018 05:29 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
NO! if you are trying to escape input to generate a sql query you should never roll your own sanitization unless you fully trust the input.
THIS IS VULNERABLE TO SQL INJECTION!
You should be using the '?' parameter substitution in your putsql stage.