Have you ever changed something in your WordPress admin and received an error that leaves you scratching your head for 2 days and not knowing what the heck went wrong? I just experienced that while working on one of the WordPress themes.
By default, everything worked just fine. By default meaning if you set all default values (database, tables and what not during installation), there was no error of any sort. I never actually changed anything in installations and trusting WordPress security for fixing any bugs or errors it might get. So, I never got the error. But then as security loopholes and vulnerability for hacks exist in any system, people tend to make is more secure and hard to hack by changing setttings on their own. One them being the table prefixes (wp_) for WordPress.
So, during installation, there was an instance when the default wp_ table prefix was changed to gpp_. Now, this theme had theme options and its short name also used gpp_ so it saved all the theme option values within gpp_*. The table prefix gpp_ also saves one of its very important field – gpp_user_roles, gpp_ coming from the prefix which otherwise would have been wp_user_roles. This field saves who can access the admin panel.
Now, the problem was whenever I reset the Theme Options, I used to get the “You do not have sufficient privileges to access this page.” error.
I looked all over the place, googled quite a number of times and found out that there was a field called wp_user_roles and wp_ was replaced with whatever table prefix you set. I then analyzed like 5-6 times trying to create a new install of WordPress, activating the theme, saving the theme options and resetting the theme options. At last I found out that everytime I reset the theme options, all instance of gpp_option_names were getting deleted including gpp_user_roles. So, that explained why I was getting insufficient privileges error.
Then, I looked at the reset code.
$query = "DELETE FROM $wpdb->options WHERE option_name LIKE 'gpp_%'";
It looked okay. gpp_ wasn’t hardcoded anywhere and the statement just looked okay. It deleted all instance starting with gpp_.
Then I tested another table prefix gpps_.
Oops! It deleted that too.
I tried another prefix : gppssssssss_.
My goodness! It deleted that too. Still getting the same error. The statement says specifically to just remove the ones matching gpp_ not gpps_ or gppssssssss_.
This left me thinking quite a lot and searching a lot. At one point I even thought LIKE and DELETE don’t go well together or if its deleting everything that started with g. But this wasn’t the case.
Then, one of my friends suggested if _% is some syntax in MySQL. It wasn’t. But I did find out that _ and % characters didn’t always play well with querying. In fact they were both used as wildcards. This was a bug with MySQL and perhaps it still is. So, the query was only understanding to delete all instances that started with gpp, not gpp_.
Finally, I found that in order to search for gpp_ in queries, you need to add a backslash otherwise it will just assume it as an escape character. So it needed to be gpp\_. Hence the query:
$query = "DELETE FROM $wpdb->options WHERE option_name LIKE 'gpp\_%'";
That solved the issue of just deleting all the instance that started with gpp_. But then, if someone somehow had a table prefix with gpp_, he is still going to be in trouble. So, I had to change the query to
$query = “DELETE FROM $wpdb->options WHERE option_name LIKE ‘gpp\_%’ AND option_name != ‘gpp_user_roles'”;
This saved the table data gpp_user_roles. So, no more You do not have sufficient privileges to access this page error.
I hope my 2 days of brain jam and timeless research helped you if you are having the same problem. I am happy to have learnt one new thing although it would have been easier and quicker if I was a hardcore programmer.