Possible version-specific MySQL Syntax error when querying customer by email

[SeanTOSCD]

Bug Report

Reported by a user: https://wordpress.org/support/topic/bug-mysql-syntax-error-ver-5-5-55/

When trying to query a customer by email using: $customer = new EDD_Customer( $email );

Apparently the result is a syntax error.

Expected behavior

When retrieving a customer by email using EDD_Customer(), the customer object should be returned.

Actual behavior

Customer reports there is a syntax error. He is on MySQL version 5.5.55. I was unable to replicate on MySQL version 5.6.34.

Steps to reproduce the behavior

1) Run $customer = new EDD_Customer( $email ); in Debug Bar Console where $email is the (string) email address of one of your customer records. 2) If you are able to replicate, it should result in a syntax error.

Information (if a specific version is affected):

MySQL version: 5.5.55 PHP Version: pending EDD Version (or branch): pending WordPress Version: pending

[SeanTOSCD]

I was also unable to replicate on MySQL 5.5.55:

[cklosowski]

I also cannot replicate just yet. I'm going to wait for the system info to hit the support ticket to see if we can produce it via some environment issue.

The basic issue is that the meta table name is not being added to his query via the where parsing, where as in all my tests it's being added correctly.

[shrimp2t]

Hi,

Sorry for the SQL version it should bee 5.6.34 https://cl.ly/840ef0e947ea

PHP code:

if ( isset( $_GET['debug'] ) ) {
    $customer = new EDD_Customer( 'willbrogan@gmail.com' );
     var_dump( $customer );
   die();
}

This is the error message: https://cl.ly/a0a248e84687

WordPress database 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 'AS email_mt ON wp_edd_customers.id = email_mt.customer_id WHERE ( ( email_mt.met' at line 1]
SELECT wp_edd_customers.* FROM wp_edd_customers LEFT JOIN AS email_mt ON wp_edd_customers.id = email_mt.customer_id WHERE ( ( email_mt.meta_key = 'additional_email' AND email_mt.meta_value = 'willbrogan@gmail.com' ) OR email = 'willbrogan@gmail.com' ) GROUP BY wp_edd_customers.id ORDER BY wp_edd_customers.id DESC LIMIT 1

Here is the system info:

### Begin System Info (Generated 2018-09-27 01:53:58) ###

-- Site Info

Site URL:                 https://ft.local
Home URL:                 https://ft.local
Multisite:                No

-- Hosting Provider

Host:                     DBH: localhost, SRV: ft.local

-- User Browser

Platform:                 Apple 
Browser Name:             Chrome  
Browser Version:          69.0.3497.100 
User Agent String:        Mozilla/5.0 (Macintosh; Intel Ma 
              c OS X 10_13_5) AppleWebKit/537.
                          36 (KHTML, like Gecko) Chrome/69
                          .0.3497.100 Safari/537.36

-- WordPress Configuration

