So your database has malware and you want to clean it up. You head to phpMyAdmin and try out search and replace but can’t figure out what type of regex engine does MySQL support?
Well long story short, we recently came across a site that had 2814 database infections. Yep, you read that right. And the database dump was 114 MB.
Table of Contents
Most Text Editors Don’t Support Opening File That Size
I have VSCode installed and it took a while to open up the file. When it came to search, it did work but regexes? No way!
PHP PREG_REPLACE Runs Out of Wind on Such Large Strings
There’s no way PHP can just handle regex-replace on such a large string.
Solution: PHP Regex Replace Line-by-Line
Malcure Malware Scanner and Malcure Advanced Edition run a complete website scan. It detects malware and infections in your WordPress installation files as well as database. The list of database infections detected by the plugin clearly specifies the kind of infection and the respective post ID / postmeta ID / options ID of the infected record. This helps in easily finding the record and cleaning the infection. While this approach works effectively when there are a limited number of database infections, with a huge number like 2814 infections, it becomes a challenge.
Here’s a script that I came up with after numerous failed attempts meddling with phpMyAdmin, text editors, etc.
Oh and don’t run it from a Windows system or even WSL. Pls just don’t even bother asking why.
Here’s the script that I coded and used to remove malware from the database.
Regex-replace line-by-line in PHP.
<?php
ini_set( 'pcre.backtrack_limit', '50000000000' );
ini_set( 'pcre.recursion_limit', '50000000000' );
$inputfile = fopen( 'inputfile.sql', 'r' ); // replace this before running
$outputfile = fopen( 'outputfile.sql', 'w' ); // replace this before running
$regex = '/myregex/s';
$lines = 0;
if ( $inputfile ) {
while ( ( $buffer = fgets( $inputfile ) ) !== false ) {
$lines ++;
echo 'Line:' . $lines . PHP_EOL;
$c = preg_replace( $regex, '', $buffer, -1, $count );
fwrite( $outputfile, $c );
}
if ( ! feof( $inputfile ) ) {
echo "Error: unexpected fgets() fail\n";
}
fclose( $inputfile );
fclose( $outputfile );
echo "Done!\n";
} else {
echo "Input issue!\n";
}