Search Breaks with uppercase database table prefix

When a mysql table prefix is used with an uppercase letter, the geolocation search on the front breaks.

Steps to reproduce

install or change the table prefix to contain anything with an uppercase letter. AND adjust the table prefix to match the new table names. The search no longer functions properly and the php error log produces this error message…

"WordPress database error Unknown column ‘vkinz9wo_postmeta.meta_value’ in ‘order clause’ for query SELECT SQL_CALC_FOUND_ROWS VkINz9WO_posts.ID FROM VkINz9WO_posts INNER JOIN VkINz9WO_postmeta "

SQL is trying to select a table with lowercase, when the table contains upper case characters.

This is because the plugin uses the sanitize_key() function which makes lower case.

Do not include the table prefix in the sanitize_key() to avoid.

Renaming of table prefix is a common security functionality with some wordpress plugins and control panels… ie Plesk will mix upper and lowercase letters in the prefix.

Allow plesk to do this will break the geolocation search.

We have tested it locally and it seems to be ok. If there are specific steps to reproduce this with a fresh HivePress installation please send the details and we will try it

Hello Yevhen.
It is definitely an issue. There are a lot of ways the wordpress prefix can be changed. It’s actually a recommended step to change the prefix for wordpress security.

We came across the bug when we installed the theme on a plesk control panel server.
Plesk is one of the most common hosting control panels.

With plesk wordpress toolkit WordPress Toolkit by Plesk
During install, securing or hardening there is the option to change the wordpress database prefix as a recommend security measure. The default wordpress prefix is wp_ It was change to random letters and numbers.

The bug comes into play when the prefix is changed to include any uppercase letters.

The website functions but the geo-location search breaks and displays no results.

To Duplicate:

  1. install the the theme
  2. configure the geo-location plugin with a google map key
  3. change the WordPress database table prefix from wp_ to anything that contains an uppercase letter. ie “Wp_”. Plesk defaults to upper and lower case random letters.
  4. make sure to change the wp-config to include $table_prefix = ‘Wp_’; to match the prefix of the tables.

This is the simplest instructions:

4. The site will load correctly and appear to be working, but make sure to test the geo-location search. It is the geolocacation plugin where the bug is and that is broken when there is an uppercase letter in the wordpress table prefix.

Hope this helps.

Our geolocation has been broken for months and we didn’t know it. When it was brought to our attention we figured out what is was through the php error logs and once we renamed the wordpress tables by changing the uppercase letters in the table prefix to all lower case it worked immediately again.

As you can see from the php error log we figured it out because the geolocation plugin is forcing lower case letter and the table contains upper case, so the php error is that the table doesn’t exist.
But of course it does, just not with a name that’s lower case, because it’s upper case.

Thanks for reporting this, indeed some DB installations use case-sensitive prefixes, for some reason our local DB installation (MySQL 8.0.16) ignores this. This will be fixed in the next update hivepress-geolocation/class-geolocation.php at master · hivepress/hivepress-geolocation · GitHub

1 Like

This is our stack:

Ubuntu 20.04.4 LTS
Plesk 18.0.43 Update #1
Apache 2.4.41-4ubuntu3.10
nginx/1.20.2
PHP 7.4.29. (FPM/FastCGI)
MySQL 8.0.23

it’s the this command in the plugin code that is forcing lowercase letters and breaking the search

sanitize_key()

There’s a new Geolocation update so it should be ok now, we removed the sanitize_key function.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.