Python 获取 SQL 指纹和 HASH 值

前言

本文介绍一个提取 SQL 指纹的方法，就是将 SQL 语句的条件转换为 ？可用于脱敏和 SQL 聚类分析的场景。

1. 工具安装

这里用到的工具，就是 pt 工具集中的 pt-fingerprint 含在 Percona Toolkit 中，安装方法可参考 Percona Toolkit Install 这篇文章的 1.1 小节。

2. 实验案例

测试一个简单的 SQL：

pt-fingerprint --query "select a, b, c from users where id = 500"

输出：
select a, b, c from users where id = ?

复杂的长 SQL 建议使用文件的方式，否则会出现一些预期之外的问题，另外 SQL 中不能包含 # – 这些注释符号。

pt-fingerprint select.sql

3. Python 组合分析

代码中的 command_bin_path 就是 pt-fingerprint 的路径，可使用 which 命令查看，

# -*- encoding: utf-8 -*-
import os
import uuid
import time
import hashlib
import subprocess# which pt-fingerprint
command_bin_path = "/usr/local/bin/pt-fingerprint"def get_sql_hash(sql_query: str):sql_info = ' '.join(sql_query.split())sql_hash = hashlib.md5(sql_info.encode()).hexdigest()return sql_hashdef exec_dos_command(command):"""Execute system commands."""process = subprocess.Popen(command, shell=True, stdout=subprocess.PIPE,stderr=subprocess.STDOUT)content = process.stdout.read()process.communicate()if process.returncode != 0:print('Program Error: {0}'.format(command))print(content)sys.exit(0)else:return content.decode()def get_sql_fingerprint(sql_text):"""输出 SQL 语句，返回指纹化的 SQL 语句和 SQL HASH"""timestamp_ns = time.time_ns()uuid_str = str(uuid.uuid4())[:16]file_name = f"{timestamp_ns}_{uuid_str}" + '.sql'with open(file_name, 'w') as w1:w1.write(sql_text)dos_command = command_bin_path + ' ' + file_namecontent = exec_dos_command(dos_command)sql_hash = get_sql_hash(content)os.remove(file_name)return content, sql_hashsql1 = "select * from tb_user where id = 10;"
sql2 = "select * from tb_user where id = 11;"
sql3 = "select * from tb_user where id = 13;"
sql4 = "select * from tb_user where id = 14;"
sql5 = "select * from tb_user where id = 1576;"
sql6 = "select * from tb_user where id = 19;"print(get_sql_fingerprint(sql1))
print(get_sql_fingerprint(sql2))
print(get_sql_fingerprint(sql3))
print(get_sql_fingerprint(sql4))
print(get_sql_fingerprint(sql5))
print(get_sql_fingerprint(sql6))

输出：

('select * from tb_user where id = ?;\n', 'ea72157cdf3e46c55792f49d01d1ce19')
('select * from tb_user where id = ?;\n', 'ea72157cdf3e46c55792f49d01d1ce19')
('select * from tb_user where id = ?;\n', 'ea72157cdf3e46c55792f49d01d1ce19')
('select * from tb_user where id = ?;\n', 'ea72157cdf3e46c55792f49d01d1ce19')
('select * from tb_user where id = ?;\n', 'ea72157cdf3e46c55792f49d01d1ce19')
('select * from tb_user where id = ?;\n', 'ea72157cdf3e46c55792f49d01d1ce19')

提供了将 sql 转换为指纹和 sql hash 的函数，大家可以将此定制到自己的代码和功能中。