Fixing broken content images on a HTTPS Drupal site
After switching my site over to HTTPs and implementing a Progressive Web App, I was doing some browsing, impressed at how much snappier it feels. Until I ran into an issue: the images were missing from several of my articles! Instead of showing up, or even being missing, an empty grey box was showing in their place:
A quick inspection reveals that it’s because they’re requesting http, not https:
This should be a quick and easy SQL query to fix in the page contents for my articles. The issue is that I have some images defined with absolute URLs (http://example.com/example/example.jpg) hard-coded in my articles. Now that the site is HTTPs, the browser won't load them as a security precaution. Instead of changing these to https, I’ll update these all to root-relative URLs instead of absolute URLs so that I don’t have to worry about specific protocols or domains. (/example/example.jpg)
To do this I open up my SQL database tool and run the following query. It searches for any articles with body content containing the the absolute URL I specified, and replaces my domain name with a root-relative leading slash instead.
update `field_data_body` SET `body_value` = REPLACE(`body_value`, 'http://example.com/', '/') WHERE INSTR(`body_value`, 'http://example.com/') > 0;
There were only 7 records updated, so I didn't have very many instances of this problem. The nice thing about this query is that it also fixes not just images, but also any links that also are pointing to the HTTP version of the URL.
After running the query, all I have to do is flush the Drupal site-wide cache, and the images show up in my articles again: