Handling special characters when querying via the http endpoint

CrateDB
1

I have a query that I tested with the WEB GUI and it works:

select regexp_replace("HOST",'^.*\((\S+)\)','$1') FROM services.route WHERE regexp_replace("HOST",'^.*\((\S+)\)','$1') != '???' limit 100;

I would like to send this query to the database as part of a shell script and I think I will be using the http endpoint. problem is I cannot figure out how to handle the special characters and which ones are special characters:

curl -sS -H 'Content-Type: application/json' -X POST '10.10.10.195:4200/_sql' -d '{"stmt":"select regexp_replace(\"HOST\",'^.*\((\S+)\)','$1') FROM services.route WHERE regexp_replace(\"HOST\",'^.*\((\S+)\)','$1') != '???' limit 100"}'
bash: syntax error near unexpected token `('

What characters I should be worried about?

Thanks,

2

Hi @asavran,

try this version:

curl -sS -H 'Content-Type: application/json' -X POST '10.10.10.195:4200/_sql' -d '{"stmt": "select regexp_replace(\"HOST\",'\''^.*\\((\\S+)\\)'\'','\''$1'\'') FROM services.route WHERE regexp_replace(\"HOST\",'\''^.*\\((\\S+)\\)'\'','\''$1'\'') != '\''???'\'' limit 100"}'

There are several aspects relating to escaping:

  1. As the -d parameter is wrapped in single quotes, occurrences of single quotes within that string are replaced with '\''
  2. "HOST" occurs within a JSON value that is wrapped in double-quotes, so it needs to be changed to \"HOST\"
  3. The backslash within the regular expression (second argument of regexp_replace) needs to be escaped so bash won’t interpret it as a regular escape sequence.