The Magento community has been putting quite an effort in securing the Magento landscape, something i’m glad to see. I have found that there is no real solution yet on scanning your database efficiently, Although we’re working on a pretty awesome database solution, i would like to share my current setup when it comes to Magento Database malware scanning.

Note: This post is a draft, the final gist will be appended later and the text revised

Requirements

I’m a big fan of YARA, this mostly came with the Magento-malware-scanner. I have my own sets of Regexps to identify certain strains of malware, and mostly are YARA compatible. So for this i’ll be using Regexps, partly based on the Regexps in the magento-malware-scanner and partly my own. Besides that i scripted out much of the process in bash, so if your in windows, you probably would like something like docker or vagrant.

The Problem of identification

Database malware is mostly in the form of javascript. There are certain strains in PHP form that can hide inside the database. I have regexps prepared for those. These strains are mostly related to the vulnerability that was addressed in SUPEE-8788.

The problem with javascript is that it always or mostly looks malicious. If you take a look at tracking scripts or those form social providers, they are sketchy but legit. Due to this, you will have either false flags but be thorough, or miss out on a lot without false flags. When it comes to magento security, i rather have some false flags to check out than miss a strain and credit card details continue to leak. Having a convenient way to review those is what this setup is all about. So i want to accomplish the following:

Have a setup that is complete
Easily adjustable
Thorough on detection
A pleasant way to review

Like Magento itself advices, it so export the database and run a scan.

yara -r malware-rules.yar databasefiles

And we are going to do this, kind of. We’ll be using grep instead due to it’s options. Let’s get started.

1. Getting your files ready

First we need to export our database. But not in one big file since you’ll lose your overview. I want to Seperate each table and row. This gives you a way to easily review and remove malicious things. We first need a list of all tables in our designated database. The following SQL gives you an overview of tables.

SELECT TABLE_NAME AS "Table"
FROM information_schema.TABLES
WHERE table_schema = 'database_name'

Then this bash function outputs all these tables into a txt file.

function getTables(){
    DBS="$( IFS=' ' mysql -u$MYSQL_USER < "${dir}/cmd.sql" )"
    echo $DBS > "tables.txt"
    exit 0
  while  IFS=' ' read -r DB <<< "$DBS" ; do
        for i in "${DB}"; do
            echo -e "${i}"
        done
    done
}

and since i’m lazy and had some trouble getting them all on a new line a used this php file.

#!/usr/bin/env php
<?php
$t = file_get_contents('tables.txt');
$tables = str_replace(' ',PHP_EOL,$t);
file_put_contents('tables.txt',$tables);

You’ll end up with a txt file of all table names. Then export them all to seperate files using the following bash function.

function exportForScan(){
    total=$(cat $tablesFile | wc -l | tr -s ' ')

    if [ ! -d  $scanDir ]; then mkdir -p $scanDir; fi
    COUNTER=0
    cat $tablesFile | while read -r line ; do
        COUNTER=$((COUNTER+1))
        outfile="${scanDir}/${line}.sql"
        echo -e "- Exporting ${total}/${COUNTER} ${line} to ${outfile}"
        mysqldump -fnty --extended-insert=FALSE --lock-all-tables --log-error=log.txt -u root $dbname $line > $outfile
    done
}

These parameters make sure that each row in a database table is on a new line. This allows us to register the line nr. while scanning and only output that line for review.

After these are outputted, it’s time to scan. I run scans using the following bash function.

function runScan(){

    outDir="out/${DTD}"
    outCsv="${outDir}/report.csv"

    if [ ! -d $outDir ]; then mkdir -p $outDir; fi
    if [ ! -d $outCsv ]; then touch $outCsv; fi

    # Echo columns
    echo -e "\"#\", \"table\", \"source\", \"sample\" " >> ${outCsv}

    COUNTER=0
    zgrep -HnrE --context=0 --color=never --file=${patternsFile} ${target}  | while IFS=':' read -a match ; do

        # Increment counter
        COUNTER=$((COUNTER+1))

        # Gather variables
        local file="$(basename ${match[0]})"
        local tableName=`echo "${file}" | cut -d'.' -f1`
        local lineNr="${match[1]}"
        local line="${match[@]:2}"

        # Create location
        local storageDir="${outDir}/${tableName}"
        local storageFile="${storageDir}/${lineNr}.sql"
        createDirs ${storageDir}
        echo -e ${line} >> ${storageFile}

        # add info to CSV
        echo -e "\"${COUNTER}\", \"${tableName}\", \"${file}:${lineNr}\", \"${storageFile}\"" >> $outCsv

        # output info
        echo - "#${COUNTER} - ${tableName} ${file}:${lineNr}"

    done

}

What this function does is the following:

Set & Create directory’s
Create a CSV file with headers
Run grep matching using a patterns.txt (contains regexps on new lines)
Write each match to the CSV
Write each match to a separate sql file (row containing malware)

This allows me to cross-reference entries form the CSV file to separate SQL files containing the potentially infected row which allows me to refer it back in the database. Since reporting these kind of incidents is very important regarding data protection law, this allows me to easily turn it into a report and store the malicious strains for administration purposes.

Note: This was an old draft post from 2017 and published to share the info present, it is not complete

Fabio Ros

How to efficiently scan your database for malware (Draft)

Requirements

The Problem of identification

1. Getting your files ready

Related Posts