[asterisk-bugs] [JIRA] (ASTERISK-22831) Asterisk Voicemail via ODBC on Oracle fails to properly insert under specific conditions
Ivan Ullmann (JIRA)
noreply at issues.asterisk.org
Tue Nov 26 15:02:03 CST 2013
[ https://issues.asterisk.org/jira/browse/ASTERISK-22831?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=212284#comment-212284 ]
Ivan Ullmann commented on ASTERISK-22831:
-----------------------------------------
Rusty,
I have root cause resolution for the defect. I altered the app_voicemail.c code to provide me with additional diagnostic information, like so:
[Nov 26 15:10:19] WARNING[14045][C-00000001]: app_voicemail.c:4034 insert_data_cb: SQL Parameter: callerid - '"8008008000 - Ullmann, Ivan" <8008008000>'
However, Asterisk when processing this particular field does not do so by encapsulating the value in single quotes. Therefore, any comma in any of the ODBC strings would parse by SQL command as a separate field. This is why the application was failing. Instead of receiving 12 fields as expected, it would receive 13. This would cause a SQL error and the data would fail to upload to the database.
[Nov 26 15:10:19] WARNING[14045][C-00000001]: app_voicemail.c:4193 store_file: SQL Execute error!
[INSERT INTO VOICEMAIL_MSG (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,flag,msg_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
If the string looked more like this:
[Nov 26 15:10:19] WARNING[14045][C-00000001]: app_voicemail.c:4193 store_file: SQL Execute error!
[INSERT INTO VOICEMAIL_MSG (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,flag,msg_id) VALUES ('?','?','?','?','?','?','?','?','?','?','?','?')
There would have been no errors experienced by the application and we would have been none the wiser that this vulnerability existed. Once I changed my string to this:
[Nov 26 15:16:13] WARNING[14257][C-00000003]: app_voicemail.c:4034 insert_data_cb: SQL Parameter: callerid - '"8008008000 - Ivan Ullmann" <8008008000>'
There were no further issues experienced. I will leave it up to wiser heads than mine to decide whether this issue warrants a code update.
Summary: Commas cannot be used as part of any string being passed to ODBC as a SQL parameter, because the application does not encapsulate those string fields with single quotes, thereby allowing the SQL statement to be interpreted as literal.
Thank you,
-Ivan
> Asterisk Voicemail via ODBC on Oracle fails to properly insert under specific conditions
> ----------------------------------------------------------------------------------------
>
> 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
> 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: Ivan Ullmann
> 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 is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.asterisk.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the asterisk-bugs
mailing list