Examining database changes through checksums

The study of the state of the database greatly helps in exploratory testing. And the tester himself can find a bug that may surprise the most experienced programmers.

A very significant part of the application I'm working on is Database running under SQL Server and Oracle. Over the 10 years of the application itself, the number of tables has grown to 210 only in the standard package, each user has overloaded with triggers, a lot of stored procedures and functions have been written.

But for me it is only important what changes in the data provoke my manipulations with the user interface. And the algorithm of my actions on system research is as follows:

1. Determine which tables were touched during user interface manipulations.
2. Investigate data changes by creating separate SQL scripts
3. Write knowledge of changes and dependencies in the database

This article is devoted to a very simple implementation of the first paragraph, tracking the modified tables. Examples of implementation will be sharpened for SQL Server, but considering that similar functionality exists in any known relational DBMS - this approach can be applied to Oracle, MySQL, etc.
')

Implementing the calculation of table checksums using SQL Server

Here we need two queries:
First, in order to get a list of full names (schema name + table name) of all tables in the database:

SELECT '[' + sys.schemas.name + '].[' + sys.Tables.name + ']' AS TABLEFULLNAME FROM sys.Tables JOIN sys.schemas ON sys.Tables.schema_id = sys.schemas.schema_id ORDER BY sys.schemas.name, sys.Tables.name 

Using the NerdDinner base as an example , we get the following result:
[dbo].[Dinners]
[dbo].[RSVP]


The second query will calculate the checksum, in this case for the table [dbo]. [Dinners]

 SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM [dbo].[Dinners] WITH (NOLOCK) 

Result:
1828475971

This is the checksum of the current state of the table. When deleting, adding, changing new data - it will change. And this is what we need.

But, really now, after getting the list of tables in the first query, it is necessary to substitute each in the second hands? - Yes! But not with your hands. For this, I had a Perl script written. But, in this article I want to do without the use of programming languages.

In order to generate the necessary substitutions using SQL tools, it is necessary to execute the following query, which will generate the code of another query:

 SELECT 'SELECT ''[' + sys.schemas.name + '].[' + sys.Tables.name + ']'' AS TABLENAME, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHECKSUM FROM [' + sys.schemas.name + '].[' + sys.Tables.name + '] WITH (NOLOCK) UNION' AS SCRIPT FROM sys.Tables JOIN sys.schemas ON sys.Tables.schema_id = sys.schemas.schema_id ORDER BY sys.schemas.name, sys.Tables.name 

And in the case of NerdDinner, we get the following result:

 SELECT '[dbo].[Dinners]' AS TABLENAME, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHECKSUM FROM [dbo].[Dinners] WITH (NOLOCK) UNION SELECT '[dbo].[RSVP]' AS TABLENAME, CHECKSUM_AGG(BINARY_CHECKSUM(*)) AS CHECKSUM FROM [dbo].[RSVP] WITH (NOLOCK) <s>UNION</s> 

Now delete the last “UNION”, and run the generated query, as a result it will show the following:
[dbo].[Dinners] 1828475971
[dbo].[RSVP] 4096


Automate tracking of table changes using a .bat file

Now let's create a simple bat file that runs the checksum calculation and outputs only the names of the changed tables to the console.

What we need:

1. Download the diff.exe command, the following files are needed: diff.exe, libiconv2.dll, libintl3.dll
2. Make sure that the SQL Server Management Command Utility (sqlcmd.exe) is installed and available (installed with SQL Server).
3. The script for calculating checksums for each table that we generated in the previous section. Save it to a file as " database_checksums.sql "

Now let's create a file getchanges.bat , which, with each press of Enter, will display a list of changed tables:

 @echo off set SQL_SERVER_HOST=dz\SQL2008 set SQL_USER=admin set SQL_USER_PASSWORD=admin set SQL_DATABASE=NerdDinner set CHECKSUM_SCRIPT=database_checksums.sql REM Clenup echo. > left.txt echo. > right.txt :HOME sqlcmd.exe -U %SQL_USER% -P %SQL_USER_PASSWORD% -S %SQL_SERVER_HOST% -d %SQL_DATABASE% -i %CHECKSUM_SCRIPT% -o left.txt diff.exe --side-by-side --suppress-common-lines left.txt right.txt > diff-result.txt type diff-result.txt copy left.txt right.txt /y > nul pause GOTO HOME 

Video demonstration of the script:

Conclusion

In my work, I very often use this approach in research testing an application. For example, without having to understand the pile of code in C ++, C # and stored procedures, which lies between my click on the user interface and changing the state of the database, I keep the situation under control and very quickly find and track the data I need.
In some situations, by monitoring the base, and relying on the previous knowledge gained, I managed to find bugs that were not visible through the user interface of the application, but strongly distorted the data in the generated reports.

For more convenience, I wrote the Sql Change Scanner utility, which allows you to monitor changes through a more convenient interface.
For more information about the utility, you can read from the following post:
Sql Change Scanner - utility for tracking changes in SQL Server tables
You can download the program on Github:
https://github.com/dzhariy/SqlChangeScanner/downloads

Enjoy your reverse engineering,
Dmitry Zhariy