Notes

Update WordPress posts for SSL https

Edit on GitHub

Databases
2 minutes

tl;dr

 1# UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, '', '')
 2
 3# Update self-hosted embeds (images, iframes, scripts, etc.)
 4UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'http://yoursite.com', 'https://yoursite.com');
 5UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'http://www.yoursite.com', 'https://www.yoursite.com');
 6
 7# Update internal pingbacks
 8UPDATE `wp_comments` SET `comment_author_url` = REPLACE(comment_author_url, 'http://yoursite.com', 'https://yoursite.com');
 9UPDATE `wp_comments` SET `comment_author_url` = REPLACE(comment_author_url, 'http://www.yoursite.com', 'https://www.yoursite.com');
10
11# Update YouTube embeds
12UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'http://www.youtube.com', 'https://www.youtube.com');
13UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'http://img.youtube.com', 'https://img.youtube.com');
14
15# Update Vimeo embeds
16UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'http://player.vimeo.com/', 'https://player.vimeo.com/');
17
18# Update Slideshare embeds
19UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'http://www.slideshare.net', 'https://www.slideshare.net');

Why?

  • You have installed an SSL cert and force all URLs to https, yet the lock on your WordPress site’s URL has still not turned green
  • Your post’s media (uploads, images etc.) and embedded content (YouTube, Vimeo etc.) still links to http (insecure) endpoints (links).

How

  • The post content for your posts is in the post_content column of the wp_posts table
  • The simplest way to upload the links is to run a REPLACE function and change http with https
  • You can find out which links are insecure/blocked in your browser’s Developer Console (the Mixed Content: warnings..)

Find all posts linking to insecure URLs

1SELECT * FROM `wp_posts` where `post_content` LIKE "%http://%"

While you might be tempted to just update ALL links in posts to https in one go with the above command, this can cause issues. Not all sites (unfortunately) have shifted to https yet and if you update all posts links, you’ll get plenty of broken ones. Best approach is to update the links for known sites that you know for a fact use https (Sites like YouTube, Vimeo, Twitter as well your own site)

This means anything in the wp-content/uploads directory

1# Update Media links
2UPDATE `wp_posts` SET `post_content`=REPLACE(post_content, 'http://amiranzur.com/wp-content/uploads', 'https://amiranzur.com/wp-content/uploads')

Update video emeds

1# Update YouTube links
2UPDATE `wp_posts` SET `post_content`=REPLACE(post_content, 'http://youtube.com/', 'https://youtube.com/')
3
4# Update Vimeo links
5UPDATE `wp_posts` SET `post_content`=REPLACE(post_content, 'http://player.vimeo.com/', 'https://player.vimeo.com/')
Browse all topics