Kibana: Dev Tools cURL converter not giving the correct curl statement

[rdesanno]

I can run the following query in Dev Tools/Console:

POST _sql
{
"query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test\" WHERE agent.hostname = 'host-1-1' "
}*

When I select the option to "Copy as cURL", it returns the following command:

curl -XPOST "http://localhost:9200/_sql" -H 'Content-Type: application/json' -d'{ "query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test\" WHERE agent.hostname = \"host-1-1\" "}'*

Executing it results in an error. The proper syntax should be:

curl -XPOST "http://localhost:9200/_sql" -H 'Content-Type: application/json' -d'{ "query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \\"test\\" WHERE agent.hostname = \"host-1-1\" "}'*

Where the index needs to have the escape characters escaped too. What was originally presented will work as long as there isnt a WHERE clause attached.

[kobelb]

Hey @rdesanno, which version of Kibana are you using? I'm unable to replicate what you're reporting in Kibana 7.4. Kibana 7.4 is requiring the double-quotes within the query to be escaped for this query to be executed:

[rdesanno]

The query works correctly however when you click on the wrench to get the curl command, it doesn't properly escape.

Looking back at my original post, github reformatted it so let me try pasting what the correct curl should be

curl -XPOST "http://localhost:9200/_sql" -H 'Content-Type: application/json' -d'{ "query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \\\"test\\\" WHERE agent.hostname = "host-1-1" "}'*

[rdesanno]

In answer to your version question, here's what i'm running:

$ rpm -qa | egrep 'logstash|kibana|elasticsearch'
kibana-7.3.2-1.x86_64
elasticsearch-7.3.2-1.x86_64
logstash-7.3.2-1.noarch

[elasticmachine]

Pinging @elastic/es-ui (Team:Elasticsearch UI)

[jloleysens]

Hi @rdesanno !

I tried to reproduce the behaviour you described, but with the statement:

POST _sql
{
"query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM "test" WHERE agent.hostname = 'host-1-1' "
}

you provided I also get a syntax error as @kobelb did. After updating to (escape quotes around test):

POST _sql
{
"query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test\" WHERE agent.hostname = 'host-1-1' "
}

I can copy to cURL to get the follow valid cURL command:

curl -XPOST "http://localhost:9200/_sql" -H 'Content-Type: application/json' -d'{"query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test\" WHERE agent.hostname = \"host-1-1\" "}'

I am closing this for now, feel free to re-open if we missed something.

[ppisljar]

Reproduction steps (Kibana -->DevTools):
1)

DELETE claude_index
PUT claude_index/_doc/1
{
  "prenom":"claude",
  "nom":"kebab"
}

2)

POST /_sql?format=txt
{
  "query": "SELECT prenom FROM claude_index WHERE prenom = 'claude' ",
  "fetch_size": 1
}

2.1) Outputs:

    prenom     
---------------
claude 
````

3) You now capture the curl command from step 2:

curl -k -u elastic:my_password -XPOST "http://27945f1d06784a96aa5fd218c7668667.containerhost:9244/_sql?format=txt" -H 'Content-Type: application/json' -d'{ "query": "SELECT prenom FROM claude_index WHERE prenom = \"claude\" ", "fetch_size": 1}'

3.1) Executing the previous curl via command line generates:

{"error":{"root_cause":[{"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:48: Unknown column [claude]"}],"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:48: Unknown column [claude]"},"

3.2) Please note that if you copy&paste the same curl command back to the same Kibana DevTools session, it does generate the same error message

4) After a lot of tests, the working curl command was (please note the subtle difference when escaping the quotes towards the end)

curl -k -u elastic:my_passowrd -XPOST https://27945f1d06784a96aa5fd218c7668667.35.187.62.242.ip.es.io:9243/_sql?format=txt -H 'Content-Type: application/json' -d'{ "query": "SELECT prenom FROM claude_index WHERE prenom = '\''claude'\''","fetch_size": 1}'
```