How to insert correctlly value in Mysql database with Asterisk without dividing in coloumns because of comma caracter?

easydoor · June 30, 2018, 6:36am

Hello…
I have a very strange problem, at least for me…
Im using Asterisk 13.20.
I will present two example. Inserting the txt in mysql database over odbc connector with asterisk.
In first, ifI do not use the comma caracter then inserting is normal and the txt stay in same coloumn. In both case dialplan is same only in one line is different:

[test-mysql-input]
exten => 8888,1,Verbose(Lets set the MESSAGE) with some message);
;same => n,Set(MESSAGE=Hello,this example, will divide, totally, the message, because of comma, caracter.);
same => n,Set(MESSAGE=Hello this example and it wont divide the message couse I didnt use comma caracter.);

same => n,Set(NOW=${STRFTIME(${EPOCH},,%d.%m.%Y %H:%M:%S)})
same => n,Set(CDR(TYPE_OF_CALL)=OUTGOING);
same => n,Set(CDR(start_)=${CDR(start)})
same => n,Set(CALLERID(num)=12345)
same => n,Set(MESSAGE_SEND_STATUS=SENT)
same => n,Set(USER_1=Me)
same => n,Set(DONGLE_NAME=Dongle_0)
same => n,Set(CDR(phone_ip)=192.168.1.2)
same => n,Set(DONGLE_MANUF_MODEL=blabla)
same => n,Set(DONGLE_CARRIER=again bla bla)
same => n,Set(CDR(useragent_)=CSipSimple_sagit-26/r2457)
same => n(ok),Set(ODBC_D1_SMSHISTORY()=${CDR(TYPE_OF_CALL)},${CDR(start_)},${MESSAGE},${MESSAGE_SEND_STATUS},${USER_1},${DONGLE_NAME},${CALLERID(num)},${CDR(phone_ip)},${DONGLE_MANUF_MODEL},${DONGLE_CARRIER},${CDR(useragent_)})


same => n,Hangup();

The result looks like this from CLI:

orangepipc*CLI> console dial 8888@test-mysql-input
  == Console is full duplex
[Jun 29 14:22:25] NOTICE[17328]: console_video.c:137 console_video_start: voice only, console video support not present
    -- Executing [8888@test-mysql-input:1] Verbose("Console/dsp", "Lets set the MESSAGE) with some message") in new stack
Lets set the MESSAGE) with some message
    -- Executing [8888@test-mysql-input:2] Set("Console/dsp", "MESSAGE=Hello this example and it wont divide the message couse I didnt use comma caracter.") in new stack
    -- Executing [8888@test-mysql-input:3] Set("Console/dsp", "NOW=29.06.2018 14:22:25") in new stack
    -- Executing [8888@test-mysql-input:4] Set("Console/dsp", "CDR(TYPE_OF_CALL)=OUTGOING") in new stack
    -- Executing [8888@test-mysql-input:5] Set("Console/dsp", "CDR(start_)=2018-06-29 14:22:25") in new stack
    -- Executing [8888@test-mysql-input:6] Set("Console/dsp", "CALLERID(num)=12345") in new stack
    -- Executing [8888@test-mysql-input:7] Set("Console/dsp", "MESSAGE_SEND_STATUS=SENT") in new stack
    -- Executing [8888@test-mysql-input:8] Set("Console/dsp", "USER_1=Me") in new stack
    -- Executing [8888@test-mysql-input:9] Set("Console/dsp", "DONGLE_NAME=Dongle_0") in new stack
    -- Executing [8888@test-mysql-input:10] Set("Console/dsp", "CDR(phone_ip)=192.168.1.2") in new stack
    -- Executing [8888@test-mysql-input:11] Set("Console/dsp", "DONGLE_MANUF_MODEL=blabla") in new stack
    -- Executing [8888@test-mysql-input:12] Set("Console/dsp", "DONGLE_CARRIER=again bla bla") in new stack
    -- Executing [8888@test-mysql-input:13] Set("Console/dsp", "CDR(useragent_)=CSipSimple_sagit-26/r2457") in new stack
    -- Executing [8888@test-mysql-input:14] Set("Console/dsp", "ODBC_D1_SMSHISTORY()=OUTGOING,2018-06-29 14:22:25,Hello this example and it wont divide the message couse I didnt use comma caracter.,SENT,Me,Dongle_0,12345,192.168.1.2,blabla,again bla bla,CSipSimple_sagit-26/r2457") in new stack
    -- Executing [8888@test-mysql-input:15] Hangup("Console/dsp", "") in new stack
  == Spawn extension (test-mysql-input, 8888, 15) exited non-zero on 'Console/dsp'
  << Hangup on console >> 
