Issues with using ODBC to query json response from MySQL

[dovi5988]

I am using func_odbc to query a table and get values out of a JSON field.

My Dialplan:

Set(RESULT=${ODBC_CHARIDY_GET_TEAM_RAISED_BY_ID(23277,749616,eur)})

If I have in func_odbc.conf

[CHARIDY_GET_TEAM_RAISED_BY_ID]
writehandle=charidy
synopsis=Get amount raised by actual team ID
readsql=select campaign_id FROM teams where campaign_id = '${SQL_ESC(${ARG1})}' and team_id = '${SQL_ESC(${ARG2})}';

Then everything is fine. However if I change that to

[CHARIDY_GET_TEAM_RAISED_BY_ID]
writehandle=charidy
synopsis=Get amount raised by actual team ID
readsql=select campaign_id, JSON_EXTRACT(team_data, '$.goal') AS goal FROM teams where campaign_id = '${SQL_ESC(${ARG1})}' and team_id = '${SQL_ESC(${ARG2})}';

I see in the CLI

[2023-12-22 10:02:06] WARNING[78835][C-00000008]: func_odbc.c:1009 acf_odbc_read: SQL Get Data error!
[select campaign_id, JSON_EXTRACT(team_data, '$.goal') AS goal FROM teams where campaign_id = '23277' and team_id = '749616']

The query is a perfectly valid query. Running ngrep on port 3306 shows the qeury going and response from MySQL. Also running the query directly in MYSQL works as well. My hunch is the $ is causing it but I can’t explain why that would be. Could this be a bug?

[penguinpbx]

You might want to turn up logging to include DEBUG messages. On the Asterisk CLI, try “core set debug 10”.

@dovi5988

Could this be a bug?

Maybe, but there are workarounds, such as modifying your SQL to wrap a VIEW around the TABLE, then in your func_odbc.conf you would SELECT from the VIEW instead of the TABLE.

[PitzKey]

What happens when you escape it?

[dovi5988]

I opened a bug report on GitHub ([bug]: func_odbc: Large octet length results in crash · Issue #510 · asterisk/asterisk · GitHub) as some in some scenarios it was causing Asterisk to crash. @jcolp mentioned that he saw a lot of memory being used. A simple way to fix the issue was to create a stored procedure where the max value for the fields were defined which fixed the problem.