Tag: database

Recovering Your Cape
One of the odder “hacks” out there is one where the person, once they get in, de-frocks your Super Admin on a Multisite. This isn’t always a hack, sometimes it’s just a simple mistake.

To quote my friend Jen Mylo:
1. People give away admin rights like logo-encrusted keychains at a car show and then the new admins abuse the power.
2. Someone who has admin rights deservedly but doesn’t know code makes a mistake.
[…]

Some people make bad decisions about who to give admin roles.
There’s an extra level of problems with making everyone and their pet monkey a super-admin on Multisite. You may think it’s a great thing, because now someone else can add new users, install plugins, edit themes and plugins, and of course, use iframes and PHP and such in widgets.

We run a Multisite at work, and they let me ‘secure it up’ recently. The first thing I did was demote pretty much everyone except five of us to ‘Editor.’ I told them all that I’d done this, and if they found something they couldn’t do, tell me, and I’d fix it. At this point, I’ve changed only three people to Admin, and dropped even more to ‘Author.’ Why? Because they don’t need to have high levels of access to do what they need to do! The admins on the site can tweak theme settings, play with widgets, and add ‘existing users.’ Everyone else? They just write content. Heck, most of them don’t even need to be Editors, but we gave them that level so they could help us copy-edit other posts. Two people complained “I need Super Admin access!” and I gave them my best Enda: NO CAPES.

Limit your admins, and there is less of a chance someone will accidentally remove access from the wrong person.

So now that that’s out of the way, how do you get it back?

Normally, reinstating an admin account is pretty easy. You go in via mySQL, pop open the wp_usermeta table, find your ID, and toss this in for meta_value for wp_capabilities: a:1:{s:13:"administrator";b:1;} That won’t restore all the roles, if you happen to be using Role Scoper, or some other management tool, but if you’ve got that, you can do anything. If you’re using WP-CLI, wp user update 1 --role=administrator (assuming you’re user ID 1).

There’s a sidebar/caveat to this. Sometimes this doesn’t work, and it happens if you change your DB prefix. So normally you have the prefix wp_ and the table wp_options. In that table you have a option named wp_user_roles and everything works. But then you make a new site, and you pick a different DB prefix, maybe you heard it was more secure, or maybe you wanted both tables in the same DB. Either way, now you have wp_wdssrr_options instead, and when you copy over your old options content, no one can log in. It’s because you have to rename that option to wp_wdssrr_user_roles

I just had a site with this problem last week.

On the other hand, getting back Super Admin access is less straightforward, but by no means is it impossible.
1. Go into wp_sitemeta and look for site_admins.
2. In there you will see something like this: a:1:{i:0;s:7:"Ipstenu";}
3. If your userID is ‘superman’ then it would be a:1:{i:0;s:8:"superman";}
Capitalization and stringlength matter. Add one user, and use that to correctly restore power to the others.

Can you do this via WP-CLI? Yes, if you’re on the latest versions. Kind of. You can get a list of super admins via wp network-meta get 1 site_admins and in theory wp network-meta update 1 site_admins USERNAME would work except that the data is serialized. I opened a ticket with WP-CLI, and it’s a ‘plugin territory’ issue right now, so I’ll have to see if I can code it myself.
25 June, 2013
phpMyAdmin vs CMS

By ‘CMS’ I mean WordPress, Drupal, whatever.

So here’s a funny. I moved my DreamHost site to a VPS, so I could learn nginx (I’m still a newb) and suddenly I couldn’t get to my phpMyAdmin anymore! Before someone says it’s DreamHost’s fault, this is actually due to how I installed subdomains (which I don’t recommend) and configured nginx (still optomizing). That said, not every CMS is flexible enough for this, so if you have this problem too, here’s a work-around.

As you know, you’re supposed to go to http://mysql.elftest.net which then sends you to http://elftest.net/dh_phpmyadmin/mysql.elftest.net/ to get to your phpMyAdmin page. Except it gives you a 404 styled like your site instead.

Solution? Make a fake subdomain.

I made db.elftest.net (which has nothing), but now I can go to http://db.elftest.net/dh_phpmyadmin/mysql.elftest.net/ and log in!

How I buggered my subdomains, and how I fixed it, is another post.

3 October, 2012
Making a Stand Alone SQL Account

One of the ways to secure your web apps is to limit the damage they can cause. When you create a database for a webapp, you have to provide a user ID and password to connect to the database, logically enough. Illogically, most people just use the same username and password they use to SSH into their server. After all, it works.

The obvious problem with this is that if someone gets access to your files (via a security hole in your webapp or your webhost), they now know your server password and ID, and can get in and cause serious damage.

But what if instead of using that normal ID and password, you made a special one that only was used for SQL. You couldn’t log in with it, you couldn’t FTP or anything except play with SQL. Then, even if they got in, they couldn’t delete your files! That’s really simple.