Version:                  4.9.8
Language:                 en_US
Permalink Structure:      /blog/%postname%/
Active Theme:             FameThemes 3.0.8
Show On Front:            page
Page On Front:            Home (#99696)
Page For Posts:           Blog (#13)
ABSPATH:                  /app/public/
Remote Post:              wp_remote_post() works
Table Prefix:             Length: 3   Status: Acceptable
WP_DEBUG:                 Enabled
Memory Limit:             40M
Registered Post Stati:    publish, future, draft, pending, private, trash, auto-draft, inherit, request-pending, request-confirmed, request-failed, request-completed, refunded, failed, revoked, abandoned, processing, active, inactive, acf-disabled, cancelled, edd_subscription, preapproval

-- EDD Configuration

Version:                  2.9.7
Upgraded From:            None
Test Mode:                Disabled
AJAX:                     Enabled
Guest Checkout:           Enabled
Symlinks:                 Disabled
Download Method:          Redirect
Currency Code:            USD
Currency Position:        before
Decimal Separator:        .
Thousands Separator:      ,
Upgrades Completed:       upgrade_payment_taxes,upgrade_customer_payments_association,upgrade_user_api_keys,remove_refunded_sale_logs,sl_add_bundle_licenses,sl_deprecate_site_count_meta,upgrade_24_subscriptions,fix_24_stripe_customers,stripe_customer_id_migration,recurring_27_subscription_meta,recurring_paypalproexpress_logs,upgrade_mailchimp_api3,upgrade_mailchimp_api3_default_list,eddmc_304_interests_fix,migrate_licenses,migrate_license_parent_child,migrate_license_logs,remove_legacy_licenses,update_file_download_log_data
Download Link Expiration: 24 hour(s)

-- EDD Page Configuration

Checkout:                 Valid
Checkout Page:            https://ft.local/checkout/
Success Page:             https://ft.local/thank-you/
Failure Page:             https://ft.local/checkout/transaction-failed/
Downloads Slug:           /themes

-- EDD Gateway Configuration

Enabled Gateways:         PayPal Express, Stripe
Default Gateway:          Stripe

-- EDD Tax Configuration

Taxes:                    Disabled
Tax Rate:                 0
Display On Checkout:      Not Displayed
Prices Include Tax:       No

-- WordPress Active Plugins

Advanced Custom Fields PRO: 5.7.4 (needs update - 5.7.6)
Easy Digital Downloads: 2.9.7
Easy Digital Downloads - 2Checkout Gateway: 1.3.12
Easy Digital Downloads - Auto Register: 1.3.9
Easy Digital Downloads - Free Downloads: 2.3.3
Easy Digital Downloads - Git Update Downloads: 9.69.6
Easy Digital Downloads - Invoices: 1.1.5
Easy Digital Downloads - MailChimp: 3.0.8
Easy Digital Downloads - Manual Purchases: 2.0.5
Easy Digital Downloads - PayPal Website Payments Pro and PayPal Express Gateway: 1.4.4
Easy Digital Downloads - Recurring Payments: 2.7.27
Easy Digital Downloads - Software Licensing: 3.6.3
Easy Digital Downloads - Stripe Payment Gateway: 2.6.15
EDD - Sendinblue: 0.0.1
EDD Auto Apply Discount: 1.0.0
EDD Enhanced eCommerce Tracking: 1.1.0
EDD Metrics: 0.7
Fame API: 1.0.1
FT EDD Bundle License: 1.0.0
FT EDD Fix Licenses Query: 
FT EDD Tools: 1.1.2
Gravity Forms: 2.3 (needs update - 2.3.4)
Insert Headers and Footers: 1.4.2 (needs update - 1.4.3)
MailChimp for WordPress - Premium: 3.3.3
Query Monitor: 3.1.0 (needs update - 3.1.1)
SiteOrigin Widgets Bundle: 1.11.2 (needs update - 1.13.0)
User Switching: 1.3.1 (needs update - 1.4.0)

-- WordPress Inactive Plugins

2Checkout EDD Webhooks: 1.1.1
Akismet Anti-Spam: 4.0.8
Better Search Replace: 1.3.2
Duplicate Post: 3.2.2
Easy Digital Downloads - SendinBlue: 1.0
Elementor: 2.0.12 (needs update - 2.2.4)
Elementor Pro: 1.12.0
Export Users to CSV: 1.1.1
Freemium Deployment: 1.1.1
FT Autocomplete Address: 1.0.0
FT EDD FastSpring: 1.0.0
Google XML Sitemaps: 4.0.9
Gravity Forms - Help Scout Docs Search Field: 3.0.2
Gravity Forms Help Scout Add-On: 1.5
Gravity Forms MailChimp Add-On: 3.7.1
Help Scout Desk: 3.1.4
Jilt for Easy Digital Downloads: 1.3.2 (needs update - 1.4.2)
MailChimp for WordPress: 4.2.2 (needs update - 4.2.5)
MailChimp User Sync: 1.5.4 (needs update - 1.7.5)
Manage Notification E-mails: 1.3.0
Messenger Customer Chat: 1.0
Notification Bar WP: 1.0.4
Page Builder by SiteOrigin: 2.6.0 (needs update - 2.8.2)
SendinBlue Subscribe Form And WP SMTP: 2.9.3
Simple Custom Post Order: 2.3.2
Support Downloads WP REST API: 1.0.1
Topbar Countdown: 0.0.1
WordPress Block Analytics Spam: 1.5
WordPress Importer: 0.6.3 (needs update - 0.6.4)
WP Mail SMTP: 1.2.1 (needs update - 1.3.3)
WP Maintenance Mode: 2.0.9 (needs update - 2.2.1)
WP Migrate DB Pro: 1.7.1
WP Migrate DB Pro Media Files: 1.4.7
WP Rocket: 3.0.5.1
Yoast SEO: 7.6.1 (needs update - 8.3)

-- Webserver Configuration

PHP Version:              7.0.3
MySQL Version:            5.6.34
Webserver Info:           nginx/1.10.1

-- PHP Configuration

Memory Limit:             256M
Upload Max Size:          1000M
Post Max Size:            1000M
Upload Max Filesize:      1000M
Time Limit:               900
Max Input Vars:           3000
Display Errors:           N/A
PHP Arg Separator:        &

-- PHP Extensions

cURL:                     Supported
fsockopen:                Supported
SOAP Client:              Installed
Suhosin:                  Not Installed

-- Session Configuration

EDD Use Sessions:         Enabled
Session:                  Disabled

### End System Info ###

[cklosowski]

@shrimp2t Ok Both @SDavisMedia and I have mimiced this EDD, WP, PHP, and MySQL version locally and cannot replicate this.

Since this appears to be local, can you try disabling all plugins but EDD and go back to a theme like TwentySeventeen?

[shrimp2t]

The same problem when I deactivated all plugins (Just active EDD) and use TwentySeventeen theme.

[SeanTOSCD]

@shrimp2t Do you have a live server you can replicate this on? I know you're working local now. We need to see if the issue can be isolated to your working environment or not. Right now, none of us are able to replicate it while attempting to match your environment.

[shrimp2t]

Hey

Why you don't check the SQL query that generates by the PHP code?

You can see the SQL code incorrect here:

SELECT wp_edd_customers.* FROM wp_edd_customers LEFT JOIN AS email_mt ON ...

The LEFT JOIN AS email_mt without table name.

See https://dev.mysql.com/doc/refman/8.0/en/outer-join-optimization.html

[SeanTOSCD]

@shrimp2t We've already checked the query that generates the code. Where you are missing a table, there is a variable in place ($meta_table) that is supposed to populate it. You can see the query here: https://github.com/easydigitaldownloads/easy-digital-downloads/blob/b3b09b718d04ded5611b8b34194cbb77190a2c47/includes/class-edd-customer-query.php#L478

For some reason, the table name is not being populated for you, but it is for us and other users, considering this query is used across several sites with no reported issues.

That's why I'm inclined to think there's something specific about your environment that we have to figure out. If you can, please spin up a fresh WordPress site, install EDD, complete a test purchase, and then query that customer to see if you can replicate on another site.

Or, if you're comfortable with debugging, go to line 477 of easy-digital-downloads/includes/class-edd-customer-query.php and add wp_die($meta_table);. Then run the following in Debug Bar Console, or your tool of choice:

$customer = new EDD_Customer( $email );
var_dump($customer);

As you can see in my results, the $meta_table variable populates the table name:

[SeanTOSCD]

@pippinsplugins I'm curious what you think we should do here.

Chris and I are unable to replicate. Furthermore, some pretty significant functionality would be broken for everyone using EDD if this were an issue.

One thing you did mention when we were in Florida is hardcoding the $meta_table value, but I don't think that was something we actually wanted to do.

I haven't heard back from @shrimp2t and there are no more reports of this (none ever as far as I know). Do you have any suggestions other than wontfix?

[pippinsplugins]

@SDavisMedia I think we leave it open but remove the mile stone until we have more clear replication. Clearly it's not happening for any large number of sites (this being the only known case).

What we really need from @shrimp2t is to know what _get_meta_table( 'customer' ) returns. I'd wager that's where the problem is, not in how the query is set up. If _get_meta_table( 'customer' ) isn't returning the right value, that would cause the behavior.

[SeanTOSCD]

That works!

@shrimp2t is it possible to check that out for us?