tools/find_dynamic_sql_procs.py

"""tools/find_dynamic_sql_procs.py
Simple CLI wrapper that runs the T-SQL scanner against a SQL Server database using pyodbc.
Usage:
  python tools/find_dynamic_sql_procs.py --server demo.azonix.in --database hs-prod3 --uid user --pwd pass

It prints a CSV to stdout with basic columns and confidence score.

Notes:
 - Requires pyodbc installed in your environment.
 - Use Windows authentication by omitting uid/pwd and passing --trusted.
"""
import argparse
import csv
import sys
import pyodbc

import os

SQL_PATH = os.path.join(os.path.dirname(__file__), 'find_dynamic_sql_procs.sql')
SQL_TEMPLATE = open(SQL_PATH, 'r', encoding='utf-8').read()

def run_scan(conn_str, top=1000):
    sql = SQL_TEMPLATE.replace('TOP(@Top)', f'TOP({top})') if 'TOP(@Top)' in SQL_TEMPLATE else SQL_TEMPLATE
    with pyodbc.connect(conn_str, autocommit=True) as cn:
        cur = cn.cursor()
        cur.execute(sql)
        cols = [c[0] for c in cur.description]
        writer = csv.writer(sys.stdout)
        writer.writerow(cols)
        for row in cur:
            writer.writerow(row)


if __name__ == '__main__':
    ap = argparse.ArgumentParser(description='Find procs using dynamic SQL patterns')
    ap.add_argument('--server', required=True)
    ap.add_argument('--database', required=True)
    ap.add_argument('--uid')
    ap.add_argument('--pwd')
    ap.add_argument('--trusted', action='store_true')
    ap.add_argument('--top', type=int, default=1000)
    args = ap.parse_args()

    if args.trusted:
        conn = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={args.server};DATABASE={args.database};Trusted_Connection=yes;'
    else:
        if not args.uid or not args.pwd:
            ap.error('Either --trusted or both --uid and --pwd are required')
        conn = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={args.server};DATABASE={args.database};UID={args.uid};PWD={args.pwd};'

    run_scan(conn, top=args.top)