cPanel

If you’re using cPanel, just go in to the MySQL Databases screen and add a new user. I like to use something totally obvious, so I can remember it, like ipstenu_sql.

For those passwords, I tend to use the generator to make something like m}+akwQN=&)!, not because I feel they’re more secure (I prefer pass-phrases, like ‘donkeyvanillatapdance’), but as a reminder for me not to use it for anything but SQL. Hang on to the password right now, though, you’ll want it in a minute.

Then you add the user to the databases. Back on the main MySQL page, there’s a little selection to Add User to Database which is really obvious to use. Pick your user and your database.

Clicking Add will take you to the privileges screen:

Give the user ALL privileges, as you may need this later on.

Plesk

It’s just as easy in Plesk. Once your new database was created you, were automatically brought to the area to create the New Database User. If you didn’t do that, it’s okay, just go back the main database page and find the datase you want to add the user to (in this case, it’s LovePlesk_NewDatabase). Click on the Add New Database User icon, fill in the information (remember to save your password!), and click okay.

Plesk should automatically grant the user ALL privileges.

Updating Your WebApp

Once you have the new user made, all you have to do is edit your config file (i.e. wp-config.php for WordPress) to use the user and password, and hit save.

Now you’ve made your install a little more secure.

3 May, 2012
Moving WordPress Multisite
I answer this a lot.

Edited to add: If you’re just moving to a new server and keeping your domain name, it’s exactly like moving Single Install WordPress. Just remember to make sure your new server is set up to handle wildcard domains, and your httpd.conf has ‘AllowOverride’ set to ‘All’ and you should be fine. If you’re moving to a new domain name, read on!

Edit #2: The Incerconnectit Search/Replace DB script is also perfect to use, though you still need to manually edit a couple places.

Moving a normal WordPress site is really easy. Copy over your data. Change your URLs, do a search/replace on your wp_posts table (to fix any internal links), go out for a beer. Some of you may need to edit a wp-config file, but mostly that’s it.

Then there’s Multisite, which sucks. See, unlike single installs of WordPress, you can’t change your site URLs easily. Don’t get me wrong, if you’re a Super Admin, you can easily go into WP Admin -> Network -> Sites and edit the sites. You’d have two places to edit it:

On the Info Tab

On the Settings Tab

But here’s where it starts to suck. If you’re changing tech.ipstenu.org to press.ipstenu.org for example, you do that and then you need to go to your database and look for the site posts table (in this case, it’s wp_2_posts) and search/replace tech.ipstenu with press.ipstenu.

That’s not terrible, right? It looks a lot like moving a single install of WordPress.

What if you have your site as subfloders, using ipstenu.org/tech and ipstenu.org/press though? And you want to move everything to lipstenu.org?

This is where it sucks.

See some widgets and theme settings store your data and include your URL. This is done with data serialization as well, which means the length of your URL matters. If you changed from ipstenu.org to Lpstenu.org, then you would be perfectly safe doing a total database search/replace of the domain name! But since I’ve proposed changing it to Lipstenu.org, I can’t do that. Any field that counted my domain name would be off by one, and thus invalid, and thus wipe out my settings. Oh and to make it worse? Depending on how you uploaded your media and included it in your site, your postmeta table might also be filled with this.

It’s important to understand two things here.
1. This situation exists on a normal single site install.
2. You don’t have to change it in those places!
And as a maybe third, I know a lot of people who do a blanket search/replace all the time and never have a single problem. But because I know enough who do have issues, I can’t safely recommend you try it unless you have a rock-solid backup of your database.

This brings us to the point. How do you move WordPress Multisite to a new domain name?

Very, very, carefully.

You’re going to have to do some work in the database, so now’s the time to get some coffee and practice not freaking out. If you have phpMyAdmin, editing your WordPress database is not terrifying, but like a cat, should be approached with caution. Remember to take a full backup of your database before you start. A good backup.

First, it’s perfectly safe to edit all wp_posts (and wp_x_posts) tables with a search/replace of your domain name. I strongly suggest using as much of the domain as you can: i.e. http://newdomain.com instead of just newdomain. This will make sure you don’t confidently change the content of your posts. The Incerconnectit Search/Replace DB script is also perfect to use here, but it won’t fix everything, which is why we have another step:

Next you need to manually go through these tables:
- wp_site
- wp_blogs
Those two tables are really straight forward, by the way. You’ll see what to edit right away.

Then you have to manually (again) review all the wp_x_options tables and look for THREE fields:
- home
- siteurl
- fileupload_url
Only edit those. And yes, you have to do it manually in each of the options table unless you used the Incerconnectit Search/Replace DB script earlier.

Once you’ve done that, go into your wp-config.php and see if you have to change define( 'DOMAIN_CURRENT_SITE', 'ipstenu.org' ); (depending on your change, you may not).

