php - Update all blob columns in all tables in MySQL

We are migrating a wiki system from MediaWiki v1.25 to v1.35 while using a cloned database in the same MySQL server, but in a new Apache server. The issue is that, after doing all the steps mentioned here and here, all the BLOB columns that have a UTF-8 encoded character like ??, ?�, ?� got somehow polluted with the UTF-8 encoded character U+0083 just before the afromentioned characters. Fortunatelly, we found out that it can be solved by doing the following UPDATE.

UPDATE clone_wiki_db.some_table 
SET blob_col = REPLACE(blob_col, CONCAT(CHAR(0x83), CHAR(0xC2)), '');

The problem here is that there are a lot of BLOB columns in the DB. Is there a way to mass UPDATE all BLOB columns in the cloned DB? Is there a better approach to do this "cleaning"?

Here are some details of both systems:

Legacy system (no issues here)

• MediaWiki 1.25
• MySQL 5.6.29
  • Original DB, not altered in any way.
• PHP 5.4.45
• CentOS 5

New system (in test stage, with issues)

• MediaWiki 1.35
• MySQL 5.6.29 (will be updated later)
  • Cloned DB
  • Same charset as original
• PHP 7.4.30
• CentOS 8 Stream

Solution:

Self answering if anyone falls in the same situation. Based in this answer, I used a stored procedure, a cursor and a prepared statement to do the job. First, I retrieve all the columns in the DB with a BLOB, TINYBLOB or VARBINARY datatype along with the respective table. Then, by using a CURSOR, I fetch every row and build a dynamic UPDATE string in order to execute it by using a prepared statement/dynamic SQL.

DELIMITER //

DROP PROCEDURE IF EXISTS BLOB_CLEANER//
CREATE PROCEDURE BLOB_CLEANER() BEGIN
    DECLARE var_final INTEGER DEFAULT 0;
    DECLARE var_column VARCHAR(25);
    DECLARE var_table VARCHAR(25);
    
    DECLARE cursor1 CURSOR FOR 
    SELECT  TABLE_NAME,
            COLUMN_NAME
    FROM    INFORMATION_SCHEMA.columns 
    WHERE   TABLE_SCHEMA = 'clone_wiki_db' 
    AND     DATA_TYPE IN ('TINYBLOB', 'BLOB', 'VARBINARY');    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_final = 1;
    
    OPEN cursor1;
    
    bucle: LOOP
        FETCH cursor1 
        INTO  var_table, 
              var_column;

        IF (var_final = 1) THEN
          LEAVE bucle;
        END IF;
                                
        SET @sql = CONCAT(
            'UPDATE ',
            var_table,
            ' SET ',
            var_column,
            ' = REPLACE(',
            var_column,
            ', CONCAT(CHAR(0x83), CHAR(0xC2)), '''')'
        );
                            
        PREPARE stmt
        FROM    @sql;
        
        EXECUTE stmt;
            
        DEALLOCATE PREPARE stmt;
    END LOOP bucle;
    
    CLOSE cursor1;    
END//

DELIMITER ;

After executing the SP, I changed the charset of the DB from latin1 to utf8_swedish_ci.

ALTER SCHEMA `clone_wiki_db` DEFAULT COLLATE utf8_swedish_ci;

Finally, restarted the Apache service.

As always, be cautious when using dynamic SQL (or a prepared statements like this) in order to avoid a SQL injection.