orangepipc*CLI>

and the database:

In second example if I use the comma caracter text is entering in different coloumn:

[test-mysql-input]
exten => 8888,1,Verbose(Lets set the MESSAGE) with some message);
same => n,Set(MESSAGE=Hello,this example, will divide, totally, the message, because of comma, caracter.);
;same => n,Set(MESSAGE=Hello this example and it wont divide the message couse I didnt use comma caracter.);

same => n,Set(NOW=${STRFTIME(${EPOCH},,%d.%m.%Y %H:%M:%S)})
same => n,Set(CDR(TYPE_OF_CALL)=OUTGOING);
same => n,Set(CDR(start_)=${CDR(start)})
same => n,Set(CALLERID(num)=12345)
same => n,Set(MESSAGE_SEND_STATUS=SENT)
same => n,Set(USER_1=Me)
same => n,Set(DONGLE_NAME=Dongle_0)
same => n,Set(CDR(phone_ip)=192.168.1.2)
same => n,Set(DONGLE_MANUF_MODEL=blabla)
same => n,Set(DONGLE_CARRIER=again bla bla)
same => n,Set(CDR(useragent_)=CSipSimple_sagit-26/r2457)
same => n(ok),Set(ODBC_D1_SMSHISTORY()=${CDR(TYPE_OF_CALL)},${CDR(start_)},${MESSAGE},${MESSAGE_SEND_STATUS},${USER_1},${DONGLE_NAME},${CALLERID(num)},${CDR(phone_ip)},${DONGLE_MANUF_MODEL},${DONGLE_CARRIER},${CDR(useragent_)})


same => n,Hangup();

and the output in CLI is:

orangepipc*CLI> console dial 8888@test-mysql-input
  == Console is full duplex
[Jun 30 08:19:38] NOTICE[9606]: console_video.c:137 console_video_start: voice only, console video support not present
    -- Executing [8888@test-mysql-input:1] Verbose("Console/dsp", "Lets set the MESSAGE) with some message") in new stack
Lets set the MESSAGE) with some message
    -- Executing [8888@test-mysql-input:2] Set("Console/dsp", "MESSAGE=Hello,this example, will divide, totally, the message, because of comma, caracter.") in new stack
    -- Executing [8888@test-mysql-input:3] Set("Console/dsp", "NOW=30.06.2018 08:19:38") in new stack
    -- Executing [8888@test-mysql-input:4] Set("Console/dsp", "CDR(TYPE_OF_CALL)=OUTGOING") in new stack
    -- Executing [8888@test-mysql-input:5] Set("Console/dsp", "CDR(start_)=2018-06-30 08:19:38") in new stack
    -- Executing [8888@test-mysql-input:6] Set("Console/dsp", "CALLERID(num)=12345") in new stack
    -- Executing [8888@test-mysql-input:7] Set("Console/dsp", "MESSAGE_SEND_STATUS=SENT") in new stack
    -- Executing [8888@test-mysql-input:8] Set("Console/dsp", "USER_1=Me") in new stack
    -- Executing [8888@test-mysql-input:9] Set("Console/dsp", "DONGLE_NAME=Dongle_0") in new stack
    -- Executing [8888@test-mysql-input:10] Set("Console/dsp", "CDR(phone_ip)=192.168.1.2") in new stack
    -- Executing [8888@test-mysql-input:11] Set("Console/dsp", "DONGLE_MANUF_MODEL=blabla") in new stack
    -- Executing [8888@test-mysql-input:12] Set("Console/dsp", "DONGLE_CARRIER=again bla bla") in new stack
    -- Executing [8888@test-mysql-input:13] Set("Console/dsp", "CDR(useragent_)=CSipSimple_sagit-26/r2457") in new stack
    -- Executing [8888@test-mysql-input:14] Set("Console/dsp", "ODBC_D1_SMSHISTORY()=OUTGOING,2018-06-30 08:19:38,Hello,this example, will divide, totally, the message, because of comma, caracter.,SENT,Me,Dongle_0,12345,192.168.1.2,blabla,again bla bla,CSipSimple_sagit-26/r2457") in new stack
    -- Executing [8888@test-mysql-input:15] Hangup("Console/dsp", "") in new stack
  == Spawn extension (test-mysql-input, 8888, 15) exited non-zero on 'Console/dsp'
  << Hangup on console >> 
