[asterisk-bugs] [JIRA] (ASTERISK-29838) ${SQL_ESC()} not correctly escaping a terminating \

[Friendly Automation (JIRA)]

    [ https://issues.asterisk.org/jira/browse/ASTERISK-29838?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=258768#comment-258768 ] 

Friendly Automation commented on ASTERISK-29838:
------------------------------------------------

Change 18389 merged by Michael Bradeen:
func_odbc: Add SQL_ESC_BACKSLASHES dialplan function.

[https://gerrit.asterisk.org/c/asterisk/+/18389|https://gerrit.asterisk.org/c/asterisk/+/18389]

> ${SQL_ESC()} not correctly escaping a terminating \
> ---------------------------------------------------
>
>                 Key: ASTERISK-29838
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-29838
>             Project: Asterisk
>          Issue Type: Security
>      Security Level: None
>          Components: Functions/func_odbc
>    Affects Versions: 13.38.2, 16.23.0, 18.9.0
>         Environment: Asterisk with MySQL realtime configuration
>            Reporter: Leandro Dardini
>            Assignee: Joshua C. Colp
>            Severity: Blocker
>              Labels: patch, security
>      Target Release: 16.25.2, 18.11.2, 19.3.2
>
>         Attachments: ASTERISK-29838.diff
>
>
> It is possible to evade the SQL_ESC escape function by terminating the parameter by a \. The SQL_ESC function will not escape the \ and pass it directly to MySQL. If the \ is the latest character of the parameter, it is interpreted by MySQL as the escape char and the ' is not processed as field terminator.
> To replicate, create a simple MySQL table (for example in asterisk database):
> {code:title=testtable.sql}
> CREATE TABLE IF NOT EXISTS `testtable` (
>   `ID` int(11) NOT NULL AUTO_INCREMENT,
>   `textfield` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
>   PRIMARY KEY (`ID`)
> )
> {code}
> And the corresponding entry for writing in it in func_odbc.conf:
> {noformat:title=func_odb.conf}
> [QUERY_BUG]
> dsn=asterisk1,asterisk2
> synopsis=Test a query bug
> writesql=insert into testtable(textfield) values ('${SQL_ESC(${ARG1})}')
> {noformat}
> An AEL code to trigger the problem can be written in extensions.ael as:
> {noformat:title=extensions.ael}
>         9999 => {
>              NoOp(This is a test);
>              Set(ODBC_QUERY_BUG("This is an escape ' test")="filler");
>              Set(ODBC_QUERY_BUG("This triggers the bug\\")="filler");
>              Hangup();
>         }
> {noformat}
> The first query will be processed correctly, escaping the ' as it should work, but the second one will trigger a SQL error. 
> {noformat:title=asterisk full log}
>     -- Executing [9999 at authenticated:1] NoOp("PJSIP/107-DEVEL-00000000", "This is a test") in new stack
>     -- Executing [9999 at authenticated:2] Set("PJSIP/107-DEVEL-00000000", "ODBC_QUERY_BUG("This is an escape ' test")="filler"") in new stack
> [2022-01-05 21:26:40] WARNING[21524][C-00000000]: pbx_variables.c:1147 pbx_builtin_setvar: Please avoid unnecessary spaces on variables as it may lead to unexpected results ('ODBC_QUERY_BUG("This is an escape ' test")' set to '"filler"').
>     -- Executing [9999 at authenticated:3] Set("PJSIP/107-DEVEL-00000000", "ODBC_QUERY_BUG("This triggers the bug\\")="filler"") in new stack
> [2022-01-05 21:26:40] WARNING[21524][C-00000000]: pbx_variables.c:1147 pbx_builtin_setvar: Please avoid unnecessary spaces on variables as it may lead to unexpected results ('ODBC_QUERY_BUG("This triggers the bug\\")' set to '"filler"').
> [2022-01-05 21:26:40] WARNING[21524][C-00000000]: func_odbc.c:478 execute: SQL Execute returned an error -1: 42000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.28-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''This triggers the bug\')' at line 1 (217)
> [2022-01-05 21:26:40] WARNING[21524][C-00000000]: func_odbc.c:487 execute: SQL Exec Direct failed (-1)![insert into testtable(textfield) values ('This triggers the bug\')]
> [2022-01-05 21:26:40] WARNING[21524][C-00000000]: func_odbc.c:478 execute: SQL Execute returned an error -1: 42000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.7.28-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''This triggers the bug\')' at line 1 (217)
> [2022-01-05 21:26:40] WARNING[21524][C-00000000]: func_odbc.c:487 execute: SQL Exec Direct failed (-1)![insert into testtable(textfield) values ('This triggers the bug\')]
>     -- Executing [9999 at authenticated:4] Hangup("PJSIP/107-DEVEL-00000000", "") in new stack
> {noformat}
> I don't know if it can be exploited leading MySQL to run different commands than expected.



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