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%'") 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