DB Diff Tool – Database Table Difference Search Comparison Tool

[AITpro Admin]

DB Diff Tool

The DB Diff Tool compares old database tables from DB backups to current database tables and displays any differences in the data/content of those 2 database tables. The DB Diff Tool allows you to check your WordPress Database if you receive a DB Monitor email alert and do not recognize the database table name change/modification. The DB Monitor email alert contains an attached zip file of your DB Monitor Log file. In that attached log file you will see the database name that was changed/modified. Example: DB Table Name: xxxxxx_usermeta. You would enter a DB Backup file name and the DB Table name in the DB Diff Tool Form to compare/check exactly what was changed/modified and click the Run Diff Comparison button to get search comparison results for exactly what was changed/modified in that particular database table. You can of course check your DB Monitor Log file directly by going to the DB Monitor Log tab page.

What if the DB Diff Tool finds malicious code or I do not understand or am unsure about the search comparison results?
Remain calm. Most likely the change/modification to your database is legitimate and is NOT something malicious. If you unsure about the search comparison results then post the search comparison results from box 1 and 3 in this Forum Topic: https://forum.ait-pro.com/forums/topic/db-diff-tool/ and we will let you know what the search comparison results mean. If the change/modification to your database table is malicious then you have the advantage. The hacker does not know that you have been alerted by the BPS Pro DB Monitor IDS. This Forum Topic: https://forum.ait-pro.com/forums/topic/db-diff-tool/ contains step by step instructions on what steps you need to do. Remain calm. Most likely the change/modification to your database is legitimate and is NOT something malicious.

If the DB Diff Tool has detected that malicious code has been added to a database table do these steps:
Remain calm. Most likely the change/modification to your database is legitimate and is NOT something malicious. If the change/modification to your database table was done by a hacker and the code is definitely malicious code then follow these steps:

1.  Put your site in frontend and backend Maintenance Mode or take it offline any other way that you prefer.
2. Change your FTP password.
3. Turn Off AutoRestore.
4. Change your WordPress database password in 2 places:
– Log into your host control panel and use phpMyAdmin to change your database password.
– Use your host’s FTP File Manager (or regular FTP) and change your database password in your wp-config.php file using the same new database password you used in phpMyAdmin.
5. Go to AutoRestore and click the Root Files Backup Files button and turn AutoRestore back On.
6. Using phpMyAdmin edit the database table where the malicious code was found and delete that malicious code.

Small Data/File Comparison Tool – Limitations
Max Limitation: 500 DB Rows x 4 = 2000 lines of content/data (sql dump format) compared. See the Large Data/File Comparison Tool – Limitations, Steps & Information help section for limitations and steps for how to use the Large Data/File Comparison Tool.

How to read and understand the search comparison results
Two files are created for comparison when you enter the DB Backup file name and the DB Table name that you want to compare for differences in those two files. What is being compared for any differences is an older DB Table to your website’s current DB Table. The DB Diff Tool takes the contents of the 2 files to compare and puts them into [key] => value Arrays for comparison. This basically creates a “Table of Contents”. The keys [1] are the line numbers of the contents of the files and the values are what has changed on that line number of the files.

1. Current DB Table Difference (This is what has changed). This search results box shows the results of comparing the two files for any differences. This is the contents of your current DB Table since the contents of this file are your website’s current DB Table name that you chose in the DB Diff Tool Form.

3. Previous DB Table Difference (This is what existed previously). This search results box shows the results of comparing the two files for any differences. This is the contents of your old DB Table since the contents of this file are your old DB Table name that is extracted from the DB Backup file name that you chose in the DB Diff Tool Form.

2. Current DB Table Diff File. This search results box shows the entire contents of the DB Table name that you entered in the DB Diff Tool Form. This is your website’s current DB Table contents for the DB Table name that you entered in the DB Diff Tool Form.

