> Important: All scripts/ paths are relative to this skill directory.

> Run with: cd {skill_dir} && python scripts/... or use the cwd parameter.

⚠️ Security & Privacy Notice / 安全与隐私声明

> Please read this notice before using this skill. / （必读）使用本 skill 前请仔细阅读本声明。

⚠️ Credential Access Notice / 凭据访问声明

How credentials are resolved (in order of precedence):

> Security (Hybrid Encryption):

>

> - Password is encrypted with dbpw_key before storing in OS env

> - Both the encrypted password (in env) and dbpw_key (in config.yaml) are needed to decrypt

> - Even if env is leaked, password cannot be recovered without dbpw_key

> - OpenClaw agent cannot access password without knowing dbpw_key

> Display Protection: When entering password, terminal shows no echo. Output only shows length and head+tail preview (e.g., te**23 for test123).

> ⚠️ IMPORTANT: Keep dbpw_key secret! If lost, password cannot be recovered.

> Optional Hardening — require_explicit_credential_approval: To force explicit confirmation before silent credential loading, set require_explicit_credential_approval: true in audit_config.json. When enabled, the first connection attempt with password_env/password_dpapi will raise PermissionError until you call db.explicit_credential_approval() in your code.

⚠️ Connection-on-Init Notice / 连接初始化声明

Creating a SQLLinker or DBBridge instance does not automatically connect. Connection is deferred until the first actual database call (connect() is called lazily on first query). This avoids premature infrastructure access.

⚠️ Audit Data Collection Notice / 审计数据收集声明

> Data Minimization: Audit records collect the minimum identity fields required for compliance traceability: user_name, user_label, session_id, and optionally ip_address (disabled by default). SQL text is masked before logging. No passwords, personal identity numbers, or business-sensitive fields are intentionally captured.

>

> Retention: Audit records are stored in sql_audit_log in the target database. Retention policy is determined by your organization's database retention schedule, not by this skill.

>

> Opt-Out: Set audit: enabled: false in audit_config.json to disable application-layer audit logging. Database-layer triggers (if any) are independent of this setting.

>

> Consent: By using this skill, you consent to having database operation metadata (operator identity, table name, masked SQL, row counts, timestamp, status) recorded in sql_audit_log. Do not include sensitive personal data (e.g., national ID numbers, passwords, medical info) in SQL query parameters — such values will be masked but still persisted in log records.

Audit Data Collection / 审计数据收集

Audit Log: Every database operation records the following fields to sql_audit_log:

⚡ Privacy Notice: SQL text is masked before logging — string and numeric literals are replaced with ?. No password or raw PII is intentionally stored. However, the log table itself contains identity metadata; treat it as sensitive. Avoid including sensitive personal data in query parameters.

Automatic Data Discovery (Can Be Disabled) / 自动数据发现（可关闭）

By default, this skill auto-collects audit context from the following sources:

If you do not want auto-collection, explicitly pass user_label and session_id parameters — this skill will prefer passed values over auto-discovery.

Automatic Config File Creation / 配置文件自动创建

On first use or when config files are missing, this skill auto-creates files under ~/.sql_linker/:

> Password Setup: Run set_env.ps1 (Windows) or set_env.sh (Linux/macOS) in .sql_linker/ folder to encrypt and save the database password. Password is encrypted with dbpw_key before storing in OS env.

For full manual control, create these files before invoking the skill. bootstrap() is idempotent but prints a safety warning before creating files.

Password Precedence / 密码来源优先级

password > password_env + dbpw_key > password_dpapi

• password: Direct plaintext (not recommended)
• password_env: OS environment variable with encrypted password (requires dbpw_key to decrypt)
• dbpw_key: 6-char encryption key (auto-generated by bootstrap, keep secret)
• password_dpapi: Windows DPAPI decryption (Windows only, user-scoped)

Setting the password (Windows):

# 1. Ensure config.yaml has dbpw_key
# 2. Run set_env.ps1 to encrypt and save password
cd .sql_linker
.\set_env.ps1
# Enter password (no echo in terminal)
# Output shows: length + head+tail preview (e.g., "Wo**88")

First-time setup (after bootstrap):

