Single quotes makes MySQL statement invalid using jdbc_streaming filter

Elastic Stack Logstash
1

Hello,

I am trying to use the jdbc_streaming filter with a regexp statement, like this:

  jdbc_streaming {
    jdbc_driver_library => "/usr/share/logstash/jdbc_drivers/mysql-connector-java-8.0.30.jar"
    jdbc_driver_class => "com.mysql.jdbc.Driver"
    jdbc_connection_string => "jdbc:mysql://zabbix.db.internal.thesocialproxy.com:3306/zabbix?cachePrepStmts=true&prepStmtCacheSize=250&prepStmtCacheSqlLimit=2048&useServerPrepStmts=true"
    jdbc_user => "admin"
    jdbc_password => "Sm3AY9^rEP91&KQnWTK"
    statement => "SELECT itemid FROM items WHERE name regexp '^.*?.*IP.*Address' limit 1"
    use_prepared_statements => true
    prepared_statement_name => "lookup_user_external_IP"
    prepared_statement_bind_values => ["[User]"]
    target => "User_external_IP"
  }

This statement is valid when used directly with MySQL, but when used with this filter, it seems invalid, since the ? is expanded to 'hrcm1k45e7o2xvyl' and not hrcm1k45e7o2xvyl which creates this statement:
"SELECT itemid FROM items WHERE name regexp '^.*'hrcm1k45e7o2xvyl'.*IP.*Address' limit 1"
instead of this valid one:
"SELECT itemid FROM items WHERE name regexp '^.*hrcm1k45e7o2xvyl.*IP.*Address' limit 1"

and this results in this error:

[INFO ][logstash.agent           ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
logstash_1  | [2022-09-09T16:34:02,491][WARN ][logstash.pluginmixins.jdbcstreaming.preparedstatementhandler][main][a9e3b2a18c3816ecaf8eac0eb80a4322fce5bf76db2fe2c9ab6f683d70b69545] Exception when executing JDBC query {:statement=>"SELECT itemid FROM items WHERE name regexp ^.*?.*IP.*Address limit 1", :parameters=>{:p0=>"hrcm1k45e7o2xvyl"}, :exception=>#<Sequel::DatabaseError: Java::JavaSql::SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '^.*'hrcm1k45e7o2xvyl'.*IP.*Address limit 1' at line 1>}

How can resolve this issue?

Thanks.

2

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