orangepipc*CLI>

and in database looks like:

In both case Im using in func_odbc.conf:

....
....
[D1_SMSHISTORY]
prefix=ODBC
dsn=asterisk
writesql=INSERT INTO D1_SMS_HISTORY (sms_type,time_date,message,msg_send_stat,username,dongle_name,sms_from,phone_ip,dongle_model,provider,user_agent) VALUES('${VAL1}','${VAL2}','${VAL3}','${VAL4}','${VAL5}','${VAL6}','${VAL7}','${VAL8}','${VAL9}','${VAL10}','${VAL11}')
...
...

and in odbc.ini:

[asterisk-connector]
Description = MySQL connection to 'asterisk' database
Driver = MySQL
Database = asterisk
Server = 192.168.1.224
Port = 3306
Socket = /var/lib/mysql/mysql.sock

Why this happening? Im try to souround with quote, with {}, but result is same.
Can someone suggest me how?

przeqpiciel · June 30, 2018, 1:16pm

please make changes in your func_odbc file and each call of ${VALX} put into additional ${SQL_ESC()}

${SQL_ESC(${VAL1})}

easydoor · June 30, 2018, 4:14pm

Thank you for helping me…but It didn’t help.
I change my func_odbc.conf file in this way as you suggest me:

[D1_SMSHISTORY]
prefix=ODBC
dsn=asterisk
writesql=INSERT INTO D1_SMS_HISTORY (sms_type,time_date,message,msg_send_stat,username,dongle_name,sms_from,phone_ip,dongle_model,provider,user_agent) VALUES('${SQL_ESC(${VAL1})}','${SQL_ESC(${VAL2})}','${SQL_ESC(${VAL3})}','${SQL_ESC(${VAL4})}','${SQL_ESC(${VAL5})}','${SQL_ESC(${VAL6})}','${SQL_ESC(${VAL7})}','${SQL_ESC(${VAL8})}','${SQL_ESC(${VAL9})}','${SQL_ESC(${VAL10})}','${SQL_ESC(${VAL11})}')

But the entrace in database is same as I mentioned before divided in coloumn…(of course I restarted asterisk)
Any other suggestion?

johnkiniston · July 2, 2018, 3:12pm

Try encoding your string into something that’s safe to pass as an argument.

https://wiki.asterisk.org/wiki/display/AST/Asterisk+13+Function_BASE64_ENCODE

same => n,Set(MESSAGE=BASE64_ENCODE(Hello this example and it wont divide the message cause I didn’t use comma character.));

Then use the FROM_BASE64 function in MySQL to decode.

easydoor · July 2, 2018, 3:39pm

Thx for suggestion…
Idea is good…but if I got in messae some complex txt like this:

