Fix special characters in filenames on FS and in WP DB

.sh

hostname=
username=
database=
password=


cd /var/www/html/wp-content/uploads

export MYSQL_PWD=$password
MYSQL_CONN="mysql -B -h$hostname -u$username $database --disable-column-names"

NUM_SPEC_CHARS_FS_1=$(find -name '*%*' | grep -o '%..' | sort | uniq | wc -l);
NUM_SPEC_FILES_FS_1=$(find -name '*%*' | grep '%..' | wc -l);
NUM_SPEC_FILES_WP_1=$($MYSQL_CONN -e "SELECT COUNT(meta_value) FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value REGEXP '%[[:alnum:]]{2}';");
NUM_SPEC_ROWS_WP_1=$($MYSQL_CONN -e "SELECT COUNT(meta_value) FROM wp_postmeta WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%') > 0;");
START_TIME=$(date);

# rename files on filesystem to change %** to three underscores
sudo su
find /var/www/html/wp-content/uploads -name '*%*' -exec rename 's/%../___/g' "{}" \;

mysql -h$hostname -u$username $database

# allow rollback
START TRANSACTION;
# change all special characters to three underscores
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%21', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%21') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%23', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%23') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%24', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%24') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%25', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%25') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%26', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%26') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%27', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%27') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%28', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%28') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%29', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%29') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%2A', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%2A') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%2B', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%2B') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%2C', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%2C') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%3A', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%3A') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%3B', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%3B') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%3D', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%3D') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%40', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%40') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%5B', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%5B') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%5D', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%5D') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%80', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%80') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%82', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%82') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%83', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%83') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%8C', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%8C') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%93', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%93') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%94', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%94') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%97', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%97') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%99', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%99') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%9C', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%9C') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%9D', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%9D') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%A2', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%A2') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%A9', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%A9') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%C2', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%C2') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%C3', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%C3') > 0;
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value, '%E2', '___') WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%E2') > 0;
# confirm no special characters remain 
SELECT COUNT(meta_value) FROM wp_postmeta WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%') > 0;
# save changes
# COMMIT;
# undo changes
# ROLLBACK;
exit;


NUM_SPEC_CHARS_FS_2=$(find -name '*%*' | grep -o '%..' | sort | uniq | wc -l);
NUM_SPEC_FILES_FS_2=$(find -name '*%*' | grep '%..' | wc -l);
NUM_SPEC_FILES_WP_2=$($MYSQL_CONN -e "SELECT COUNT(meta_value) FROM wp_postmeta WHERE meta_key = '_wp_attached_file' AND meta_value REGEXP '%[[:alnum:]]{2}';");
NUM_SPEC_ROWS_WP_2=$($MYSQL_CONN -e "SELECT COUNT(meta_value) FROM wp_postmeta WHERE ( meta_key = '_wp_attached_file' OR meta_key = '_wp_attachment_metadata' ) AND INSTR(meta_value, '%') > 0;");
END_TIME=$(date);


echo "Before:";
echo "On filesystem, found $NUM_SPEC_CHARS_FS_1 distinct character codes among $NUM_SPEC_FILES_FS_1 files.";
echo "In WordPress, found $NUM_SPEC_FILES_WP_1 files with special characters.";
echo "In WordPress, found $NUM_SPEC_ROWS_WP_1 rows with special characters, which should be roughly double $NUM_SPEC_FILES_WP_1.";

echo "After:";
echo "On filesystem, found $NUM_SPEC_CHARS_FS_2 distinct character codes among $NUM_SPEC_FILES_FS_2 files.";
echo "In WordPress, found $NUM_SPEC_FILES_WP_2 files with special characters.";
echo "In WordPress, found $NUM_SPEC_ROWS_WP_2 rows with special characters, which should be roughly double $NUM_SPEC_FILES_WP_2.";

echo "Process started at $START_TIME and finished at $END_TIME.";