Escaping double quotes with ODBC functions

Asterisk Asterisk Dialplan
1

Before reporting as a bug, I’d like to check with you if I am doing something wrong. When using an ODBC function, if any of the fields contains a double quote, the field ordering and value results are wrong. I have tried with the use of the ARRAY and HASH function

If I use a simple function like:

[QUERY_BUG]
dsn=asterisk1,asterisk2
synopsis=Get a double quote
readsql=SELECT "George \“o Neal” as name,987897 as number from DUAL

With a simple dialplan

    9998 => {
         NoOp(This is a test);
         Set(ARRAY(name,number)=${ODBC_QUERY_BUG()});
         NoOp(The name value is ${name} and the number value is ${number});
         Set(HASH(varhash)=${ODBC_QUERY_BUG()});
         NoOp(The varhash name value is ${HASH(varhash,name)} and the varhash number value is ${HASH(varhash,number)});
         Hangup();
    }

The result is not what is expected:

-- Executing [9998@authenticated:1] NoOp("PJSIP/104-DEVEL-000000cc", "This is a test") in new stack
-- Executing [9998@authenticated:2] Set("PJSIP/104-DEVEL-000000cc", "ARRAY(name,number)=George "o Neal,987897") in new stack
-- Executing [9998@authenticated:3] NoOp("PJSIP/104-DEVEL-000000cc", "The name value is George o Neal,987897 and the number value is ") in new stack
-- Executing [9998@authenticated:4] Set("PJSIP/104-DEVEL-000000cc", "HASH(varhash)=George "o Neal,987897") in new stack
-- Executing [9998@authenticated:5] NoOp("PJSIP/104-DEVEL-000000cc", "The varhash name value is George o Neal,987897 and the varhash number value is ") in new stack

Is there a way to get around this problem?

2

Other SQL implementations may differ.

3

Thank you for the reply, I was not aware of that page. However, that doesn’t completely solve the issue because when the double quote is in the MySQL table data, escaping it while reading is a very dirty solution. I suppose the escaping should be done in the ODBC driver, not by the user in this way:

[QUERY_BUG]
dsn=asterisk1,asterisk2
synopsis=Get a double quote
readsql=SELECT REPLACE(‘George "o Neal’,‘"’,‘“”’) as name,987897 as number from DUAL

4

You need to filter the string, if you don’t want to fall victim to an SQL injection attack.

The way that SQL handles this properly is that you put question marks as placeholders for the values, and then provide a binary data structure containing the actual values, in the same order, meaning that the SQL parser never sees the values. However quick and dirty support for SQL just works with the values directly in the SQL.

5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.