4. Previous DB Table Diff File. This search results box shows the entire contents of the DB Table name that you entered in the DB Diff Tool Form. This is your website’s older DB Table contents for the DB Table name that you entered in the DB Diff Tool Form. This file is created by extracting your old DB Table name from the DB Backup file name that you entered in the DB Diff Tool Form.

Example Usage:
Let’s say I received a DB Monitor email alert that a database change has occurred and I want to use the DB Diff Tool to find out exactly what was changed/modified in my database. The example DB Table name that was changed/modified in the DB Monitor email alert is: 74ibuq_options.

For simplicity sake I will refer to the search results boxes as search box 1, search box 2, etc. I will use an example search result to make this easier to understand. I have made 1 database change and changed 1 value for this example. I have changed the avatar_rating value from G to X in my example database.

I enter a DB Backup file name and the DB Table name: 74ibuq_options in the DB Diff Tool Form and click the Run Diff Comparison button to compare an older DB backup file to my current website’s database.

In search box 1 I have this search result: [130] => VALUES ( 61, ‘avatar_rating’, ‘X’, ‘yes’ ); This is the new database table value that has been changed/modified.
In search box 3 I have this search result: [130] => VALUES ( 61, ‘avatar_rating’, ‘G’, ‘yes’ ); This is the old database table value that existed previously.

