Apostrophe Issue

[tylerkolden]

I am having an issue with names that have an apostrophe ( ‘ ) in them.
I have the following query in a logic hook. The purpose is to get an email address from another module based on the first and last names. The email addresses are not stored in the email tables in sugarCRM.

     $first_name = $bean->first_name;
    $last_name = $bean->last_name;
    //$first_name = "Theresa";
    //$last_name = "O'Loughlin";
                        
    ///obtain email address///
    $query1 = "select rolus_onmission.first_name, rolus_onmission.last_name, rolus_applications.email 
                    from rolus_onmission
                    join rolus_applications 
                    on
                    rolus_applications.first_name  = rolus_onmission.first_name
                                        and
                                        rolus_applications.last_name  = rolus_onmission.last_name
                    where 
                    rolus_applications.first_name = \"$first_name\"
                    and
                    rolus_applications.last_name  = \"$last_name\" " ;
                    $results1 = $bean->db->query($query1, true);
                    $row1 = $bean->db->fetchByAssoc($results1);
                    $applicant_email = $row1['email']; 
  

When this logic hook encounters names without an apostrophe, it works very well and properly provides the email address. However when the name contains and apostrophe, it fails.

It is also very interesting that when the first and last names are not collected from the bean, but stated (currently commented out) the query works properly.

Below is a copy of the query:

select rolus_onmission.first_name, rolus_onmission.last_name, rolus_applications.email from rolus_onmission join rolus_applications on rolus_applications.first_name = rolus_onmission.first_name and rolus_applications.last_name = rolus_onmission.last_name where rolus_applications.first_name = "Theresa" and rolus_applications.last_name = "O'Loughlin"

It works at a mysql command line query but not through sugarcrm.

Any help would be greatly appreciated.

Tyler

[andopes]

Try this code:

 $first_name = $bean->db->quote($first_name); 
  

Do the same for other variables.

Regards

[tylerkolden]

Amazing. It worked perfectly. I simply added your line of code for each variable and the SQL produced the proper result.

 $first_name = $bean->first_name;
$last_name = $bean->last_name;  
$query_first_name = $bean->db->quote($first_name);  
$query_last_name = $bean->db->quote($last_name); 
  

Thank you!