# Sql escape character in logstash

**URL:** <https://discuss.elastic.co/t/sql-escape-character-in-logstash/351800>\
**Category:** Logstash\
**Created:** [January 25, 2024, 12:44pm UTC](https://discuss.elastic.co/t/sql-escape-character-in-logstash/351800 "2024-01-25T12:44:39Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Rakesh\_Verma](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rakesh_verma/32/122245_2.png) [@Rakesh\_Verma](https://discuss.elastic.co/u/Rakesh_Verma)\
**Post date:** [January 25, 2024, 12:44pm UTC](https://discuss.elastic.co/t/sql-escape-character-in-logstash/351800/1 "2024-01-25T12:44:39Z")

</div>

I am getting below error in following sql .

SELECT HA.HotelID AS HotelCode,'[' + STUFF((SELECT ',' + '{"AmenityId": ' + CAST(HA\_inner.AmenityId AS VARCHAR(10)) + ', "AmenityName": ' + QUOTENAME(HAT\_inner.AmenityName, '"') + ', "Rank": null, "Group": null}' FROM HotelAmenity HA\_inner INNER JOIN HotelAmenityType HAT\_inner ON HAT\_inner.AmenityId = HA\_inner.AmenityId WHERE HA\_inner.HotelID = HA.HotelID ORDER BY HA\_inner.AmenityId FOR XML PATH('')), 1, 1, '') + ']' AS Amenities FROM HotelAmenity HA INNER JOIN HotelAmenityType HAT ON HAT.AmenityId = HA.AmenityId WHERE HA.HotelID = 1000053 GROUP BY HA.HotelID

error:

[ERROR] 2024-01-25 12:35:53.063 [Converge PipelineAction::Create] agent - Failed to execute action {:action=\>LogStash::PipelineAction::Create/pipeline\_id:main, :exception=\>"LogStash::ConfigurationError", :message=\>"Expected one of [\t\r\n], "#", "{", "}" at line 10, column 79 (byte 538) after input {\n jdbc {\n jdbc\_driver\_library =\> "/home/abhishek/sqljdbc\_12.4/enu/jars/mssql-jdbc-12.4.1.jre11.jar" \n jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"\n jdbc\_connection\_string =\> "jdbc:sqlserver://192.168.201.59:1433;databaseName=UAPIStaticData;encrypt=true;trustServerCertificate=true"\n jdbc\_user =\> "uapiservice" \n jdbc\_password =\> "HotelServices456" \n tracking\_column =\> "HotelCode"\n use\_column\_value =\> true\n statement =\> "SELECT HA.HotelID AS HotelCode,'[' + STUFF((SELECT ',' + '{"", :backtrace=\>["/home/abhishek/logstash/logstash-core/lib/logstash/compiler.rb:32:in `compile_imperative'", "org/logstash/execution/AbstractPipelineExt.java:239:in `initialize'", "org/logstash/execution/AbstractPipelineExt.java:173:in `initialize'", "/home/abhishek/logstash/logstash-core/lib/logstash/java_pipeline.rb:48:in `initialize'", "org/jruby/RubyClass.java:931:in `new'", "/home/abhishek/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:49:in `execute'", "/home/abhishek/logstash/logstash-core/lib/logstash/agent.rb:386:in `block in converge\_state'"]}

I have tried escaping double quote with backslash how ever this did not help .

---

<div class="post-metadata">

**Author:** ![Rios](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rios/32/95745_2.png) [@Rios](https://discuss.elastic.co/u/Rios)\
**Post date:** [January 25, 2024, 12:47pm UTC](https://discuss.elastic.co/t/sql-escape-character-in-logstash/351800/2 "2024-01-25T12:47:40Z")

</div>

Welcome to the community!

Your .conf syntax is not OK, most likely } or quotes are missing somewhere

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [January 25, 2024, 5:51pm UTC](https://discuss.elastic.co/t/sql-escape-character-in-logstash/351800/3 "2024-01-25T17:51:31Z")

</div>

> [@Rakesh\_Verma](#):
>
> "LogStash::ConfigurationError", :message=\>"Expected one of [\t\r\n], "#", "{", "}" at line 10, column 79 (byte 538) after input {\n jdbc ...statement =\> "SELECT HA.HotelID AS HotelCode,'[' + STUFF((SELECT ',' + '{"", :

It is complaining about whatever comes after `SELECT ',' + '{"`, which is AmenityId. Since the double quote ends the value of the statement option it expect another option on the jdbc filter to come next (or }). You need to escape all of the double quotes with backslash.

```
 statement => "SELECT HA.HotelID AS HotelCode,'[' + STUFF((SELECT ',' + '{\"AmenityId\": ' + CAST(HA_inner.AmenityId AS VARCHAR(10)) + ', \"AmenityName\": ' + QUOTENAME(HAT_inner.AmenityName, '\"') + ', \"Rank\": null, \"Group\": null}' FROM HotelAmenity HA_inner INNER JOIN HotelAmenityType HAT_inner ON HAT_inner.AmenityId = HA_inner.AmenityId WHERE HA_inner.HotelID = HA.HotelID ORDER BY HA_inner.AmenityId FOR XML PATH('')), 1, 1, '') + ']' AS Amenities FROM HotelAmenity HA INNER JOIN HotelAmenityType HAT ON HAT.AmenityId = HA.AmenityId WHERE HA.HotelID = 1000053 GROUP BY HA.HotelID"

```

does not get that error

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 22, 2024, 5:52pm UTC](https://discuss.elastic.co/t/sql-escape-character-in-logstash/351800/4 "2024-02-22T17:52:05Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.