# Elastic SQL and cURL not playing nice
**URL:** https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971
**Category:** Elasticsearch
**Created:** [October 23, 2019, 10:06pm UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971 "2019-10-23T22:06:52Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@rdesanno](https://discuss.elastic.co/u/rdesanno)
#### Post date: [October 23, 2019, 10:06pm UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/1 "2019-10-23T22:06:53Z")
I'm having the hardest time trying to figure out what i'm doing wrong here and hope that someone else has some pointers
I can run the following query in Dev Tools
```
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' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5"
}
```
but the curl command that it gives me to use
`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\" AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5"}'`
results in an error. I'm confident its related to this part not being able to get parsed correctly:
`agent.hostname = \"host-1-1\"`
Depending on how I escape it, I get either an _Unexpected character_ error or _Found 1 problem(s)\nline 1:127: Unknown column [host-1-1]"_ but never the results I am looking for. I can query it the following way but its not optimal
`./bin/elasticsearch-sql-cli -d http://user:password@localhost:9200 < test.sql`
Anyone have any experience with something similar?
---
### Author:  [@stephenb](https://discuss.elastic.co/u/stephenb)
#### Post date: [October 24, 2019, 4:40am UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/2 "2019-10-24T04:40:26Z")
Hmm yes interesting....
I have constructed some similar data and queries and having same issues with the `WHERE` the `-` seems to be an issue, then when I compared to a value without an `-` then I am getting an `Unknown column` error.
What I did get to work is put the query in a `data.json` file then use this syntax
`curl -XPOST "http://localhost:9200/_sql?format=txt" -H 'Content-Type: application/json' -d @data.json`
And that worked.
But now I am curious too, suspect it is a shell + curl parsing issue
---
### Author:  [@DavidTurner](https://discuss.elastic.co/u/DavidTurner)
#### Post date: [October 24, 2019, 8:32am UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/3 "2019-10-24T08:32:44Z")
SQL distinguishes `"double-quoted-strings"` (which are things like column names possibly containing funny characters) from `'single-quoted-strings'` (which are string literals).
Your original query says `WHERE agent.hostname = 'host-1-1'` where `host-1-1` is a string literal since it uses single quotes.
The `curl` command says `WHERE agent.hostname = "host-1-1"` where `host-1-1` is a column name since it uses double quotes.
I think you want this:
```auto
curl 'http://localhost:9200/_sql' -H 'Content-type: application/json' --data-binary "{\"query\":\"SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \\\"test*\\\" WHERE agent.hostname = 'host-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5\"}"
```
Using double-quotes means you can include single-quotes in the string, but also means you must escape all the double-quotes. Alternatively you can use this:
```auto
curl 'http://localhost:9200/_sql' -H 'Content-type: application/json' --data-binary $'{"query":"SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \\\"test*\\\" WHERE agent.hostname = \'host-1-1\' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5"}'
```
A `$'dollar-single-quoted-string'` works like a `"double-quoted-string"` except double-quotes are no longer special so do not need escaping. You still need to escape the double-quotes inside the JSON string, however, because that's how JSON works, and then you need to escape the escape characters too, hence `FROM \\\"test*\\\"`.
However really I would do what @stephenb did and put the JSON in a file, because this avoids all the shell quoting issues.
---
### Author:  [@DavidTurner](https://discuss.elastic.co/u/DavidTurner)
#### Post date: [October 24, 2019, 8:45am UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/4 "2019-10-24T08:45:15Z")
By the way @rdesanno if the `curl` command coming from the Dev Tools doesn't match what happens in Dev Tools itself then I think that's a Kibana bug. Please would you [open an issue on Github about it](https://github.com/elastic/kibana/issues)?
---
### Author:  [@rdesanno](https://discuss.elastic.co/u/rdesanno)
#### Post date: [October 24, 2019, 6:08pm UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/5 "2019-10-24T18:08:45Z")
THIS IS THE SOLUTION!!
Wow, I was triple-escaping the hostname but never thought to do just the index. Thanks for taking the time to explain this to me! You just made my day!
---
### Author:  [@rdesanno](https://discuss.elastic.co/u/rdesanno)
#### Post date: [October 24, 2019, 6:09pm UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/6 "2019-10-24T18:09:07Z")
Will do
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 21, 2019, 6:09pm UTC](https://discuss.elastic.co/t/elastic-sql-and-curl-not-playing-nice/204971/7 "2019-11-21T18:09:10Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.