[Home]

Summary: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
Reporter:Ivan Ullmann (iullmann)Labels:
Date Opened:2013-11-08 09:27:51.000-0600Date Closed:2019-10-01 10:11:10
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Applications/app_voicemail/ODBC Resources/res_odbc
Versions:11.2.2 13.18.4 Frequency of
Occurrence		Constant
Related
Issues:
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_64Attachments:( 0) 2344003006.Polycom.Internal.Failure.11072013.txt
( 1) 2344003006.Sonus.SBC.External.Success.11072013.txt
( 2) Asterisk.DidNotInsert.wav
( 3) Asterisk.Inserted.wav
( 4) dyn_sip_peers.2344003006.txt
( 5) dyn_vm_users.2344003006.txt
( 6) extconfig.conf
( 7) extensions.conf
( 8) func_odbc.conf
( 9) modules.conf
(10) res_odbc.conf
(11) sip.conf
(12) unixODBC.zip
(13) voicemail_msg.2344003006.txt
(14) VOICEMAIL_MSG.PROD.103013.sql
(15) voicemail.conf
Description: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.
Comments:By: Ivan Ullmann (iullmann) 2013-11-08 09:36:49.582-0600

ZIP contains the following:

Relevant .conf files
SQL Table construction
Output of failed calls and functioning calls
Output of database for all relevant tables
By: Ivan Ullmann (iullmann) 2013-11-20 09:02:07.000-0600

Is additional detail required?

-Ivan
By: Rusty Newton (rnewton) 2013-11-21 19:00:27.932-0600

Can you provide output from the MySQL query log for both the working and not working scenarios?

Can you also grab the msgXXXX.txt file for both calls?

Would you also be able to temporarily swap out the latest Asterisk 11 SVN revision to test in place of your cert version?
By: Ivan Ullmann (iullmann) 2013-11-22 09:40:08.663-0600

Rusty,

Thank you for your response.  

1.  I have uploaded my unixODBC.log file (I'm not using mySQL).  I do not see any log output being updated for non-working scenarios.

2.  The msgXXXX.txt file is removed from the filesystem as soon as the process for ODBC Storage is instantiated.  I do not have the files for comparison.

3.  Looks like 11.6 is the latest code rev.  I'll recompile and attempt to process the same call flow and report my findings as soon as I am able.

Thank you for your assistance,
-Ivan
By: Rusty Newton (rnewton) 2013-11-22 09:58:33.964-0600

Thanks. For #2, I was hoping you could grab a copy of them when they are created for that moment. Another silly question. For the internal to internal tests, have you tested with more than the one particular extension/mailbox on the receiving side?
By: Ivan Ullmann (iullmann) 2013-11-22 10:07:08.714-0600

Rusty,

The process happens pretty quick.  It'd be hard to capture in the moment.

For internal to internal, I have processed this with more than one extension.  Some of them work, and some of them don't.  The mailboxes themselves seem to operate properly when they're externally called in all cases.  Users can change their voicemail messages and passwords without issue and all of that information is passed via ODBC to the database.  Internally, comparing the SIP invites and RTP streams, I can't see anything that's out of the ordinary.  Aside from the identification of the call (branch tags, calluuids, etc.) they're identical.

Thank you,
-Ivan
By: Ivan Ullmann (iullmann) 2013-11-25 18:08:41.203-0600

Rusty,

3.  I upgraded to 11.6 and the problem persists.

Additional information:  I am using a Polycom VVX500 phone to instantiate the voicemail call.  However, when I use a soft phone such a phonerlite, the issuer does not persist.

Thank you,
-Ivan
By: Ivan Ullmann (iullmann) 2013-11-26 10:20:07.792-0600

Rusty,

2.  I have provided examples of working output as well as nonworking output as recorded in the filesystem prior to uploading to the database.

Thank you,
-Ivan
By: Ivan Ullmann (iullmann) 2013-11-26 15:00:09.489-0600

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
By: Sean Bright (seanbright) 2017-12-18 11:35:32.829-0600

Asterisk is using {{SQLBindParameter}} which should take care of all the necessary escaping. The {{?}} syntax (without the single quotes wrapped around it) is normal and valid. The driver itself should just be doing the right thing. I will try to lab this up with Asterisk 13 and test.
By: Walter Doekes (wdoekes) 2017-12-19 09:33:10.519-0600

{quote}
RDBMS environment is Oracle 11g
{quote}
I would guess that this is some kind of Oracle/unixodbc-driver bug. If this didn't work on mysql, cdr_odbc would have failed us long ago.
By: Sean Bright (seanbright) 2019-10-01 10:11:10.230-0500

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.