Summary:ASTERISK-15788: [patch] func_odbc query is limited to 15 characters
Reporter:viniciusfontes (viniciusfontes)Labels:
Date Opened:2010-03-11 08:58:59.000-0600Date Closed:2010-03-11 13:23:21.000-0600
Versions:Frequency of
Environment:Attachments:( 0) 20100311__issue17006.diff.txt
( 1) func_odbc.c.patch
Description:You can't have a query string longer than 15 characters passed to ODBC_[NAME] functions. The query string is trimmed to the first 15 characters.

There's a patch to solve this included.


That happens because the ast_str_create() function is called with the parameter 16. Passing 4096 to allow a 4KB string solved the issue. Patch included.
Comments:By: Leif Madsen (lmadsen) 2010-03-11 09:15:54.000-0600

Thanks for the submission!

By: Tilghman Lesher (tilghman) 2010-03-11 10:34:49.000-0600

The ast_str API is a dynamic string API.  16 is merely the initial size, but the string will grow to meet the needs of the particular invocation.  Clearly, you did not TEST the functionality, or you would have realized that there is no 15 character limit.

Please, in the future, before you make assumptions about code, TEST the functionality first.

By: viniciusfontes (viniciusfontes) 2010-03-11 11:39:03.000-0600

That's anything but right. I made the patch because the feature wasn't working as intended.

Here's my setup and the CLI output:

exten => 2,1,Set(TEMP=${ODBC_PGSQL("SELECT id,billed,uniqueid,userfield FROM cdr WHERE id = 2")})
exten => 2,n,NoOp(TEMP = ${TEMP})
exten => 2,n,Hangup()


ch-corretora*CLI> console dial 2@teste
[Mar 11 14:34:21] WARNING[4466]: chan_oss.c:492 setformat: Unable to re-open DSP device /dev/dsp: No such file or directory
[Mar 11 14:34:21] NOTICE[4466]: console_video.c:133 console_video_start: voice only, console video support not present
[Mar 11 14:34:21] ERROR[4468]: func_odbc.c:343 acf_odbc_read: Unable to execute query [SELECT id,bille]
   -- Executing [2@teste:1] Set("Console/dsp", "TEMP=") in new stack
   -- Executing [2@teste:2] NoOp("Console/dsp", "TEMP = ") in new stack
   -- Executing [2@teste:3] Hangup("Console/dsp", "") in new stack
 == Spawn extension (teste, 2, 3) exited non-zero on 'Console/dsp'
<< Hangup on console >>

As you can see, the value passed to the ODBC engine is precisely 15 characters. If the "real" bug is somewhere else I really don't know. What I know is I found a bug I didn't created but fixed it anyway.

By: Tilghman Lesher (tilghman) 2010-03-11 13:16:30.000-0600

Okay, confirmed.  The actual problem is that we did not check to see if the buffer was full, and most people do not use func_odbc in this way, preferring to use a real template.  This is why it was not caught before.  Patch uploaded which fixes the issue in a way that does not recreate a static buffer.  Larger static buffers could still potentially have the same problem, which is why your patch wasn't a real fix.

By: Digium Subversion (svnbot) 2010-03-11 13:23:20.000-0600

Repository: asterisk
Revision: 251875

U   branches/1.6.1/funcs/func_odbc.c

r251875 | tilghman | 2010-03-11 13:21:19 -0600 (Thu, 11 Mar 2010) | 14 lines

Verify whether the created buffer was actually large enough to hold the expanded value.

For certain types of queries, where the size of the substituted query was much
larger than the template, it was possible for the substitution buffer to be too
small.  This is only an issue in 1.6.1, as previously we used a static buffer
anyway, and we have a substitution routine in 1.6.2 forward that automatically
sizes itself appropriately to handle larger expansions.

(closes issue ASTERISK-15788)
Reported by: viniciusfontes
      20100311__issue17006.diff.txt uploaded by tilghman (license 14)
Tested by: tilghman



By: Leif Madsen (lmadsen) 2014-12-10 11:51:25.821-0600

Just to add some further commentary here. We just recently ran into something that appeared like this issue, and it was incredibly bizarre. The issue ended up being when using func_odbc against a MySQL {{longtext}} field, the func_odbc implementation won't know exactly how to handle that, so it defaults to the 15 character limit, and only returns the first 15 chars.

The work around for this is to 1) not use {{longtext}} fields, or 2) to {{CAST}} the variable into a {{varchar}}.

For example:

readsql=SELECT CAST(my_awesome_longtext_column AS char(255)) FROM table_name WHERE something = '${SQL_ESC(${ARG1})}'