Dynamic Routes Errors

[comtech]

I have two FreePBX servers, one production (few months), one brand new (testing).
Both are:

• PBX Version: 16.0.26
• System Version: 12.7.8-2208-2.sng7
• Dynamic Routes: 16.0.2

The only differences I notice are:

• Asterisk version 16.8 on the working box and version 16.27.0 on the new testing box
• I am building with a certificate on the working box (https) and no certificate on the testing (http)

The issue I notice is that when I am doing MySQL Source Type the query does not seem to accept % for wild cards on the new testing server. The older production server work fine.

I am trying to input the following query:
SELECT EXISTS(SELECT grplist FROM asterisk.findmefollow WHERE grplist LIKE '%${CALLERID(num)}%') as truth

This has been working on the production box for months, but when I type or copy and paste in the new test box, when I put the SQL statement in and click the submit button I go to a " Hmmm… can’t reach this page" error.

With some trial and error I figured out that adding the SQL wildcards (%) is what is causing this error to pop.

If I enter:
SELECT EXISTS(SELECT grplist FROM asterisk.findmefollow WHERE grplist LIKE '${CALLERID(num)}') as truth

The submit button works on the new testing box and FreePBX behaves normally.

If I enter:
SELECT EXISTS(SELECT grplist FROM asterisk.findmefollow WHERE grplist LIKE '%${CALLERID(num)}%') as truth

I get that webpage error.

Any ideas on what is going on? Is this happening to anyone else? I have several queries that rely on the wildcard and its working on the slightly older box.

Thanks for any guidance in advance.

[comtech]

Well, for now, I edited the fields in MySQL table via workbench, and it seems to work that way. I will submit a bug report and see if that yields a result.

[PitzKey (Itzik)]

Maybe try escaping the quotes and percent symbols?

https://wiki.asterisk.org/wiki/display/AST/Asterisk+18+Function_SQL_ESC

[lgaetz (Lorne Gaetz)]

Attempting to repro this now [ edit - working for me using your example]. You can open a bug in the contributed modules project, but at this point the parts of the dynroutes module dealing with mysql needs a full overhaul as the dialplan applications it currently relies on are all removed starting in Asterisk 19+. The module needs to be updated to use an AGI for SQL, as @franckdanard has already done for the CID Lookup module.