Bug in CallerID Lookup module with SQL query presented with ending changed when handed to asterisk?

Hi,

I’ve created a database for contacts with a php frontend and this is working fine with with read and write through php and SQL queries through mysql shell.

Running with Asterisk 1.8.9.1 and FreeePBX version 2.10.0rc1.1 with CallerID Lookup Version 2.10.0.1

The following query is configured in the query field of the CallerID Lookup module

SELECT concat_ws(’ ',firstname,lastname) name FROM addressbook WHERE (mobile LIKE ‘%[NUMBER]%’ OR home LIKE ‘%[NUMBER]%’);

Take note of the '); at the end of the query, this is where the bug comes in later…

When running the query in mysql shell it returns the result no problem

Current database: addressbook

mysql> SELECT concat_ws(’ ',firstname,lastname) name FROM addressbook WHERE (mobile LIKE ‘%07403%’ OR home LIKE ‘%07403%’);
±------------+
| name |
±------------+
| Craig Allen |
±------------+
1 row in set (0.00 sec)

mysql>

The asterisk database has the correct entry in the Asterisk/cid_lookup/mmysql_query table

Using this query against the asterisk db - SELECT mysql_query FROM cidlookup -gives the correct result in the cidlookup table as the query placed in the FreePBX cidlookup module.

SELECT concat_ws(’ ',firstname,lastname) name FROM addressbook WHERE (mobile LIKE ‘%[NUMBER]%’ OR home LIKE ‘%[NUMBER]%’);

HOWEVER, this is where the bug comes in, when a call initiates the lookup starts but shows an error. The query ending is changed and this causes a formatting issue.

The correct ending is '); but this is changed to ") as per the bold text highlighted below from the logs.

[Feb 11 20:29:31] VERBOSE[5375] pbx.c: – Executing [1004179@from-trunk:3] Gosub(“SIP/1004179-00000008”, “cidlookup,cidlookup_2,1()”) in new stack
[Feb 11 20:29:31] VERBOSE[5375] pbx.c: – Executing [cidlookup_2@cidlookup:1] MYSQL(“SIP/1004179-00000008”, “Connect connid localhost ******* ******** addressbook”) in new stack
[Feb 11 20:29:31] VERBOSE[5375] pbx.c: – Executing [cidlookup_2@cidlookup:2] MYSQL(“SIP/1004179-00000008”, “Query resultid 1 SELECT concat_ws(’ ',firstname,lastname) fullname FROM addressbook WHERE (mobile LIKE ‘%07403772904%’ OR home LIKE ‘%07403772904%’[B]”)[/B] in new stack
[Feb 11 20:29:31] WARNING[5375] app_mysql.c: 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 ‘’ at line 1

This seems to be a bug to me, either in FreePBX handling of the query or Asterisk database lookup

Any help with this would be appreciated.

Craig

Hi,

Does anyone have any thoughts or help on this? I’ve searched a lot on most peoples favourite fountain of all knowledge, but coming up short on a solution.

Thanks

Craig

I assume you have tried the query without the trailing semi-colon. Most of my experience with the CID module is with http lookups, but the few times I use a MySQL query, I don’t recall terminating queries with a semi-colon.

Hi Tadpole,

Thanks for responding. The issue was around ascii codes. I changed the ending to the US ascii character and it spoofed the correct ending I needed.

All working now.

Take it easy.

My callerID is also not working. It is showing the default caller ID–but not as desired in the SQL copied and pasted below. i’ve setup this in the the CIDlookup source and specified in the inbound route

SELECT concat(ID_usr, ‘:’, LNAME, ', ', fname) as name from web_users where usr_phone_cell LIKE ‘%[NUMBER]%’;

(the correct username, server, and password are also specified). where do i find the log, as referenced above to see where the problem is…?
the SQL is configured correctly and returns the correct phone number in MySQL.

any help/pointers would be most appreciated (i’m very new to Linux, therefore some step by step direction may be very useful).

thanks
lee

Which ascii characters did you use to solve this issue?
I am having issues with a simple query such as SELECT COUNT(*) FROM TABLE and get the same issue you mentioned (the issue with the closing parentheses “’)”).

Any help or even better an example on how you wrote your query with the ascii characters would be much appreciated.

Thanks in advance
Ken

Use a query that doesn’t use % (percent) signs . For some reason it croaks on them.