And … that should be it.

It’s a pain, but it’s not insurmountable.

Right away, though, you can see the complications if you’re moving a site from ipstenu.org/wordpress to ipstenu.org, and while most of the changes remain similar, you need to remain vigilant and attentive with every change you make. The wp_blogs table is where it gets stickiest, as you have to add in the new subfolder (or remove it) by editing a separate field in the row.

Just pay attention, read carefully, and remember to breathe.
31 January, 2012
WordPress: Open Comments More Selectively
There are a whole lot of tricks you can do with SQL to enable/disable comments.

If you want to turn them on for every single page, there’s this:
```
UPDATE wp_posts SET comment_status = 'open';
```
But me? I don’t like to blanket turn things on! I don’t like to have my pages allow comments, you see, so I use this instead:
```
UPDATE wp_posts SET comment_status = 'open' WHERE post_type = 'post';
```
I find this preferable than just opening it for everything (most people will tell you to omit the WHERE clause) since if you do that, you open comments for attachments, pages, CPTs and everything. Which I never want.

But what if you did open comments for everything and now you realized, oh snap!, you want to turn them off on attachment pages? That’s pretty easy:
```
UPDATE wp_posts SET comment_status = 'closed' WHERE post_type = 'attachment';
```
For more awesome SQL queries, check out Digging into WordPress – Easy Comment Management via SQL or the incredible Andrea’s How to Close Comments Across a MultiSite Network.
18 April, 2011
Dig Yourself Out of a Hole – Multisite Edition
When I started my first MultiSite install, I didn’t want Blog #1 to be the main ‘dashboard’ site. The reasons why don’t matter. What does is that when you make seemingly simple changes, you have to be aware of how things work, as a whole, and be willing to take risks to fix them.

Site #1 was my test site and has since been deleted
Site #2 was a vlog which still exists
Site #3 is my ‘main’ site, the front page of it all
Site #6 is my saved Tweets
Site #7 is a documentation site
Site #8 is to save my Formspring posts

I know it’s weird. Bear with me. At first I was going to use Site #1 as an ‘all posts!’ site, like Andrea suggests you do with sitewide tags plugin, but then I realized that I wanted a splash page and then some fancy ‘Hi! Welcome!’ stuff with links to the vlog, blog, etc. Once I decided I wanted site #3 to be the ‘main’ site, I made that switch by editing two things.

First I went into Super Admin >> Options and changed my main site to #3:

Then I edited my wp-config.php file to say:
```
define('SITE_ID_CURRENT_SITE', 3);
define('BLOGID_CURRENT_SITE', '3' );
```
Now to be honest, I think that I only need one of those, but I’m totally stumped about that now. At the time, I just saw they were both set to 1 and I changed them to 3. Everything worked! Great! This was done.

Then I went to create site #4. And it didn’t show up. So I made site #5, ditto. Decided that was weird as hell, I went into phpMyAdmin and after some poking around, deleting sites and re-adding, I found the wp_blogs table and saw this:

Rationalizing that since I could see Blogs 2 and 3, and their site_id was 1, I changed it to this:

And again, things seemed to work just fine. Until I started trying to use the new iPad WordPress app. Or add anything to post to my blog. Like Formspring. Nothing worked. The blogs couldn’t be found, and third-party apps sometimes crashed. Again, I thought ‘Maybe the site_id should be 3!’ So I changed it to this:

Not only did that not work, it broke things. Quickly, I changed it back and frowned. I was obviously missing something, but what!? I started looking around for all instances of site_id and found the wp_sitemeta table! Most of the entries had site_id of 1, but some had 3.

I changed the wp_blogs table again, knowing it would break things, exported the whole wp_sitemeta table as a backup, and then ran a quick SQL search and replace, changing 1 to 3 and got this:

Now the whole site worked from front and back end, so I went to Formspring and tried to add my site. And guess what? It worked! It also magically fixed my iPad/WordPress testing woes.

So the lessons learned are “Never give up!” and “Never make changes when you don’t know what you’re doing!” Actually, no, not that second one. The lesson is to be bold with your solutions. Don’t panic and be willing to take a risk. I took backups before I started playing around, and I knew at worst, I could restore my site in 30 minutes. Because I had an escape plan that took me back to where I was, I had no fears about my changes. When you have nothing to lose, that’s when you should jump forward and try something silly!

I doubt the actual details of all this will help you, since I don’t think anyone but me was boneheaded enough to screw around like this in the first place. But seeing that someone else has seriously screwed themselves up, shot themselves in the foot, and come out feeling super smart should, I hope, encourage you to stay calm, think it through, and make a stab at something that seems right. When the wave rocks your boat, hang on and don’t be afraid to swim a bit.
20 September, 2010