exten => sms,1,Set(MESSAGE=${BASE64_ENCODE("include => dongle-incoming-ussd ;"exten => _*.,1,DongleSendUSSD(dongle0,${EXTEN})  bla bla 123456789")});

It can not be encoded, couse asterisx complaning wih message:

[Jul  2 17:25:41] WARNING[19519]: pbx_config.c:1828 pbx_load_config: No closing parenthesis found? 'Set(MESSAGE=${BASE64_ENCODE("include => dongle-incoming-ussd' at line 15 of extensions.conf

I tried even:

exten => sms,1,Set(MESSAGE=BASE64_ENCODE("include => dongle-incoming-ussd ;"exten => _*.,1,DongleSendUSSD(dongle0,${EXTEN})  bla bla 123456789"));

and even:

exten => sms,1,Set(MESSAGE=BASE64_ENCODE(include => dongle-incoming-ussd ;"exten => _*.,1,DongleSendUSSD(dongle0,${EXTEN})  bla bla 123456789));

or with ’ instead of "
Btw how can I set variable with very complex txt, but not a static txt, with escape caracters, than with dynamic complex txt what can come from MESSAGE(body) variable for example.
Thx

johnkiniston · July 2, 2018, 5:14pm

Why are you trying to stuff dialplan in as your message? I feel like that might be the issue due to the contents?

Have you tried it with a simple test string? like a ‘hello world’ or ‘this is a test’?


 same => n,Set(MyString=Hello World)
 same => n,Set(EncodedString=${BASE64_ENCODE(${MyString})})
 same => n,NoOP(MyString is ${MyString} and EncodedString is ${EncodedString})

easydoor · July 2, 2018, 5:48pm

Thx again, for helping me…
Yes this working what you proposed, even decode:

same => n,Set(EncodedString=${BASE64_ENCODE(${MyString})})
same => n,NoOP(MyString is ${MyString} and EncodedString is ${EncodedString}, but the decoded is: ${BASE64_DECODE(${EncodedString})})

Why? Couse simulating the complex txt what if I receive. In most case that sending in gsm sms the twitter with lot of hashtag comma…and other special caracters what asterisk do not know how to handle (or at least I do not know to set in the variable).
If I put for example in your example next message:

same => n,Set(MyString="include => dongle-incoming-ussd ;"exten => _*.,1,DongleSendUSSD(dongle0,${EXTEN})") bla blaaaa");
same => n,Set(EncodedString=${BASE64_ENCODE(${MyString})})
same => n,NoOP(MyString is ${MyString} and EncodedString is ${EncodedString}, but the decoded is: ${BASE64_DECODE(${EncodedString})})

asterisk start to complain in cli after command:

dialplan reload

[Jul  2 19:41:07] WARNING[17794]: pbx_config.c:1828 pbx_load_config: No closing parenthesis found? 'Set(MyString="include => dongle-incoming-ussd' at line 18

My question is how to protect the variable to accept all what is for me the message txt? (look above)

david551 · July 2, 2018, 6:41pm

I wouldn’t try it directly from the dialplan. I’d use a language binding that allows native variables to be passed.

Trying to form the insert as a pure SQL statement with no parameter substitution is too likely to end up in a code injection attack.

johnkiniston · July 2, 2018, 7:07pm

Your test string appears to have unbalanced parens, I count two open and three close.

Please use different test data, I really think it’s the contents of your test data that is messing you up.

poing · July 18, 2018, 1:24am

@johnkiniston is right. You need to watch the use of parenthesis. You may also have to audit your code, and settle on a uniform type.

same => n,NoOp("this is acceptable")
same => n,NoOp('this is acceptable too')

same => n,NoOp("some times times, it's ok")
same => n,NoOp('some times it's tricky, and breaks things') ; oops

easydoor · October 3, 2018, 12:40am

After few months I jumped back on same problem…
@ johnkiniston
Yes, I realised…and it is not about that. Even if I fix it it is same.
This story with encode - decode, do not do nothing worthy in my case…
So I found that main culpit as a caracter is " and \.
So I used the STRREPLACE function in next way:

same => n,Set(MESSAGE(body)=${STRREPLACE(MESSAGE(body),"\"","'")}); \" is escape caracter for " couse the quotes ruin the writing in mysql base
same => n,Set(MESSAGE(body)=${STRREPLACE(MESSAGE(body),"\\","")}); \\ is escape caracter for \ couse this caracter ruin the writing in mysql base

I this case I have not any more dividing the rows in mysql database…
It is very importont to quote the variable, in my case: ${MESSAGE(body)} when I’m putting in ODBC function like this:


same => n(ok),Set(ODBC_D1_SMSHISTORY()=${CDR(TYPE_OF_CALL)},${CDR(start_)},"${MESSAGE(body)}",${MESSAGE_SEND_STATUS},${USER_1},${DONGLE_NAME},${CALLERID(num)},${CDR(phone_ip)},${DONGLE_MANUF_MODEL},${DONGLE_CARRIER},${CDR(useragent_)})

and in func_odbc.conf I used ${SQL_ESC()} funftion too:

....
....
[D1_SMSHISTORY]
prefix=ODBC
dsn=asterisk
writesql=INSERT INTO D1_SMS_HISTORY (sms_type,time_date,message,msg_send_stat,username,dongle_name,sms_from,phone_ip,dongle_model,provider,user_agent) VALUES('${SQL_ESC(${VAL1})}','${SQL_ESC(${VAL2})}','${SQL_ESC(${VAL3})}','${SQL_ESC(${VAL4})}','${SQL_ESC(${VAL5})}','${SQL_ESC(${VAL6})}','${SQL_ESC(${VAL7})}','${SQL_ESC(${VAL8})}','${SQL_ESC(${VAL9})}','${SQL_ESC(${VAL10})}','${SQL_ESC(${VAL11})}')
...
...

Thx for suggestion, and maybe someone will need this once!