[asterisk-bugs] [JIRA] (ASTERISK-22831) Commas cannot be used as part of any string being passed to ODBC as a SQL parameter, due to lack of string field encapsulation

[Sean Bright (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-22831?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Sean Bright closed ASTERISK-22831.
----------------------------------

    Resolution: Not A Bug

I've finally gotten around to lab this up and test. This is the configuration I used:

* Asterisk 13 GIT (1b29d0a7edcfd3841cc04334e6e8ae6ac5a0aa4b)
* unixODBC 2.3.4-1.1ubuntu3
* Oracle ODBC Driver 19.3
* Oracle 18.0.0.0.ru-2019-07.rur-2019-07.r1 (Amazon RDS)

Messages were inserted properly with no error.

> Commas cannot be used as part of any string being passed to ODBC as a SQL parameter, due to lack of string field encapsulation
> ------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: ASTERISK-22831
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-22831
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Applications/app_voicemail/ODBC, Resources/res_odbc
>    Affects Versions: 11.2.2, 13.18.4
>         Environment: Red Hat Enterprise Linux 2.6.32-358.18.1.el6.x86_64
> Asterisk 11.2-cert2
> unixODBC-2.2.14-12.el6_3.x86_64
> unixODBC-devel-2.2.14-12.el6_3.x86_64
> oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64
>            Reporter: Ivan Ullmann
>            Assignee: Sean Bright
>         Attachments: 2344003006.Polycom.Internal.Failure.11072013.txt, 2344003006.Sonus.SBC.External.Success.11072013.txt, Asterisk.DidNotInsert.wav, Asterisk.Inserted.wav, dyn_sip_peers.2344003006.txt, dyn_vm_users.2344003006.txt, extconfig.conf, extensions.conf, func_odbc.conf, modules.conf, res_odbc.conf, sip.conf, unixODBC.zip, voicemail.conf, voicemail_msg.2344003006.txt, VOICEMAIL_MSG.PROD.103013.sql
>
>
> Environment:
> Asterisk 11.2-cert2 platform using ODBC database back end for both realtime configuration and voicemail storage on RHEL 6 64bit
> RDBMS environment is Oracle 11g
> Polycom VVX500 registered to a Genesys SIP Server v8.1.100.94
> Carrier VoIP processed through a Sonus SBC
> Issue:
> When trying to leave a voicemail for a user in an internal to internal scenario (calling another user on the platform rather than sending the call to the carrier), voicemail processes as normal until performing the insert into the database.  When attempting to upload voicemail to database, the following error notifications are observed:
> {noformat}
> [Nov  7 17:11:15] DEBUG[10969][C-00000837]: config.c:1644 config_text_file_load: Parsing /var/spool/asterisk/voicemail/GVMA_DN/2344003006/INBOX/msg0000.txt
>   == Parsing '/var/spool/asterisk/voicemail/GVMA_DN/2344003006/INBOX/msg0000.txt': Found
> [Nov  7 17:11:15] WARNING[10969][C-00000837]: app_voicemail.c:4029 insert_data_cb: SQL Direct Execute failed!
> [Nov  7 17:11:15] WARNING[10969][C-00000837]: res_odbc.c:608 ast_odbc_direct_execute: SQL Execute error! Verifying connection to Oracle11g [OracleODBC-11g]...
> [Nov  7 17:11:15] WARNING[10969][C-00000837]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect...
> [Nov  7 17:11:15] DEBUG[10969][C-00000837]: res_odbc.c:1493 odbc_obj_disconnect: Disconnected 0 from Oracle11g [OracleODBC-11g]
> [Nov  7 17:11:15] DEBUG[10969][C-00000837]: res_odbc.c:1500 odbc_obj_disconnect: Database handle 0x7f6c580e9400 deallocated
> [Nov  7 17:11:15] NOTICE[10969][C-00000837]: res_odbc.c:1527 odbc_obj_connect: Connecting Oracle11g
> [Nov  7 17:11:15] NOTICE[10969][C-00000837]: res_odbc.c:1559 odbc_obj_connect: res_odbc: Connected to Oracle11g [OracleODBC-11g]
> [Nov  7 17:11:16] WARNING[10969][C-00000837]: app_voicemail.c:4029 insert_data_cb: SQL Direct Execute failed!
> [Nov  7 17:11:16] WARNING[10969][C-00000837]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect...
> [Nov  7 17:11:16] DEBUG[10969][C-00000837]: res_odbc.c:1493 odbc_obj_disconnect: Disconnected 0 from Oracle11g [OracleODBC-11g]
> [Nov  7 17:11:16] DEBUG[10969][C-00000837]: res_odbc.c:1500 odbc_obj_disconnect: Database handle 0x7f6c580e9400 deallocated
> [Nov  7 17:11:16] NOTICE[10969][C-00000837]: res_odbc.c:1527 odbc_obj_connect: Connecting Oracle11g
> [Nov  7 17:11:16] NOTICE[10969][C-00000837]: res_odbc.c:1559 odbc_obj_connect: res_odbc: Connected to Oracle11g [OracleODBC-11g]
> [Nov  7 17:11:16] WARNING[10969][C-00000837]: app_voicemail.c:4145 store_file: SQL Execute error!
> [INSERT INTO VOICEMAIL_MSG (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,flag,msg_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)]
> {noformat}
> Additionally, when an external caller is attempting to leave a voicemail, this problem is not observed.  Also, user setting up their own voicemail box has no issues when doing so.  Greetings and password changes are taken immediately.
> Troubleshooting:
> Forced G.711 and ULAW codecs for RTP.  Comparing the working and non-working SDP shows no difference in the messaging.  Voicemails are kept on the system in wav format.
> Researched the issue using Google and could not find root cause or solution.



--
This message was sent by Atlassian JIRA
(v6.2#6252)