1. Run bootstrap(dry_run=False, explicit_confirm=True)
2. SAVE the dbpw_key shown in output (e.g., Kx**)
3. Run set_env.ps1 to set encrypted password
4. REMEMBER the dbpw\_key — without it, password cannot be recovered!

Destructive Operations / 破坏性操作确认

UPDATE / DELETE operations execute directly and cannot be rolled back. In production, enable read-only mode (read_only: true) for pre-validation.

Legacy Users Notice / 旧版用户请注意

Version 1.2.3 → 1.3.0 Changes:

• dbpw_key added: Password encryption now requires dbpw_key (6-char key) for hybrid encryption
• password_env stores encrypted password, decrypted using dbpw_key from config.yaml
• Both encrypted password (in env) AND dbpw_key (in config.yaml) are required to decrypt
• OpenClaw agent cannot access password without knowing dbpw_key
• set_env.ps1 encrypts password before saving to OS env
• Output shows only password length and head+tail preview (e.g., Wo**88)
• dbpw_key auto-generated by bootstrap, must be kept secret
• .env file no longer created by bootstrap or used

Version 1.2.2 → 1.2.3 Changes:

• bootstrap(dry_run=False) now requires explicit_confirm=True to write files — a BootstrapConfirmationRequired exception is raised otherwise. This prevents accidental configuration persistence in shared workspaces (Finding #1 of the ClawHub security audit, 62% confidence)
• New exception BootstrapConfirmationRequired raised when bootstrap write is attempted without explicit confirmation
• DBBridge.bootstrap() forwards the new explicit_confirm parameter

Version 1.1.1 → 1.2.0 Changes:

• Tightened trigger language: removed "whenever" + vague "CRUD tasks" / "data manipulation" open-ended triggers; now requires specific named table or explicit intent
• Added Credential Access Notice: documents password_env / password_dpapi auto-resolution and the no-prompt nature of credential loading
• Added Connection-on-Init Notice: clarifies that SQLLinker/DBBridge instantiation does NOT auto-connect
• Added Audit Data Minimization & Consent Notice: documents what is/isn't captured, opt-out path, and privacy expectations
• Audit log SELECT can be disabled via log_select: false (default false) — SELECT logging only occurs when BOTH audit: enabled: true AND log_select: true
• collect_lan_ip defaults to false (was not explicitly defaulted before)
• session_id / user_label prefer explicitly passed values; no longer auto-read from sessions.json

SQL-Linker — 双层架构：数据操作层 + 业务层 / Data Ops + Business Layer

Overview / 概述

(中文) SQL-Linker 提供跨数据库的 CRUD 操作能力，支持 MySQL、PostgreSQL、SQLite 三种主流数据库。内置审计日志模块，每次操作自动记录操作人身份、IP、SQL 语句、操作时间，确保数据可溯源、安全可控。业务层（db\_bridge）负责字段白名单过滤和时间戳自动注入，数据操作层（sql\_linker）负责连接管理、CRUD 执行和审计记录，两层严格分离，互不干扰。

(English) SQL-Linker provides cross-database CRUD operations, supporting MySQL, PostgreSQL, and SQLite, with a built-in audit trail module that automatically records operator identity, IP, SQL statements, and timestamps for full traceability and compliance. The business layer (db\_bridge) handles field whitelist filtering and automatic timestamp injection, while the data operation layer (sql\_linker) manages database connections, CRUD execution, and audit logging. The two layers are strictly separated and independent.

Core Architecture / 核心架构

(中文) 系统由两层组成，业务层和数据操作层职责分明：

(English) The system consists of two layers with clearly defined responsibilities:

workspace/
└── .sql_linker/                          ← Config root
    ├── config_home/
    │   ├── config.yaml                   ← DB connection config
    │   ├── audit_config.json             ← Audit config
    │   └── extra_tables.json             ← Privileged table config (JSON)
    └── table_home/
        └── table_dictionary.json         ← Main dictionary (JSON, all controlled tables)

skills/sql-linker/scripts/
├── controller_layer/                      ← Data operation layer
│   ├── sql_linker.py                     ← Connection management + CRUD execution + audit context injection
│   └── sql_audit.py                       ← Audit module (used internally by sql_linker.py)
└── service_layer/                        ← Business layer
    └── db_bridge.py                      ← Four-layer access control + timestamp injection + field whitelist

(中文) 业务层（service\_layer）：读取 table\_dictionary.json，过滤字段，注入时间戳，校验访问权限，调用数据操作层，不直接操作数据库。

(English) Business Layer (service\_layer): Reads table\_dictionary.json, filters fields, injects timestamps, verifies access rights, and calls the data operation layer. Does not directly access the database.

(中文) 数据操作层（controller\_layer）：管理数据库连接，执行 CRUD 操作，写入审计日志，处理参数化查询，不处理业务逻辑。

(English) Data Operation Layer (controller\_layer): Manages database connections, executes CRUD operations, writes audit logs, handles parameterized queries. Does not process business logic.

Four-Layer Access Model / 四层访问模型

(中文) 系统通过四层访问模型实现精确的表访问控制：

(English) The system implements precise table access control through a four-layer access model:

SYSTEM (系统表 sql\_audit\_log)

• Hard-coded: db\_bridge.py SYSTEM\_TABLES
• SELECT/INSERT: ALLOW
• UPDATE/DELETE: DENY (SystemTableWriteDenied)
• Field whitelist: N/A
• Timestamp injection: N/A
• Audit: Native cursor bypasses db\_bridge
• ⚠️ Important: Audit log is a regular database table, NOT tamper-evident. It does NOT provide cryptographic chaining, signatures, or append-only enforcement. For tamper-evident requirements, implement additional database-layer controls (e.g., triggers, immutable audit tables).

NORMAL (主词典表格)

• File: table\_dictionary.json
• Field whitelist: YES (only fields in table.json)
• Timestamp injection: YES (created\_at/updated\_at auto-generated)
• Audit: Full
• Ready to use without extra config

PRIVILEGED (特权表格)

• File: extra\_tables.json (table list) + config.yaml extra\_tables\_enabled (global on/off switch)
• Field whitelist: NO (unknown schema, direct DB exposure)
• Timestamp injection: NO
• Audit: Full
• Two-layer gate: (1) table must be listed in extra\_tables.json; (2) config.yaml extra\_tables\_enabled must be true (both required)

BLOCKED (禁用)

• Not in dictionary nor extra\_tables
• All operations denied, denial logged
• Cannot access unless added to extra\_tables.json

(中文) 访问判定流程：提取 SQL 中的表名 → 检查 SYSTEM → 检查主词典（NORMAL）→ 检查 extra\_tables（PRIVILEGED）→ 其余 BLOCKED。

(English) Access Decision Flow: Extract table name from SQL → Check SYSTEM → Check main dictionary (NORMAL) → Check extra\_tables (PRIVILEGED) → Rest BLOCKED.

Bootstrap / 引导初始化

(中文) 首次使用或缺少配置文件时，系统自动生成默认模板（幂等操作，不会覆盖已有文件）：

(English) On first use or when config files are missing, the system automatically generates default templates (idempotent, will not overwrite existing files):

> ⚠️ Bootstrap 自动创建配置：首次使用时会自动在 ~/.sql_linker/ 目录下创建配置文件（config.yaml、audit_config.json 等）。密码请使用 set_env.ps1 设置到 Windows 环境变量。bootstrap() 为幂等操作，不会覆盖已有文件，但会创建缺失的文件。

from db_bridge import DBBridge

db = DBBridge(user_label="openclaw-control-ui", session_id="agent:hr:main")

# Preview files to be created (no actual write)
preview = db.bootstrap(dry_run=True)
print(f'Will create: {preview}')

# Execute actual bootstrap
created = db.bootstrap()
print(f'Created: {created}')
# ['...\\config.yaml', '...\\audit_config.json', ...]

(中文) 自动生成的文件列表：

(English) Auto-generated files:

Config Files / 配置文件说明

table_home/table_dictionary.json — Main Dictionary / 主词典

(中文) 所有受控业务表必须在主词典中声明，字段白名单仅对 NORMAL 层生效：

(English) All controlled business tables must be declared in the main dictionary. Field whitelist only applies to NORMAL layer:

{
  "version": 1,
  "tables": [
    {
      "table_name": "supplier_table",
      "comment": "供应商信息表",
      "fields": [
        { "name": "id",            "type": "BIGINT",       "pk": true,  "auto": true  },
        { "name": "supplier_code", "type": "VARCHAR(32)",  "pk": false, "auto": false },
        { "name": "supplier_name", "type": "VARCHAR(128)", "pk": false, "auto": false },
        { "name": "short_name",    "type": "VARCHAR(64)",  "pk": false, "auto": false },
        { "name": "supplier_level","type": "VARCHAR(16)",  "pk": false, "auto": false },
        { "name": "contact_person","type": "VARCHAR(64)",  "pk": false, "auto": false },
        { "name": "contact_phone", "type": "VARCHAR(32)",  "pk": false, "auto": false },
        { "name": "contact_email", "type": "VARCHAR(128)", "pk": false, "auto": false },
        { "name": "status",        "type": "VARCHAR(16)",  "pk": false, "auto": false },
        { "name": "created_at",    "type": "DATETIME",     "pk": false, "auto": false },
        { "name": "updated_at",   "type": "DATETIME",     "pk": false, "auto": false }
      ]
    }
  ]
}

config_home/extra_tables.json — Privileged Table Config / 特权表配置

(中文) 词典外表格需通过此配置显式授权，enabled=false 时所有非词典表均 BLOCKED：

(English) Tables outside the dictionary require explicit authorization via this config. When enabled=false, all non-dictionary tables are BLOCKED:

{
  "version": 1,
  "enabled": false,
  "max_extra_tables": 10,
  "tables": [
    { "table_name": "employee_table" },
    { "table_name": "payroll_table" }
  ]
}

config_home/config.yaml — Connection Config / 连接配置

(中文) 数据库连接配置，password 不直接写在文件中，通过 password\_env 引用加密后的密码，dbpw\_key 用于混合加密：

(English) Database connection config. Password is encrypted with dbpw_key before storing in OS env:

type: mysql
host: 127.0.0.1
port: 3306
database: db_dev
user: admin
password_env: mysql_pw           # OS env key (stores encrypted password)
dbpw_key: Kx9mT2                 # 6-char encryption key (KEEP SECRET!)
# password_dpapi: AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAA...   # DPAPI-encrypted password (optional)
read_only: false
max_rows: 1000
timeout: 30
extra_tables_enabled: false

> Security: Password is encrypted with dbpw_key using HMAC-SHA256. Both encrypted password (in OS env) AND dbpw_key (in config) are required to decrypt. OpenClaw agent cannot access password without knowing dbpw_key.

Python API — Business Layer (Recommended) / Python API — 业务层（推荐）

(中文) 使用业务层 API（推荐），完整支持四层访问控制和时间戳自动注入：

(English) Use the business layer API (recommended), with full four-layer access control and automatic timestamp injection:

import sys
sys.path.insert(0, "skills/sql-linker/scripts/service_layer")
from db_bridge import DBBridge

db = DBBridge(
    user_label="openclaw-control-ui",   # ← OpenClaw metadata.label
    session_id="agent:hr:main"          # ← OpenClaw metadata.id
)

INSERT — Automatic Timestamp Injection / 时间戳自动注入

(中文) INSERT 操作自动生成 created\_at 和 updated\_at（两者同值），仅写入主词典中声明的字段：

(English) INSERT operations automatically generate created\_at and updated\_at (same value), writing only fields declared in the main dictionary:

db.insert("supplier_table", {
    "supplier_code": "LX001",
    "supplier_name": "立讯精密",
    "supplier_level": "A",
    "status": "active"
})
# → created_at / updated_at auto-generated, no manual injection needed

UPDATE — Automatic Timestamp Refresh / 时间戳自动刷新

(中文) UPDATE 操作自动刷新 updated\_at，created\_at 保持不变：

(English) UPDATE operations automatically refresh updated\_at, leaving created\_at unchanged:

db.update(
    "supplier_table",
    {"supplier_level": "AA"},
    "supplier_code = %s",
    ("LX001",)
)
# → updated_at auto-refreshed to current time

DELETE — Full Audit / 完整审计

db.delete("supplier_table", "status = %s", ("inactive",))

SELECT — Parameterized Injection Prevention / 参数化防注入

rows = db.query(
    "SELECT * FROM supplier_table WHERE status = %s AND supplier_level = %s",
    ("active", "A")
)
for row in rows:
    print(row)

Helper Methods / 辅助方法

db.tables()           # Return all table names in main dictionary
db.extra_tables()     # Return current privileged table list
db.system_tables()    # Return protected system table list
db.fields("supplier_table")  # Return table field list (privilege table returns empty)
db.bootstrap(dry_run=False) # Execute bootstrap; dry_run=True returns file list without writing

Error Handling / 错误处理

from db_bridge import TableAccessDenied, SystemTableWriteDenied

try:
    db.query("SELECT * FROM unknown_table LIMIT 1")
except TableAccessDenied as e:
    print("Access denied:", e)

try:
    db.update("sql_audit_log", {"status": "tampered"}, "id = %s", (1,))
except SystemTableWriteDenied as e:
    print("System table write denied:", e)

Python API — Data Operation Layer / Python API — 数据操作层

(中文) 直接使用数据操作层，跳过业务层字段过滤和时间戳注入（适用于高级用户）：

(English) Use the data operation layer directly, bypassing business layer field filtering and timestamp injection (for advanced users):

import sys
sys.path.insert(0, "skills/sql-linker/scripts/controller_layer")
from sql_linker import SQLLinker

linker = SQLLinker()
linker.connect()
# Explicit audit context (preferred over auto-discovery)
linker.set_user_context(user_name="HR", user_label="openclaw-control-ui",
                        ip_address="", session_id="agent:hr:main")

Timestamp Logic / 时间戳注入规则

Audit Trail / 审计日志

(中文) 配置位置：.sql_linker/config_home/audit_config.json

(English) Config location: .sql_linker/config_home/audit_config.json

{
  "username": "HR",
  "audit": {
    "enabled": true,
    "log_table": "sql_audit_log",
    "log_select": false,
    "mask_values": true,
    "collect_lan_ip": false
  }
}

(中文) 审计记录字段（自动注入，不可为空）：

(English) Audit record fields (automatically injected, must not be empty):

Field Type Reference / 字段类型参考

Dual-Layer Audit / 双层审计体系

sql-linker adopts application layer + database layer dual-layer audit; any direct-connection bypass of the application layer is still captured.

Audit Log Query / 审计日志查看

sql_audit_log is in SYSTEM layer, business layer can SELECT directly:

from datetime import date, timedelta
db = DBBridge(user_label="audit-viewer", session_id="agent:audit")

# Query today's operation records
today = date.today().strftime('%Y-%m-%d')
rows = db.query(
    "SELECT log_time, user_name, operation, table_name, sql_statement, rows_affected, status "
    "FROM sql_audit_log WHERE DATE(log_time) = %s ORDER BY log_time DESC",
    (today,)
)

# Query failed operations in last 7 days
week_ago = (date.today() - timedelta(days=7)).strftime('%Y-%m-%d')
failed = db.query(
    "SELECT * FROM sql_audit_log WHERE status = 'FAILED' AND log_time >= %s "
    "ORDER BY log_time DESC",
    (week_ago,)
)

# Query recent operations for a supplier (fuzzy match)
rows = db.query(
    "SELECT log_time, user_name, operation, table_name, sql_statement "
    "FROM sql_audit_log WHERE sql_statement LIKE %s ORDER BY log_time DESC LIMIT 20",
    ('%LX001%',)
)

> ⚠️ sql_audit_log is a system-protected table. UPDATE/DELETE blocked by SystemTableWriteDenied. Only SELECT is allowed.

Layer 1: Application-Layer Audit / 第一层：应用层审计

• Component: sql_linker.py → SQLAudit class
• Mechanism: Write to sql_audit_log after each CRUD operation
• Coverage: Operations via db_bridge only
• Limitation: Direct pymysql / mysql CLI can bypass

Layer 2: Database-Layer Trigger / 第二层：数据库层触发器

• Nature: Deployment artifact, not part of the skill package. Triggers are bound to specific table schemas, created by the deployer/DBA per actual schema.
• Mechanism: Create AFTER INSERT/UPDATE/DELETE triggers on MySQL side, mandatory write to sql_audit_log
• Coverage: All write operations directly connecting to the database, regardless of connection tool or path

Trigger Writing Principles / 触发器编写原则

On each controlled business table, create one AFTER trigger each for INSERT / UPDATE / DELETE. Example structure:

-- Example using supplier_capa (same for other tables)
CREATE TRIGGER trg_<table>_ai
AFTER INSERT ON <table>
FOR EACH ROW
BEGIN
  INSERT INTO sql_audit_log
    (log_time, user_name, user_label, ip_address, session_id,
     db_type, operation, table_name, sql_statement, rows_affected, status, error_msg)
  VALUES
    (NOW(), CURRENT_USER(), 'DB_TRIGGER', 'internal', 'DB_TRIGGER',
     'mysql', 'INSERT', '<table>',
     CONCAT('INSERT id=', NEW.id, ' supplier_code=', NEW.supplier_code),
     1, 'SUCCESS', NULL);
END;

Implementation Steps:

1. Confirm sql_audit_log.id is AUTO_INCREMENT (otherwise trigger INSERT fails due to no default id)
2. Execute three triggers for each controlled table
3. Triggers saved in user repository or DBA management scripts, not distributed with the skill package

Dual-Layer Combined Effect / 两层配合效果

Security Principles / 安全原则

(中文)

1. Field Whitelist: NORMAL tables only write fields declared in table_dictionary.json; illegal fields auto-filter
2. Four-Layer Access Control: SYSTEM (read+audit write) / dict (whitelist+timestamp) / privileged (direct) / blocked (deny)
3. Parameterized Queries: All use %s + tuple to prevent SQL injection
4. Sensitive Credential Separation: password_env reads from Windows OS env; password_dpapi (DPAPI encrypted) alternative — no plaintext in files
5. Dual-Layer Audit: Application-layer db_bridge + database-layer triggers (created by deployer per actual schema)
6. System Table Protection: sql_audit_log prohibits UPDATE/DELETE, SystemTableWriteDenied exception enforced
7. Idempotent Bootstrap: Missing config files auto-generated without overwriting existing configs

(English)

1. Field Whitelist: NORMAL tables only write fields declared in table_dictionary.json; illegal fields are automatically filtered
2. Four-Layer Access Control: SYSTEM table (read+audit write) / Normal dictionary (whitelist+timestamp) / Privileged (direct query) / Blocked (denied)
3. Parameterized Queries: All use %s + tuple to prevent SQL injection
4. Sensitive Credential Separation: password_env reads from Windows OS env (set via set_env.ps1); password_dpapi alternative — no plaintext in any file
5. Dual-Layer Audit: Application-layer db_bridge + database-layer triggers (created by deployer per actual schema)
6. System Table Protection: sql_audit_log prohibits UPDATE/DELETE, SystemTableWriteDenied exception enforced
7. Idempotent Bootstrap: Missing config files are auto-generated without overwriting existing configs

Common Errors and Solutions / 常见错误与解法

Directory Structure Overview / 目录结构总览

workspace/
└── .sql_linker/
    ├── set_env.ps1                    ← Password setup script for Windows (auto-generated)
    ├── set_env.sh                     ← Password setup script for Linux/macOS (auto-generated)
    ├── config_home/
    │   ├── config.yaml                 ← Connection config (extra_tables_enabled switch)
    │   ├── audit_config.json           ← Audit config (collect_lan_ip option)
    │   └── extra_tables.json           ← Privileged table list (JSON)
    └── table_home/
        └── table_dictionary.json       ← Main dictionary (JSON, all controlled tables)
            └── tables[]                 ← Each table's fields[] whitelist + comment

skills/sql-linker/
├── SKILL.md                           ← This document
└── scripts/
    ├── controller_layer/               ← Data operation layer
    │   ├── sql_linker.py              ← Connection management + CRUD + audit
    │   └── sql_audit.py               ← Audit module
    └── service_layer/                  ← Business layer
        └── db_bridge.py               ← Four-layer access + timestamp + Bootstrap