🛍️ 📈 🅿️ チェックサムによるデータベースの変更の調査 👩 🏦 ✊🏼

データベースの状態を調査することは、調査テストに非常に役立ちます。そして、テスター自身が、最も熟練したプログラマーを驚かせるようなバグを見つけることができます。

私が取り組んでいるアプリケーションの非常に重要な部分は、SQL ServerとOracleを実行しているデータベースです。アプリケーションの存在の10年にわたって、標準配信でのみテーブルの数は210に増え、各ユーザーのユーザーはトリガーに囲まれ、多くのストアドプロシージャと関数が記述されました。

しかし、私にとって重要なのは、どのデータ変更がユーザーインターフェイスでの操作を引き起こすかだけです。そして、システムを研究するための私の行動のアルゴリズムは次のとおりです。

ユーザーインターフェイスの操作中に影響を受けたテーブルを特定する
個別のSQLスクリプトを作成して、データの変更を調べる
データベース内の変更と依存関係について習得した知識を記録する

この記事では、最初の段落の非常に単純な実装、変更されたテーブルの追跡に焦点を当てています。 SQL Serverの実装例は鋭くなりますが、既知のリレーショナルDBMSに同様の機能があることを考慮すると、このアプローチはOracle、MySQLなどに適用できます。

SQL Serverを使用したテーブルチェックサム計算の実装

ここでは、2つのクエリが必要です。

まず、データベース内のすべてのテーブルのフルネーム（スキーマ名+テーブル名）のリストを取得するには、次のようにします。

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

NerdDinnerデータベースを例として使用すると、次の結果が得られます。

[dbo].[Dinners] 
      

        
        
        
      

     [dbo].[RSVP]

2番目のクエリは、チェックサムを計算します（この場合、テーブル[dbo]について）。

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

結果：

1828475971

これは、テーブルの現在の状態のチェックサムです。新しいデータを削除、追加、変更すると、変更されます。そして、それが私たちに必要なものです。

しかし、今、最初のクエリでテーブルのリストを受け取った後、秒でそれぞれを置き換える必要がありますか？ -はい！しかし、手ではありません。このために、Perlで記述されたスクリプトがありました。しかし、この記事では、プログラミング言語を使用せずにやりたいと思います。

SQLを使用して必要な置換を生成するには、別のクエリのコードを生成する次のクエリを実行する必要があります。

 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

そして、NerdDinnerの場合、次の結果が得られます。

 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>

最後の「UNION」を削除し、生成されたクエリを実行すると、次のように表示されます。

[dbo].[Dinners] 1828475971 
      

        
        
        
      

     [dbo].[RSVP] 4096

.batファイルを使用してテーブルの変更の追跡を自動化する

次に、チェックサム計算を開始し、変更されたテーブルの名前のみをコンソールに表示する単純なbatファイルを作成しましょう。

必要なもの：

diff.exeコマンドをダウンロードします;必要なファイルはdiff.exe、libiconv2.dll、libintl3.dllです
SQL Server管理コマンドユーティリティ（sqlcmd.exe）がインストールされており、利用可能であることを確認します（SQL Serverと共にインストールされます）。
前のセクションで生成した各テーブルのチェックサムを計算するためのスクリプト。「 database_checksums.sql 」という名前のファイルに保存します

次に、Enterキーを押すたびに変更されたテーブルがリストされるgetchanges.batファイルを作成しましょう。

 @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

スクリプトのビデオデモ：

おわりに

私の仕事では、アプリケーションを調査するときにこのアプローチを頻繁に使用します。たとえば、ユーザーインターフェイスをクリックしてデータベースの状態を変更する間にあるC ++、C＃コード、ストアドプロシージャのヒープを理解する必要なく、状況を制御し、必要なデータを非常にすばやく見つけて追跡します。

状況によっては、データベースを監視し、以前の知識に頼って、アプリケーションのユーザーインターフェイスからは見えないバグを見つけることができましたが、生成されたレポートのデータを大きく歪めました。

利便性を高めるために、Sql Change Scannerユーティリティを作成しました。このユーティリティを使用すると、より便利なインターフェイスで変更を監視するプロセスを実行できます。

次の投稿からユーティリティの詳細を読むことができます。

Sql Change Scanner-SQL Serverテーブルの変更を追跡するためのユーティリティ

Githubでプログラムをダウンロードできます。

https://github.com/dzhariy/SqlChangeScanner/downloads

リバースエンジニアリングをお楽しみください。

ドミトリー・ザリー

チェックサムによるデータベースの変更の調査

SQL Serverを使用したテーブルチェックサム計算の実装

.batファイルを使用してテーブルの変更の追跡を自動化する

おわりに

More articles: