chuanqi/tools/create_rsa_key_table.py

# -*- coding: utf-8 -*-
"""
996cq 库新增 login_rsa_keys 表：存储 996盒子登录页 RSA 公钥
- 单行语义（scope='login_byPwd'），is_active 标记当前生效
- 保留历史版本行（is_active=0），便于回溯轮换记录
"""
import pymysql

CONN = dict(host="47.99.164.66", port=3306, user="root",
            password="hn02le.34lkdLKD", database="996cq",
            charset="utf8mb4", autocommit=True, connect_timeout=10)

DDL = """
CREATE TABLE IF NOT EXISTS login_rsa_keys (
    id           INT PRIMARY KEY AUTO_INCREMENT,
    scope        VARCHAR(64)  NOT NULL COMMENT '用途: login_byPwd',
    public_key   VARCHAR(512) NOT NULL COMMENT 'X.509 SPKI base64 (216 chars)',
    key_n_hex    VARCHAR(256) NOT NULL COMMENT '模数 hex，便于比对/排查',
    source       VARCHAR(32)  NOT NULL DEFAULT 'bootstrap' COMMENT '来源: bootstrap/login_page_hook',
    is_active    TINYINT(1)   NOT NULL DEFAULT 1,
    verified_at  TIMESTAMP NULL COMMENT '最近一次用该 key 成功登录的时间',
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_scope_active (scope, is_active, public_key(216)),
    INDEX idx_scope (scope, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='996盒子登录 RSA 公钥（自动轮换）'
"""

# 2026-09-17 从登录页 runtime hook 导出的当前公钥
CURRENT_KEY = ("MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDAjCQvqNPnOZm6GVDqeOb5S2pJtbN3FMZ8"
               "vddW3B2f3jB0mf7vd1B+yjjfex2A5XVxr4YwAY/w4d1/m3v3Bn/qgAnLMKnpjeEwxQU3cU81eXAam"
               "4fMB+nlmC291quIuC0qFFL8YgT2bDjARsMRYbmKd6LTc58sKYeJD2MvkkOdKwIDAQAB")

from Crypto.PublicKey import RSA
import base64
N_HEX = format(RSA.import_key(base64.b64decode(CURRENT_KEY)).n, "x")

conn = pymysql.connect(**CONN)
try:
    with conn.cursor() as cur:
        cur.execute(DDL)
        print("[+] table login_rsa_keys created (IF NOT EXISTS)")
        # 同 scope 旧 active 行全部下线
        cur.execute("UPDATE login_rsa_keys SET is_active=0 WHERE scope=%s AND is_active=1", ("login_byPwd",))
        cur.execute(
            "INSERT INTO login_rsa_keys (scope, public_key, key_n_hex, source, is_active) "
            "VALUES (%s, %s, %s, %s, 1)", ("login_byPwd", CURRENT_KEY, N_HEX, "bootstrap"))
        print("[+] active key inserted:", CURRENT_KEY[:50] + "...")
        cur.execute("SELECT id, scope, LEFT(public_key,40), is_active, source, created_at FROM login_rsa_keys")
        for row in cur.fetchall():
            print("   ", row)
finally:
    conn.close()