Author Topic: send email to contact: AJAX database error while looking up email address (Read 742 times)

ken · May 03, 2011, 03:49:45 am

Steps to reproduce in 3.4.0 ...

Visit an individual's contact record
Choose the action "Add an email"
The URL will be something like /civicrm/activity/email/add?action=add&reset=1&cid=10222&selectedChild=activity&atype=3
Type a space followed by a "C" into the "To" field
Press backspace to remove the "C" but not the space
The following messages appear in the logs (see below)

(I have a suggested fix, at the bottom)

Apache access log

Quote

127.0.0.1 - - [03/May/2011:20:03:39 +1000] "GET /civicrm/ajax/checkemail?id=1&name= HTTP/1.1" 200 10859 "https://ken.citybibleforum.local/civicrm/activity/email/add?action=add&reset=1&cid=10222&selectedChild=activity&atype=3" "Mozilla/5.0 (X11; Linux x86_64; rv:2.0.1) Gecko/20100101 Firefox/4.0.1"

CiviCRM log

Quote

May 03 20:03:46 [info] $Fatal Error Details = Array
(
[callback] => Array
(
[ 0 ] => CRM_Core_Error
[ 1 ] => handle
)

[ code ] => -2
[message] => DB Error: syntax error
[mode] => 16
[debug_info] =>
SELECT sort_name name, ce.email, cc.id
FROM civicrm_email ce INNER JOIN civicrm_contact cc ON cc.id = ce.contact_id

WHERE ce.on_hold = 0 AND cc.is_deceased = 0 AND cc.do_not_email = 0 AND cc.id IN ( )
AND cc.is_deleted = 0
LIMIT 0, 20
[nativecode=1064 ** 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 ')
AND cc.is_deleted = 0
LIMIT 0, 20' at line 4]
[type] => DB_Error
[user_info] =>
SELECT sort_name name, ce.email, cc.id
FROM civicrm_email ce INNER JOIN civicrm_contact cc ON cc.id = ce.contact_id

WHERE ce.on_hold = 0 AND cc.is_deceased = 0 AND cc.do_not_email = 0 AND cc.id IN ( )
AND cc.is_deleted = 0
LIMIT 0, 20
[nativecode=1064 ** 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 ')
AND cc.is_deleted = 0
LIMIT 0, 20' at line 4]
[to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="
SELECT sort_name name, ce.email, cc.id
FROM civicrm_email ce INNER JOIN civicrm_contact cc ON cc.id = ce.contact_id

WHERE ce.on_hold = 0 AND cc.is_deceased = 0 AND cc.do_not_email = 0 AND cc.id IN ( )
AND cc.is_deleted = 0
LIMIT 0, 20
[nativecode=1064 ** 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 ')
AND cc.is_deleted = 0
LIMIT 0, 20' at line 4]"]
)

May 03 20:03:46 [info] $backTrace = /var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Error.php, backtrace, 205
, handle,
/var/www/citybibleforum/sites/all/modules/civicrm/packages/PEAR.php, call_user_func, 931
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB.php, PEAR_Error, 968
/var/www/citybibleforum/sites/all/modules/civicrm/packages/PEAR.php, DB_Error, 564
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/common.php, raiseError, 1903
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/mysql.php, raiseError, 898
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/mysql.php, mysqlRaiseError, 327
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/common.php, simpleQuery, 1216
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/DataObject.php, query, 2420
/var/www/citybibleforum/sites/all/modules/civicrm/packages/DB/DataObject.php, _query, 1606
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 145
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/DAO.php, query, 869
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Contact/Page/AJAX.php, executeQuery, 735
, getContactEmail,
/var/www/citybibleforum/sites/all/modules/civicrm/CRM/Core/Invoke.php, call_user_func, 190
/var/www/citybibleforum/sites/all/modules/civicrm/drupal/civicrm.module, invoke, 355
, civicrm_invoke,
/var/www/citybibleforum/includes/menu.inc, call_user_func_array, 348
/var/www/citybibleforum/index.php, menu_execute_active_handler, 18

Suggested fix
From the Apache log, we can see that the 'name' parameter is the empty string, and there is no 'cid' parameter. The code at lines 688-698 of /CRM/Contact/Page/AJAX.php assumes that if the 'name' parameter in in the query is missing or empty, then there is a 'cid' parameter. The code should check if neither 'cid' nor 'name' is set and return an empty result-set.

Kurund Jalmi · May 03, 2011, 04:44:27 am

Fixed. Check this patch:

Quote

Index: CRM/Contact/Page/AJAX.php
===================================================================
--- CRM/Contact/Page/AJAX.php   (revision 34059)
+++ CRM/Contact/Page/AJAX.php   (working copy)
@@ -697,7 +697,7 @@
}
} else {
      $noemail = CRM_Utils_Array::value( 'noemail', $_GET );
-
+ $queryString = null;
if ( $name = CRM_Utils_Array::value( 'name', $_GET ) ) {
$name = CRM_Utils_Type::escape( $name, 'String' );
if ( $noemail ) {
@@ -705,22 +705,23 @@
} else {
$queryString = " ( cc.sort_name LIKE '%$name%' OR ce.email LIKE '%$name%' ) ";
}
- } else {
-            $cid = CRM_Utils_Array::value( 'cid', $_GET );
+ } elseif ( $cid = CRM_Utils_Array::value( 'cid', $_GET ) ) {
+ $cid = CRM_Utils_Type::escape( $cid, 'Integer' );
             $queryString = " cc.id IN ( $cid )";
          }

-     $offset = CRM_Utils_Array::value( 'offset', $_GET, 0 );
-     $rowCount = CRM_Utils_Array::value( 'rowcount', $_GET, 20 );
+ if ( $queryString ) {
+ $offset = CRM_Utils_Array::value( 'offset', $_GET, 0 );
+ $rowCount = CRM_Utils_Array::value( 'rowcount', $_GET, 20 );

- // add acl clause here
- require_once 'CRM/Contact/BAO/Contact/Permission.php';
- list( $aclFrom, $aclWhere ) = CRM_Contact_BAO_Contact_Permission::cacheClause( 'cc' );
- if ( $aclWhere ) {
- $aclWhere = " AND $aclWhere";
- }
- if ( $noemail ) {
- $query="
+ // add acl clause here
+ require_once 'CRM/Contact/BAO/Contact/Permission.php';
+ list( $aclFrom, $aclWhere ) = CRM_Contact_BAO_Contact_Permission::cacheClause( 'cc' );
+ if ( $aclWhere ) {
+ $aclWhere = " AND $aclWhere";
+ }
+ if ( $noemail ) {
+ $query="
SELECT sort_name name, cc.id
FROM civicrm_contact cc
{$aclFrom}
@@ -729,13 +730,13 @@
LIMIT {$offset}, {$rowCount}
";

- $dao = CRM_Core_DAO::executeQuery( $query );
- while( $dao->fetch( ) ) {
- $result[]= array( 'name' => $dao->name,
- 'id' => $dao->id);
- }
- } else {
- $query="
+ $dao = CRM_Core_DAO::executeQuery( $query );
+ while( $dao->fetch( ) ) {
+ $result[]= array( 'name' => $dao->name,
+ 'id' => $dao->id);
+ }
+ } else {
+ $query="
SELECT sort_name name, ce.email, cc.id
FROM civicrm_email ce INNER JOIN civicrm_contact cc ON cc.id = ce.contact_id
{$aclFrom}
@@ -743,19 +744,19 @@
{$aclWhere}
LIMIT {$offset}, {$rowCount}
";
-

- $dao = CRM_Core_DAO::executeQuery( $query );
+ $dao = CRM_Core_DAO::executeQuery( $query );

- while( $dao->fetch( ) ) {
- $result[]= array( 'name' => '"'.$dao->name.'" <'.$dao->email.'>',
- 'id' => (CRM_Utils_Array::value( 'id', $_GET ) ) ? "{$dao->id}::{$dao->email}" :'"'.$dao->name.'" <'.$dao->email.'>');
- }
- }
+ while( $dao->fetch( ) ) {
+ $result[]= array( 'name' => '"'.$dao->name.'" <'.$dao->email.'>',
+ 'id' => (CRM_Utils_Array::value( 'id', $_GET ) ) ? "{$dao->id}::{$dao->email}" :'"'.$dao->name.'" <'.$dao->email.'>');
+ }
+ }

- if ( $result ) {
- echo json_encode( $result );
- }
+ if ( $result ) {
+ echo json_encode( $result );
+ }
+ }
}
CRM_Utils_System::civiExit( );
}

ken · May 03, 2011, 05:47:16 am

Thanks Kurund!

That patch fixes the problem for me.

Ken

Kurund Jalmi · May 03, 2011, 09:14:41 pm

Fixed and will be part of next release.

Kurund

CiviCRM Community Forums (archive)

News:

Author Topic: send email to contact: AJAX database error while looking up email address (Read 742 times)

ken

send email to contact: AJAX database error while looking up email address

Kurund Jalmi

Re: send email to contact: AJAX database error while looking up email address

ken

Re: send email to contact: AJAX database error while looking up email address

Kurund Jalmi

Re: send email to contact: AJAX database error while looking up email address