I'm using the Table Iterator + a curl command to process updates to an API endpoint, and it all works great *except* when my string value contains a single or double quote. Matillion handles those gracefully in other contexts, but in the case of the Table Iterator, when assigning these values to variables, I get an error which indicates these characters aren't being escaped and so break the json. If I remove these characters completely in an earlier step, everything runs successfully.
So my question: is there any way for me to handle escaping single/double quotes in this scenario so they can be passed in my curl command? Other ideas on how to accomplish the overall goal (reading from snowflake table, posting to remote endpoint) are also welcome.
Hello, I would assume the error comes now from bash curl -d ' bracket got broke.. So I guess there's singlebrackets coming in the data?
1) identify the columns and replace the ' to \' and the " to \" before sending them through curl in a temp variable.
" should really be \" when escaped.
' should really be '\'' when escaped.
Note: POSTMAN is quite nice giving example code when it comes to really any API related task.
2) try escaping all the doubles from the bash code inside curl -d ''
example:
-d'{ \"aliases\": \"${ss_aliases}\",
etc..
(found from stackoverflow)
Hey Michael, thanks for the reply. Yes, the issue boils down to the curl -d ' bracket getting broken, because the data being loaded into the ${ss_name} variable, from Snowflake via the Table Iterator, contains single (and double) quotes on some rows. I've just been unable to figure out exactly how to escape the offending characters.
What I tried today was using a Python script to escape all problematic characters in the source string then assign it to a temporary variable as you suggested. Unfortunately, that's not working either. My steps were:
- Unhooked the Table Iterate from the Bash script and hooked it instead to a new orchestration job
- Inside that new orchestration job, I added a python script with this code:
- Next step in the job is my bash script, where I first echo and then try to use the new escaped variable in my curl command.
In Task Info, you can see that the python is indeed escaping everything, but that doesn't seem to yielding the desired result in Bash.
So after this, I put a manually escaped test string in the bash script and STILL got the same error as shown above.
Thoroughly confused now 
Next step will be to test this curl statement at the actual server command line and see if the escape characters work there.
Hiya,
manually, does it work if you escape it by
"name": "Women'\''s Small" ?
And in bash, you could do a sed for that specific value, to make the escaping happen.
Did some testing, hope this helps:
###
# Variables are exported into bash, but should be considered read-only:
# echo $myvar
###
dude="Women's Clothing"
echo $dude //Women's Clothing
//dude='Women's Clothing"
//echo $dude //Will not work
dude='Women'\''s Clothing'
echo $dude //Women's Clothing
dude="Women's Clothing"
echo $dude
//eval "dude='hello \${$dude}'" ##Crash
//echo $dude ##Breaks again due the '
dude="Women's Clothing"
echo $dude | sed 's/'\''/'\''\\'\'''\''/g'
//Women'\''s Clothing
dude=$(echo "$dude" | sed 's/'\''/'\''\\'\'''\''/g') ## This is the replacement you need to be doing.
echo "$dude"
//Women'\''s Clothing
eval "dude='hello $dude'"
echo $dude ##Works, but will not work again.
//eval "dude='hello $dude'" ##Crash
//echo $dude
Hey Michael,
Yes, escaping as you have above, Women'\''s Small, does work at the command line, as does the sed command, thank you so much!
However... referencing the bash variable in the curl command does *not* work, at least not the way I'm doing it, neither in the job nor at the actual command line. I just get the literal string "$dude" as my product name. Am I doing it wrong?
Interesting!
I managed to get the variable thrown into the curl by sending the whole curl command into a nested command.
try
CURLCOMMAND=$(<whole curl command here>)
As per example, I tried mimicing your case by following:
ss_name="Women's Clothing"
dude="${ss_name}"
dude=$(echo "$dude" | sed 's/'\''/'\''\\'\'''\''/g')
url='localhost'
curl_command=$(curl $url:8080 -d '{"Value": "$dude"}') ##Command gets execute and result is in the variable.
echo $curl_command
the curl command was successful, so the singlebrackets didn't break it. Otherwise couldnt' verify if the $dude was changed, so the $url I passed to confirm that variables are used. :)
Hope you get it to work, You are really close!
Hmm, I'm not able to reproduce your result on the Matillion server command line. See below, I'm setting $url = 'localhost' but it's not being parsed. The static value used in the second attempt appears in the error message.
Are you perhaps testing in a different environment? Really hope this isn't a weird CentOS thing...
I am also on CentOS, so no worries.
looks like you used $localhost as variable in the first part of your screenshot? try $url, localhost should be the value.
Ah, yeah that was a silly mistake! What I confirmed today though was that while the variable works as expected in your examples (and in mine, when I actually use the variable name!) it fails to work when surrounded by single quotes. So, since the whole -d portion of the curl command is wrapped in them, my $dude was not being processed.
After beating my head on that for a while I returned to my two-step Python version, where I applied my newfound knowledge on how to properly escape the single/double quotes in this context, and surprisingly I made it work! This method has the added benefit of letting me see the full Curl response, instead of just the first line when using curl_command.
Thanks again for the help in getting me on the right path. 
Excellent! Glad to hear you got it to work, this was valuable findings on this.
I was looking at sending back from bash the variable after the rework but bash's silly one-way variable messed that plan up.
The Python solution is excellent for this kind of transforming, the python step has specific meaning which can be used to other solutions so its not hidden in the bashcurl jungle :)
Thanks for this problem, was fun to ponder!
Br,
Michael
