Jdbc_static prepared_parameters Mismatched number of placeholders error

[jgabor (Jan)]

Hi!

When using the jdbc_static filter and using prepared_parameters in lookup I get a "Mismatched number of placeholders" error.

The configuration looks like this:

    local_lookups => [
      {
        id => "local-meld"
        query => "SELECT nr
                        ,meldung 
                    FROM meld 
                   WHERE ts_einfuegung = ?
                     AND system_nr = ?"
        prepared_parameters => [ "[ts_einfuegung]", "[system_nr]" ]
        target => "meldungen"
      }
    ]

This is the error message:

[main] Pipeline aborted due to error {:pipeline_id=>"main", :exception=>#<Sequel::Error: Mismatched number of placeholders (2) and placeholder arguments (1) when using placeholder string>

The Logstash Version I use is the Docker container logstash:7.6.1

What am I doing wrong here or is this a bug?

Thanks,
Jan

[Badger]

The jdbc_static filter verifies that the number of prepared_parameters matches the number of question marks in the query.

I would have expected the sequel library to see prepared_parameters as an array, but the error message has "when using placeholder string", so it went through this code path.

The filter does a sprintf or get on each prepared parameter. You do not have %{} around your parameters so it will be doing a get.

My guess is that the get fails, because the event is missing either a ts_einfuegung or system_nr field. If that is the case I would say it is a bug, but I am unsure whether it is a bug in the filter or a bug in the underlying library.

[jgabor (Jan)]

I did some additional testing:
The fields ts_einfuegung and system_nr must be present as the sql query in the source looks like this

SELECT 
 *
  FROM LOG
WHERE TS_EINFUEGUNG IS NOT NULL
  AND SYSTEM_NR IS NOT NULL

But: when using parameters instead of prepared_parameters it works:

local_lookups => [
      {
        id => "local-meld"
        query => "SELECT nr
                        ,meldung 
                    FROM meld 
                   WHERE ts_einfuegung = :ts_einfuegung
                     AND system_nr = :system_nr"
        parameters =>{ ts_einfuegung => "[ts_einfuegung]"  system_nr => "[system_nr]" }
        target => "meldungen"
      }
    ]

Well, this is a feasable workaround for my problem but I really would like to know if prepared_statements make a difference in performance

Jan