MyCart Issues & Issues with Search AJAX & Site speed

Follow-up — same search code path, additional bug (SQL syntax error on apostrophes)
Site: argostheclassics.com (XStore + Elementor Pro Theme Builder, WooCommerce, PHP 8.2).
On top of the slow product-search results page (the N+1 loop I reported), the same search path also throws a SQL syntax error whenever a search term contains an apostrophe (‘).
Example — searching Children’s bed or Children’s bed montessori logs:
WordPress database error: You have an error in your SQL syntax … near ”s bed%” … for query SELECT SQL_CALC_FOUND_ROWS aor_posts.ID FROM aor_posts LEFT JOIN aor_term_relationships … AND meta_value LIKE ‘%Children’s bed%’
The apostrophe isn’t escaped before being placed in the LIKE clause, so the query breaks. The call stack points to the theme’s search rendering: …/themes/xstore/header.php → ETC\App\Controllers\Elementor\General\Search->render → WP_Query.
Impact: any customer searching a term with an apostrophe (e.g. “children’s”, “kids'”) gets a broken/empty result. It generated 7,500+ database errors (~20 MB of logs) in a short window. Because unescaped user input is reaching raw SQL, this is also an input-sanitization / security concern, not only a UX bug.
Please fix the escaping of the search term in the product-search SQL ($wpdb->prepare / esc_sql). This is the same et-core search path as the performance issue, so ideally both are fixed together. Happy to share a full log sample or temporary admin access.

Hi Andrew,

Thanks for the snippet. We added it to the child theme’s functions.php and confirmed it’s active (the function loads, posts_search is hooked, and we cleared OPcache) — but apostrophe searches still generate ~300 You have an error in your SQL syntax entries each. So the broken meta_value LIKE ‘%…%’ clause isn’t passing through the posts_search filter your snippet targets — it appears to be built as raw/secondary queries inside the et-core search render loop (where the snippet’s is_search() guard skips them). It looks like the fix needs to live inside et-core itself.

To make that easy, we’ve created a dedicated FTP account for you. Access details are below.

Notes

The account is scoped to wp-content, so you have full access to the theme and et-core plugin. If you need anything outside that (e.g. the raw error_log or to toggle WP debug constants), just let me know and I’ll provide it.

Current XStore Core is 5.7.6 — the issue reproduces on it with any apostrophe search term (e.g. children’s desk).
Please let me know once you’re finished so I can remove the access.

Hi team,

Thanks for the changes. Unfortunately the issue is not resolved — I re-tested under controlled conditions (emptied the error log, reset PHP OPcache so your edited plugin compiled fresh, then ran three product searches containing apostrophes).
Result: 909 SQL syntax errors logged — exactly 303 per search, identical to before your edit. The pages return HTTP 200 only because WordPress suppresses the DB errors and still renders.
The exact failing query and error:

WordPress database error You have an error in your SQL syntax … near ‘s wardrobe%’
for query SELECT SQL_CALC_FOUND_ROWS aor_posts.ID
FROM aor_posts INNER JOIN aor_postmeta ON ( aor_posts.ID = aor_postmeta.post_id )
… AND aor_posts.post_type = ‘product’ …
The unescaped LIKE clauses being generated:
LIKE ‘%kid’s wardrobe%’
LIKE ‘%baker’s table%’
LIKE ‘%children’s desk%’
Two important points:

In the same request, WordPress core’s own search query escapes the apostrophe correctly (LIKE ‘%kid\’s%’). Only the theme’s query is unescaped — so this is the theme building a LIKE fragment with the raw search term rather than passing it through $wpdb->prepare() / esc_like().

The broken query is a SELECT SQL_CALC_FOUND_ROWS … FROM aor_posts INNER JOIN aor_postmeta with a _price meta filter — this is not the Elementor product-grid render path you edited in product-grid.php. The unescaped term is being injected into the main product search query (the price/meta filtered one), so the fix needs to go wherever that search LIKE is constructed.

The FTP access is still active so you can apply the corrected fix directly. Please let me know once you’ve pushed it and I’ll re-run the same test.

Thanks!

______

Hi team,
I traced the actual source. Your product-grid.php change is written correctly (it uses esc_like + $wpdb->prepare), but it only applies to the Elementor Product Grid widget’s query — not the main search results query. That’s why the error count is unchanged: still exactly 303 SQL syntax errors per apostrophe search.
The real bug is in the theme:
File: wp-content/themes/xstore/framework/theme-functions.php
Function: etheme_search_post_excerpt() (added to posts_where by etheme_search_all_sku_query())
At line 853 the search term is taken unescaped:
php$s = $wp_the_query->query_vars[‘s’];
and then interpolated raw into LIKE clauses at lines 864, 893, 939, 940, 941, 946, e.g.:
php… AND meta_value LIKE ‘%$s%’
… post_title LIKE ‘%$s%’
Any search containing an apostrophe (kid’s wardrobe, baker’s table) breaks the SQL: error in your SQL syntax … near ‘s wardrobe%’.
Fix — escape $s at line 853, which covers all six LIKE sites at once:
php$s = esc_sql( $wp_the_query->query_vars[‘s’] );
Or the fully-correct version (also neutralises user-typed %/_ wildcards):
phpglobal $wpdb;
$s = esc_sql( $wpdb->esc_like( $wp_the_query->query_vars[‘s’] ) );
FTP is still active. Once you push it I’ll re-run the apostrophe test and confirm a clean log.

Thanks!

Hi team — your latest fix resolved the apostrophe SQL errors, but it broke the product search results page: every search returned “no products.”

Root cause: the new etheme_search_parent_products_by_variation_sku function (hooked to posts_search, priority 999) leaks WordPress’s %-wildcard placeholder into the final SQL — the executed query contains LIKE ‘{hash}term{hash}’ instead of LIKE ‘%term%’, so it matches nothing. It also matches the full phrase rather than individual words. This function isn’t in our pre-fix backup, so it was introduced in this update.

To keep the live store working, we’ve temporarily disabled that single filter via a must-use plugin, which restores native word-based search (verified: multi-word searches return correct products, apostrophe searches still produce 0 SQL errors). Please correct the placeholder handling in that function (the % wildcards must survive $wpdb->prepare()), and confirm word-based multi-term matching. FTP access is still active. Once you’ve shipped the corrected version we’ll remove our temporary plugin and re-test.

________

I traced exactly why the new fix broke the product search. The problem is the new function etheme_search_parent_products_by_variation_sku() (in wp-content/themes/xstore/framework/theme-functions.php, registered at line 1007, defined at line 1012). It has two bugs:
Bug 1 — it replaces the core search instead of extending it (breaks multi-word search).
The function ignores the incoming $search (which contains WordPress’s word-split logic) and returns a brand-new clause that matches the whole phrase: $like = ‘%’ . $wpdb->esc_like( $search_term ) . ‘%’ → post_title LIKE ‘%pink chair%’. No product title contains the literal contiguous string “pink chair”, so every multi-word search returns zero results. It needs to merge its SKU condition into $search, not replace it.
Bug 2 — the % wildcards leak as placeholder tokens (matches nothing).
$wpdb->prepare( ” … LIKE %s”, $like ) where $like already contains % causes prepare() to placeholder-escape those % into its internal {hash} markers. Because the string is returned via the posts_search filter, remove_placeholder_escape() isn’t applied to it, so the executed SQL literally contains:
LIKE ‘{bc631d548e…}pink chair{bc631d548e…}’
— i.e. it searches for the literal text {hash}pink chair{hash}, matching nothing. You must call $wpdb->remove_placeholder_escape() on the prepared fragment.
Here’s a corrected version that fixes both — it keeps WordPress’s word-split title/content search and adds the SKU/variation match as an OR:
phpadd_filter( ‘posts_search’, ‘etheme_search_parent_products_by_variation_sku’, 999, 2 );
function etheme_search_parent_products_by_variation_sku( $search, $query ) {
if ( is_admin() || ! ( $query instanceof WP_Query ) || ! $query->is_search() ) {
return $search;
}
$search_term = $query->get( ‘s’ );
if ( ” === (string) $search_term || ! get_theme_mod( ‘search_by_sku_et-desktop’, 1 ) ) {
return $search;
}
$post_type = $query->get( ‘post_type’ );
$is_product_query = ( ‘product’ === $post_type ) || ( is_array( $post_type ) && in_array( ‘product’, $post_type, true ) );
if ( ! $is_product_query ) {
return $search;
}

global $wpdb;
$like = ‘%’ . $wpdb->esc_like( $search_term ) . ‘%’;

// Build ONLY the extra SKU / variation-SKU condition — do NOT rebuild the title/content search.
$sku_clause = $wpdb->prepare(
“EXISTS (
SELECT 1 FROM {$wpdb->postmeta} product_sku
WHERE product_sku.meta_key = ‘_sku’
AND product_sku.meta_value LIKE %s
AND product_sku.post_id = {$wpdb->posts}.ID
)
OR {$wpdb->posts}.ID IN (
SELECT variation.post_parent
FROM {$wpdb->posts} variation
INNER JOIN {$wpdb->postmeta} variation_sku ON variation_sku.post_id = variation.ID
WHERE variation.post_type = ‘product_variation’
AND variation.post_status = ‘publish’
AND variation.post_parent > 0
AND variation_sku.meta_key = ‘_sku’
AND variation_sku.meta_value LIKE %s
)”,
$like,
$like
);

// CRITICAL: restore the % wildcards so they survive into the final query.
$sku_clause = $wpdb->remove_placeholder_escape( $sku_clause );

// MERGE with WordPress’s existing word-split search instead of replacing it:
// AND ( (core word search) OR (sku / variation-sku match) )
if ( ” !== trim( (string) $search ) ) {
$core = preg_replace( ‘/^\s*AND\s+/i’, ”, $search ); // strip leading “AND”
$search = ” AND ( {$core} OR ( {$sku_clause} ) )”;
} else {
$search = ” AND ( {$sku_clause} )”;
}

return $search;
}
Note this function is currently defined unconditionally (no function_exists guard), so we couldn’t override it from the child theme — we’ve temporarily disabled it via a must-use plugin to keep the live store searchable. Once you deploy the corrected version, tell us and we’ll remove our temporary plugin and re-test (multi-word, apostrophes, and a real variation-SKU lookup). FTP is still active.