[Metricbeat : SQL & Oracle Modules] Connection string for Oracle does not handle special characters properly

[bvader]

Summary

• Metricbeat : SQL and Oracle Modules
• Version: 7.11.x
• Operating System: Multiple Linux

Background:

Valid Characters for Oracle Passwords per
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-USER.html#GUID-F0246961-558F-480B-AC0F-14B50134621C

"Passwords can contain any single-byte, multibyte, or special characters, or any combination of these, from your database character set, with the exception of the double quotation mark (") and the return character. If a password starts with a non-alphabetic character, or contains a character other than an alphanumeric character, the underscore (), dollar sign ($), or pound sign (#), then it must be enclosed in double quotation marks. Otherwise, enclosing a password in double quotation marks is optional. "_

Basically every character except "

Specifically it looks that % and \ are not handled correctly.
So the workaround is to not use these characters, unfortunately many of the passwords are machine generated and often contains % at the very least

This can easily be reproduced using the following SQL module and running the test command, an actual connection to Oracle is not required.

Steps to reproduced

1. Download metricbeat
2. create the sql.yml file below in the modules.d directory under metricbeat home directory
3. run ./metricbeat test modules sql -d "*"
4. You do not need an actual Oracle connection to see the errors, the errors happen while parsing the connection string.
5. Observe the error messages such as
6. Work you way through the non working and working examples

error... ERROR error opening connection: opening connection: oracle://c##elkuser:%!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA: parse "oracle://c##elkuser:%!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA": invalid URL escape "%!@"

sql.yml

# Module: sql
# Docs: https://www.elastic.co/guide/en/beats/metricbeat/7.11/metricbeat-module-sql.html

- module: sql
  metricsets:
    - query
  period: 10s

  # Does not work or parse because of % or \ which are a valid character for password
  hosts: ["oracle://c##elkuser:%!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA"]
  # hosts: ['oracle://c##elkuser:%!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA']
  # hosts: ["oracle://c##elkuser:\!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA"]
  # hosts: ['oracle://c##elkuser:\!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA']
  
  # Works without % or \
  # hosts: ["oracle://c##elkuser:!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA"]
  # hosts: ['oracle://c##elkuser:!@#$^&*()~`<>?[]{}:oracleprd123:1525/PRD_DATA']

  driver: "oracle"
  sql_query: "SHOW GLOBAL STATUS LIKE 'Innodb_system%'"
  sql_response_format: variables

[elasticmachine]

Pinging @elastic/integrations (Team:Integrations)

[sorantis]

cc @sayden

[P1llus]

Want to add this for reference as well: #25058

[andresrc]

This can be a particular case of #30947

[taylor-swanson]

I see this was closed, however, the sql module doesn't appear to have been fixed and I just got an SDH in for this issue: elastic/sdh-beats#2742 (Oracle integration).

[chrisberkhout]

This now works for the sql module. The relevant logic was changed in #32293, the usage is described in https://github.com/elastic/sdh-beats/issues/4233#issuecomment-1877567227.