Now to cross reference which DB fields/columns go with these values (you do not necessarily need to cross reference values, but it is good to understand the full intended usage for all search boxes (1,2,3,4):

In search box 2 I will look for key [130] or line number [130] and if I look at line number [129] it shows me the DB Table name and the fields/columns that go with these values.
[129] => INSERT INTO 74ibuq_options ( option_id, option_name, option_value, autoload )
[130] => VALUES ( 61, ‘avatar_rating’, ‘X’, ‘yes’ );

The DB Table name is: 74ibuq_options
The fields/columns are: option_id, option_name, option_value, autoload
The values that are entered into the fields/columns are: 61, ‘avatar_rating’, ‘X’, ‘yes’
The value that goes with field/column “option_value” is X.

In search box 4 I will look for key [130] or line number [130] and if I look at line number [129] it shows me the DB Table name and the fields/columns that go with these values.
[129] => INSERT INTO 74ibuq_options ( option_id, option_name, option_value, autoload )
[130] => VALUES ( 61, ‘avatar_rating’, ‘G’, ‘yes’ );

Large Data/File Comparison Tool – Limitations, Steps & Information

Max Limitations Testing (PHP, Server, Memory, etc.):
Overall each line of content/data contained very little data.
25,000 x 2 = 50,000 lines of data/content compared failed. Server ran out of available memory and crashed.
20,000 x 2 = 40,000 lines of data/content compared was successful. Server was able to process and compare the data/content successfully.

Max Safe Limits:
Large testing Database Table used in Safe Max Limit testing. Overall each line of content/data contained a large amount of data. No Server memory issues or other problems occurred after extensively stress testing comparing this amount of content/data in numerous/various scenarios and conditions.
PHP Configuration Memory Limit: 128M
Actual large testing DB Table Size: 7.33 MB / 7,506 KB
Dump/Extraction DB Table Size in sql Format: 8.2MB
Total content/data compared: 16.4MB (8.2MB x 2 of content/data in each text area box)
Total lines of content/data compared: 24,174 lines of content/data (12,087 x 2 lines of content/data in each text area box)

Notes:
– You are comparing a current Database Table to an older Database Table and NOT comparing an entire Database. A Database is made up of several Database Tables within that Database.
– It can take several seconds for a Paste to complete when Copying and Pasting a large amount of data/content into the text area boxes.
– It took 15 seconds for a Paste to complete when Copying and Pasting 8.2MB of data/content into a text area box.
– It took 36 seconds to process & compare: 16.4MB of content/data (8.2MB x 2 of content/data in each text area box) & 24,174 lines of content/data total (12,087 x 2 lines of content/data in each text area box).
– You can make edits to refine the data/content in each text area box if needed before clicking the Run Large Diff Comparison button so that the format/structure matches. In order to compare the content/data successfully, the start and end points of the content/data must match exactly.
– The data/content will remain in the text area boxes after clicking the Run Large Diff Comparison button. You can make edits to the content/data in the text area boxes and run another comparison. If you leave the main DB Monitor page the data/content will no longer be in the text area boxes and you will have to copy and paste new data/content into the text area boxes again.
– This tool could be used to compare other data, but that format/structure of the data/content must match and the data/content would have to prepped first and have \r\n carriage returns/newlines/line breaks added to all the lines of content/data to compare before attempting to compare that data/content. You can use Notepad++ to do that prep work on the data/content and insert/replace line breaks/newlines/carriage returns: https://stackoverflow.com/questions/10668044/how-to-break-lines-at-a-specific-character-in-notepad
– The DB Status & Info page Show Table Status/Size tool displays the total size and rows of each Database Table in your database. The number of Rows x 2 in each DB Table will be the number of lines of content/data that is compared in each text area box. The total number of lines of content/data being compared in both text area boxes would be Rows x 4.

Large Data/File Comparison Tool Steps
1. Run the Small to Medium Data/File Comparison tool first to create your files. The Small to Medium Data/File Comparison tool comparison will fail if you are trying to compare too much data/content at one time, but a necessary file is created that you can use to compare data/content in the Large Data/File Comparison tool.
2. Diff Files are created in this folder: /wp-content/bps-backup/backups_xxxxxxxxx/db-diff/. Download the Diff files from this folder.

Example Diff file name and zip backup file to download:
xxxxx_some_table_name-current.sql – This file contains your current database table that you entered in the “DB Table name” text box in the Small to Medium Data/File Comparison tool form. Download the zip backup file (if you download and save your zip backup files regularly to your computer (recommended) then use that zip file that you already previously downloaded) for the zip backup file name that you entered in the “DB Backup file name” text box in the Small to Medium Data/File Comparison tool form.

3. Download and install the Notepad++ free application on your computer: http://notepad-plus-plus.org/download/. It is a plain text and code editor application that shows line numbers, which makes it easy for you to see how much content/data is in each file that you open in Notepad++, line by line. Open the xxxxx_some_table_name-current.sql file and your zip backup [DB Name].sql file in Notepad++.

4. Copy and Paste your data/content to compare into each text area box.
– If you are comparing an entire current DB Table that is 12,000 lines or less – Copy from BEGIN Table xxxxx_some_table_name to END Table xxxxx_some_table_name from the xxxxx_some_table_name-current.sql file and paste it into text area box 1.
– If you are comparing an entire old DB Table that is 12,000 lines or less – Copy from BEGIN Table xxxxx_some_table_name to END Table xxxxx_some_table_name from the zip backup [DB Name].sql file and paste it into text area box 2.
– If you are comparing more than 12,000 lines of content/data per text area box then if you use Notepad++ you can edit your downloaded files and add a placeholder at 12,000 lines of content/data so that after comparing the first 12,000 lines of content/data from each file you can then go back to your placeholder and copy lines 12,001 to lines 24,000 into the text area boxes to compare that data/content. You would do this for both your current database table file and your old database dump file. An average database table is typically going to have much less than 12,000 lines of content/data.

5. Click the Run Large Diff Comparison button.

• This topic was modified 2 years, 4 months ago by AITpro Admin.
• This topic was modified 2 years, 4 months ago by AITpro Admin.
• This topic was modified 2 years, 4 months ago by AITpro Admin.
• This topic was modified 2 years, 4 months ago by AITpro Admin.
• This topic was modified 2 years, 4 months ago by AITpro Admin.
• This topic was modified 2 years, 4 months ago by AITpro Admin.

[Author]

Posts