Sql escape character in logstash

Elastic Stack Logstash
1

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 .

2

Welcome to the community!

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

3

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

1 Like
4

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