[asterisk-users] Upgrading from 1.4.21.2 to 1.6.0.5 breaks sql queries with backslashes?

[Kristina Harris]

Hi, all. I've been searching google, bug reports and forums and have 
looked in all the asterisk-users list archives back to 2003 but haven't 
seen an answer to this, so thought I'd post here.

The problem seems to be that Asterisk 1.6.0.5 is sending backslashes 
(needed to escape commas and so forth in 1.4.21.2) as 
*literal* backslashes to Mysql, so that Mysql gives a syntax error and 
many things associated with app_addon_sql_mysql.c fail. I'm pretty sure 
this is an asterisk thing and not an addon thing because the query has the 
backslashes when app_addon_sql_mysql.c gets it whereas I would have 
expected asterisk to have already used them to escape the commas, but I 
could be wrong.

Here's an example of a failing query from extensions.conf (line 2) that 
works fine in 1.4.21.2 and fails in 1.6.0.5:

[vpbx-generic-cc-get-vpbx-number]
exten => s,1,MYSQL(Connect connid ${DBHOST} ${DBUSER} ${DBPW} ${DBNAME})
exten => s,2,MYSQL(Query resultid ${connid} SELECT vpbx_id\,vpbx.password\,vpbx.name FROMext\,vpbx WHERE mailbox="${CALLERID(num)}" and vpbx_id=vpbx.id)
exten => s,3,MYSQL(Fetch fetchid ${resultid} vpbx-id vpbx-password vpbx)
exten => s,4,MYSQL(Clear ${resultid})
exten => s,5,MYSQL(Query resultid ${connid} SELECT mailbox FROM ext\,devtype WHERE active="Y" and ext.devtype_id=devtype.id and devtype.model like "AUTOATTENDANT%" and vpbx_id=${vpbx-id})
exten => s,6,MYSQL(Fetch fetchid ${resultid} mailbox)
exten => s,7,MYSQL(Clear ${resultid})
exten => s,8,Set(vpbx-number=${mailbox})
exten => s,9,MYSQL(Disconnect ${connid})
exten => s,10,Return

And here's the error from 1.6.0.5:

[Apr 22 09:06:36] WARNING[17379]: app_addon_sql_mysql.c:311 aMYSQL_query: 
aMYSQL_query: mysql_query failed. Error: 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 '\,vpbx.password\,vpbx.name FROM ext\,vpbx 
WHERE mailbox="1234567890" and vpbx_id' at line 1

If I remove *all* backslashes in the Query line, it works fine in 1.6.0.5. 
If I add backslashes to all spaces and double-quotes, it fails in 1.6.0.5.

I have added all three "[compat]" options I could find to asterisk.conf 
although none of them would seem to affect this problem:

[compat]
app_set=1.4
pbx_realtime=1.4
res_agi=1.4

I have looked through all the UPGRADE instructions, but haven't found 
anything helpful.

It seems so odd that suddenly backslashes would not only not be required
but that they wouldn't work at all. Sure, I could just remove all the
backslashes in the configs for 1.6 and it would work, but it would be a
much smoother upgrade (and smoother fallback, if necessary) if the config
file changes are as minimal as possible between versions.

Anyone have any ideas? Am I just missing some compatibility option (or
combination thereof) somewhere?

Thanks for any info!

Kristina