CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • send email to contact: AJAX database error while looking up email address
Pages: [1]

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

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
send email to contact: AJAX database error while looking up email address
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.
« Last Edit: May 03, 2011, 04:00:16 am by ken »

Kurund Jalmi

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4169
  • Karma: 128
    • CiviCRM
  • CiviCRM version: 4.x, future
  • CMS version: Drupal 7, Joomla 3.x
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: send email to contact: AJAX database error while looking up email address
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.'" &lt;'.$dao->email.'&gt;',
+                                          '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( );
     }

Found this reply helpful? Support CiviCRM

ken

  • I live on this forum
  • *****
  • Posts: 916
  • Karma: 53
    • City Bible Forum
  • CiviCRM version: 4.6.3
  • CMS version: Drupal 7.36
  • MySQL version: 5.5.41
  • PHP version: 5.3.10
Re: send email to contact: AJAX database error while looking up email address
May 03, 2011, 05:47:16 am
Thanks Kurund!

That patch fixes the problem for me.

Ken

Kurund Jalmi

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 4169
  • Karma: 128
    • CiviCRM
  • CiviCRM version: 4.x, future
  • CMS version: Drupal 7, Joomla 3.x
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: send email to contact: AJAX database error while looking up email address
May 03, 2011, 09:14:41 pm
Fixed and will be part of next release.

Kurund
Found this reply helpful? Support CiviCRM

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using Core CiviCRM Functions (Moderator: Yashodha Chaku) »
  • send email to contact: AJAX database error while looking up email address

This forum was archived on 2017-11-26.