Mar
17
2016

Query WordPress Database for Invalid Media Filenames

WordPress for some reason didn’t use to filter out invalid file names on upload, causing some interesting issues with charsets and rendering (specifically images not loading in browsers).  Using the following queries, you can easily find those items with invalid filenames, and their associated posts (which unfortunately means you’ll still have to replace them)…

SELECT ID, post_title, post_content, post_parent, guid FROM wp_posts WHERE post_type = 'attachment' AND `post_status` = 'inherit' AND (guid LIKE '%.jpg' OR guid LIKE '%.gif' OR guid LIKE '%.png' OR guid LIKE '%.jpeg' OR guid LIKE '%.tiff' OR guid LIKE '%.tif' OR guid LIKE '%.mov' OR guid LIKE '%.mp4') AND guid REGEXP '[^ -~@]';
SELECT CONCAT(post_parent) AS listed FROM wp_posts WHERE post_type = 'attachment' AND `post_status` = 'inherit' AND (guid LIKE '%.jpg' OR guid LIKE '%.gif' OR guid LIKE '%.png' OR guid LIKE '%.jpeg' OR guid LIKE '%.tiff' OR guid LIKE '%.tif' OR guid LIKE '%.mov' OR guid LIKE '%.mp4') AND guid REGEXP '[^ -~@]' GROUP BY post_parent;
SELECT post_title, post_date FROM wp_posts WHERE ID IN(LIST OF POSTS FROM ABOVE) ORDER BY post_date DESC;

Leave a comment