java-sql-audit

Java SQL Injection Vulnerability Audit Tool

Scan Java Web project source code, locate all SQL execution entry points, and detect SQL injection vulnerabilities.

Audit boundaries (this skill only detects the following content and must not exceed this scope):

• ✅ SQL injection vulnerabilities (parameter concatenation, dynamic SQL, ORDER BY injection, etc.)
• ✅ Missing parameterized queries
• ❌ Not included: code quality issues, architectural security issues, other vulnerability types (XSS/SSRF/command injection, etc.)

---

Vulnerability severity grading standards

See SEVERITY_RATING.md

• Vulnerability ID format: {C/H/M/L}-SQL-{序号}
• Severity level = f(reachability R, impact scope I, exploitation complexity C)
• Score = R × 0.40 + I × 0.35 + C × 0.25, mapped to CVSS 3.1

---

Core requirements

This skill must fully analyze all SQL-related code; omissions are not allowed.

• ✅ Identify all SQL execution entry points (JDBC/MyBatis/Hibernate)
• ✅ Analyze the parameterization status of each SQL operation
• ✅ Detect all potential SQL injection patterns
• ✅ Provide a verification PoC for each risk point
• ❌ Omitting any SQL operation is prohibited
• ❌ Skipping decompilation steps is prohibited

No-omission rules (mandatory)

All lists and tables in the report must be output completely; any form of omission is prohibited:

Prohibited wording	Correct practice
{...省略...}	List all entries completely
... (其他N个)	List all entries completely
等等 / etc.	List all entries completely
以此类推	List all entries completely
更多见xxx	List completely at the current position

Example - incorrect wording:

| # | 方法名 | 说明 |
|---|--------|------|
| 1 | getCommonQuery | 通用查询 |
| 2 | getCollisionQuery | 碰撞查询 |
|{...省略...}| ... | ... |    ← ❌ 禁止

Example - correct wording:

| # | 方法名 | 说明 |
|---|--------|------|
| 1 | getCommonQuery | 通用查询 |
| 2 | getCollisionQuery | 碰撞查询 |
| 3 | getIntervalQuery | 区间查询 |
| ... | ... | ... |
| 23 | getNewStaypointQuery | 新停留点查询 |    ← ✅ 完整列出

Content that must be listed completely:

• List of affected methods
• SQL operation mapping table
• Vulnerability details list
• Parameter list
• Verification Payload list

---

Skill collaboration workflow (CRITICAL)

java-sql-audit should be executed after java-route-mapper, conducting the audit based on the already-organized route information.

┌─────────────────────────────────────────────────────────────────┐
│                    完整审计流程                                  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  [步骤1] java-route-mapper                                      │
│     │                                                           │
│     │ 输出：                                                    │
│     │ ├─ 所有 HTTP 路由列表                                     │
│     │ ├─ 每个路由的参数定义                                     │
│     │ │   ├─ 参数名、类型                                       │
│     │ │   └─ JSON 内部字段（如 pageJson.orderBy）               │
│     │ └─ Burp Suite 请求模板                                    │
│     │                                                           │
│     ↓                                                           │
│  [步骤2] java-sql-audit（本技能）                               │
│     │                                                           │
│     │ 输入：java-route-mapper 的输出                            │
│     │                                                           │
│     │ 执行：                                                    │
│     │ ├─ 快速扫描高危文件                                       │
│     │ ├─ 参数-SQL 映射分析                                      │
│     │ ├─ 检查每个 String 参数是否进入 SQL                       │
│     │ └─ 执行条件分析                                           │
│     │                                                           │
│     ├─── 需要深入追踪 ───→ java-route-tracer                    │
│     │                           │                               │
│     │    ←── 返回调用链信息 ────┘                               │
│     │                                                           │
│     ↓                                                           │
│  [步骤3] 输出综合审计报告                                       │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Input dependencies (from java-route-mapper)

Before starting the audit, you must check whether the output file of java-route-mapper already exists:

{project_name}_audit/
├── route_mapper/
│   ├── {project_name}_route_mapper_{timestamp}.md    ← 主索引（先读此文件定位模块详情）
│   ├── {module_name}/
│   │   └── {project_name}_module_{module_name}_{timestamp}.md  ← 模块详情
│   └── webservice/
│       └── {project_name}_ws_{service_name}_{timestamp}.md
└── sql_audit/
    └── {project_name}_sql_audit_{timestamp}.md  ← 本技能输出

If the route_mapper output does not exist, you must run it first (if not run, stop the audit and return an error prompt):

Skill(skill="java-route-mapper", args="--project {project_path}")

Key information obtained from route_mapper

Information	Purpose
Route path	Locate Controller/Action entry points
Parameter name + type	Identify high-risk String-type parameters
JSON internal fields	Identify nested parameters (such as pageJson.orderBy）
Parameter purpose description	Determine whether it is used for SQL (sorting, grouping, etc.)

---

Workflow (three stages)

Stage 1: Quick scan (execute first)

Objective: Quickly locate high-risk files and patterns without missing key points.

# 1.1 搜索分页/排序辅助类（最易遗漏）
find . -name "*Pagination*.java" -o -name "*PageHelper*.java" -o -name "*Pager*.java"
find . -name "*JdbcSupport*.java" -o -name "*JdbcTemplate*.java"

# 1.2 搜索 DAO 基类
find . -name "Abstract*Dao*.java" -o -name "Base*Dao*.java" -o -name "*Support.java"

# 1.3 搜索 ORDER BY 拼接模式
grep -ri "order by" --include="*.java" | grep -v "ORDER BY \?"
grep -ri "getOrderBy\|getSortField\|getOrder\|getSortOrder" --include="*.java"
grep -ri "append.*order" --include="*.java"

# 1.4 搜索 SQL 执行点
grep -ri "executeQuery\|executeUpdate\|prepareStatement" --include="*.java"
grep -ri "createQuery\|createNativeQuery\|createSQLQuery" --include="*.java"

Output: High-risk file list (sorted by priority)

Priority	File Type	Audit Focus
P0	*Pagination*.java, *PageHelper*.java	orderBy, order, sort parameters
P1	Abstract*Dao.java, Base*Dao.java	Generic SQL construction methods
P2	*Mapper.java, *Dao.java, *Repository.java	Business SQL operations
P3	*Mapper.xml	MyBatis ${} usage

Phase 2: Parameter-SQL Mapping Analysis

Based on the output of java-route-mapper, analyze whether each parameter enters SQL.

2.1 High-Risk Parameter Identification

Extract all String-type parameters from the route_mapper output:

Parameter Source	Parameter Name	Type	SQL Injection Level
pageJson	orderBy	String	High Risk - Sort field directly concatenated
pageJson	order	String	High Risk - Sort direction directly concatenated
searchJson	keyword	String	High Risk - Search keyword directly concatenated
searchJson	status	String	Medium Risk - Status filter concatenated
pageJson	pageSize	int	Low - Numeric type difficult to inject

2.2 Parameter Tracing

For each high-risk parameter, trace its usage in the code:

HTTP 参数: pageJson.orderBy (String)
    ↓ 反序列化
Page.orderBy
    ↓ 传递
Service.method(page)
    ↓ 传递
Dao.query(page)
    ↓ 调用
AbstractDao.findSql(page)
    ↓ 使用
sql.append(" order by ").append(page.getOrderBy())  ← SQL 拼接点

2.3 SQL Execution Point Inspection

For each SQL execution point found in Phase 1, check:

1. Is parameterized query used?

   • PreparedStatement + ? Placeholder → Safe
   • String concatenation → Dangerous

2. Source of concatenated parameters?

   • String parameter from HTTP request → High Risk
   • From system configuration/constants → Safe
   • From database query results → Further analysis required

3. Is there input validation?

   • Whitelist validation → Safe
   • No validation, direct concatenation → High Risk

Phase 3: In-Depth Analysis and Reporting

3.1 Trigger java-route-tracer

When the following situations are found, call java-route-tracer to obtain the complete call chain:

Trigger Condition	Invocation Method
Parameters passed through multiple layers	Skill(skill="java-route-tracer", args="--route {route}")
Branch conditions unclear	Skill(skill="java-route-tracer", args="--route {route}")
Parent class SQL execution	Skill(skill="java-route-tracer", args="--route {route}")

3.2 Execution Condition Analysis

After discovering SQL concatenation, execution conditions must be analyzed (see subsequent chapters for details).

3.3 Generate Report

Integrate all analysis results to generate a comprehensive audit report.

---

SQL Framework Identification

Framework	Identification Features	Configuration File	Reference Materials
JDBC	java.sql.*, Statement, PreparedStatement, DriverManager	-	JDBC.md
MyBatis	@Mapper, @Select, SqlSession, #{}/${}	mybatis-config.xml, *Mapper.xml	MYBATIS.md
Hibernate	@Entity, Session.createQuery(), HQL, Criteria	hibernate.cfg.xml, persistence.xml	HIBERNATE.md

3. Decompilation Phase (CRITICAL)

When source code is unavailable, the CFR decompiler must be used to decompile SQL-related classes.

For detailed strategy, see DECOMPILE_STRATEGY.md

3.1 Decompilation Tool Invocation

# 反编译单个 DAO/Mapper 类
java -jar {CFR_JAR} /path/to/UserMapper.class --outputdir {output_path}/decompiled

# 反编译 DAO 相关目录
find /path/to/WEB-INF/classes/com/example/dao -name "*.class" | xargs java -jar {CFR_JAR} --outputdir {output_path}/decompiled

# 反编译多个指定文件
java -jar {CFR_JAR} /path/to/UserDao.class /path/to/OrderService.class /path/to/BaseMapper.class --outputdir {output_path}/decompiled

3.2 Classes That Must Be Decompiled

Type	Match Pattern	Purpose
DAO/Mapper	*Dao.class, *Mapper.class, *Repository.class	Extract SQL execution logic
Service	*Service.class, *ServiceImpl.class	Trace SQL call chain
Utility Class	*SqlUtil*.class, *DbHelper*.class	Extract generic SQL methods
Entity Class	*Entity.class, *Model.class	Hibernate annotation analysis

---

Execution Condition Analysis (CRITICAL - Avoid False Positives)

After discovering SQL concatenation code, you must analyze whether that code will actually be executed!

1. Database type branch check

After discovering SQL concatenation, you must check whether a database type determination exists:

Check pattern	Code characteristic	Handling method
Oracle branch	isOracle(), getDbType().equals("oracle")	Mark as Oracle-only
MySQL branch	isMySQL(), isMySql(), getDbType().equals("mysql")	Mark as MySQL-only
Common code	No database type determination	Mark as common

Example analysis:

public String getSql(QueryBean bean, List<Object> params) {
    if (this.isOracle()) {
        return this.getOracleSql(bean, params);  // Oracle分支 - SQL拼接在这里
    }
    return "";  // ⚠️ MySQL分支 - 直接返回空字符串！
}

2. Code path reachability analysis

Trace the complete path from entry to SQL execution, and check:

Check item	Description	Impact
Early return	return "", return null	Code not executed
Exception thrown	throw new Exception()	Code not executed
Condition not satisfied	if (false) and other dead code	Code not executed
Environment restriction	Executed only under specific database/configuration	Environment needs confirmation

3. Conclusion grading (must be marked)

Status	Meaning	Follow-up action
⚠️ To be verified	SQL concatenation exists in the code, but execution conditions are unconfirmed	Target environment needs confirmation
✅ Confirmed exploitable	It has been verified that the code path will execute in the target environment	Perform vulnerability exploitation testing
❌ Not exploitable	The code exists but is not executed in the target environment	Mark the reason and lower the priority
🔍 Environment-dependent	The vulnerability exists but is exploitable only in a specific environment	Mark the environment conditions

4. Audit process correction

发现 SQL 拼接代码
       ↓
检查是否有数据库类型分支（isOracle/isMySQL）
       ↓
  ┌────┴────┐
  有        无
  ↓         ↓
分析各分支   继续常规分析
  ↓
确认目标环境数据库类型
  ↓
判断漏洞代码是否会执行
  ↓
  ┌────┴────┐
 执行      不执行
  ↓         ↓
标注✅     标注❌
已确认     不可利用

5. Report output requirements

When SQL concatenation is discovered, the report must include:

### 执行条件分析

| 项目 | 值 |
|------|-----|
| 代码位置 | AnalyseDaoImpl.java:187 |
| 分支条件 | `if (this.isOracle())` |
| Oracle 分支 | 调用 getOracleSql() → SQL 拼接 |
| MySQL 分支 | `return ""` → 不执行 |
| 目标环境 | MySQL |
| **可利用性** | ❌ 不可利用（MySQL 环境下代码路径不执行） |

---

SQL injection detection rules quick reference

Core detection principles

Based on behavior identification, not fixed naming patterns. Developers may use arbitrary naming; fixed patterns will cause missed detections!

Principle	Description
Behavior first	Search for SQL keyword concatenation behavior, not method names/class names
Data flow tracing	Trace whether any String parameter flows into SQL
Multi-database coverage	Detection must cover Oracle, MySQL, PostgreSQL, SQL Server, etc.

For detailed detection strategies, please refer to:SQL_DETECTION_RULES.md

Detection rule reference

Detection type	Description	Reference document
General behavior detection	SQL keyword concatenation, data flow tracing, pagination sorting	SQL_DETECTION_RULES.md
JDBC framework	Statement, PreparedStatement, parameterization	JDBC.md
MyBatis framework	#{} vs ${}, dynamic SQL	MYBATIS.md
Hibernate framework	HQL、Criteria、Native Query	HIBERNATE.md

Quick detection command

# 搜索 SQL 关键字拼接（通用，不依赖命名）
grep -ri "order by\|group by" --include="*.java" | grep -E "\+|\.append"
grep -ri "limit\s|offset\s|rownum|row_number" --include="*.java"

---

⚠️ Parameter types and injection risk

SQL injection vulnerabilities mainly occur on String type parameters!

Parameter Type	Injection Risk	Reason
String	High	Arbitrary SQL fragments can be injected
Integer/int	Low	Illegal input will throw NumberFormatException
Long/long	Low	Illegal input will throw NumberFormatException
Boolean/boolean	Low	Can only be true/false

Audit points:

• Focus on String SQL concatenation of type parameters
• Pay special attention to sorting/grouping parameters such as orderBy, order, groupBy, etc.
• Even if numeric type parameters are concatenated, injection is difficult (but prepared statements are still recommended)
• Check whether there is intermediate processing that converts String to numeric

---

JDBC Dangerous vs Safe → Detailed Rules

Type	Dangerous Pattern	Safe Pattern
Query	stmt.executeQuery("..."+var)	pstmt.setXxx(1, var)
Concatenation	+, StringBuilder, String.format	? placeholder
ORDER BY	" order by " + orderBy	Whitelist validation

MyBatis Dangerous vs Safe → Detailed Rules

Type	Dangerous Pattern	Safe Pattern
Parameter	${param}	#{param}
like	'%${keyword}%'	CONCAT('%', #{keyword}, '%')
order by	ORDER BY ${col}	Use after whitelist validation
in	IN (${ids})	<foreach> tag

Hibernate Dangerous vs Safe → Detailed Rules

Type	Dangerous Pattern	Safe Pattern
HQL	"FROM User WHERE name='"+n+"'"	query.setParameter("name", n)
Native	createNativeQuery(sql+var)	Parameter binding
Criteria	Restrictions.sqlRestriction(str)	CriteriaBuilder API
Sorting	" order by " + sortField	Whitelist validation

When detailed detection rules are needed, click the corresponding framework link to load the complete documentation.

---

Audit Checklist (to prevent omissions)

Dangerous Patterns That Must Be Searched

At the start of the audit, the following searches must be performed:

# ORDER BY 注入检测
grep -r "order by" --include="*.java" | grep -v "ORDER BY \?"
grep -r "getOrderBy\|getSortField\|getOrder\|getSortOrder" --include="*.java"
grep -r "append.*order" --include="*.java"

# GROUP BY 注入检测
grep -r "group by" --include="*.java" | grep -v "GROUP BY \?"
grep -r "getGroupBy" --include="*.java"

# 分页辅助类检测
find . -name "*Pagination*.java" -o -name "*PageHelper*.java" -o -name "*JdbcSupport*.java"

# 基类检测
find . -name "Abstract*Dao*.java" -o -name "Base*Support*.java"

---

Data Flow Tracing (load java-route-tracer when needed)

When Parameter Tracing Is Needed

When the following situations are found,the java-route-tracer skill must be invoked for in-depth tracing：

Scenario	Description	Action
Parameters passed through multiple layers	HTTP parameters are passed through multiple layers Controller → Service → DAO and then concatenated into SQL	Load java-route-tracer
Parameter type conversion	After a String JSON is deserialized into an object, a certain field is used in SQL	Load java-route-tracer
Parent class/base class SQL execution	SQL execution is in a base class such as the parent class AbstractDao	Load java-route-tracer
Variable names change multiple times	The same parameter uses different variable names at different layers	Load java-route-tracer

Automatically trigger java-route-tracer (Claude Code specification)

When the following scenarios are found,the java-route-tracer skill must be automatically invoked：

# 当需要追踪参数流向时，自动调用 java-route-tracer
mcp__cclsp__find_references  # 先分析方法引用
# 然后自动触发
Skill(
    skill="java-route-tracer",
    args="--route {controller_route} --project {project_path}"
)

Vulnerability determination based on tracer output (CRITICAL - to prevent misjudgment)

java-sql-audit must perform vulnerability determination based on the "actual parameter usage analysis" output of java-route-tracer.

Decision flow

1. 调用 java-route-tracer 获取追踪报告
       ↓
2. 读取"参数实际使用分析"章节
       ↓
3. 筛选参数
   ├─ "✅ 被使用" → 进行 SQL 注入检测
   ├─ "❌ 未使用" → 跳过，不标记为漏洞
   └─ "⚠️ 部分使用" → 仅检测被使用的字段
       ↓
4. 对"被使用"的参数进行详细分析
       ↓
5. 输出审计结论

tracer output example and audit decision

Parameter	tracer output (actual usage status)	sql-audit decision
page.orderBy	❌ Not used (overridden by hardcoding)	⏭️ Skip, not detected
page.order	❌ Not used (overridden by hardcoding)	⏭️ Skip, not detected
searchBean.keyword	✅ Used (#{keyword} parameterized)	🔍 Detected → Safe (parameterized)
searchBean.status	✅ Used (${status} concatenated)	🔍 Detected → Vulnerability found

Reflection in the audit report

### [SQL-001] ORDER BY 注入分析

| 检查项 | 结果 |
|:-------|:-----|
| 拼接代码位置 | PaginationJdbcSupport.java:115 |
| 调用方法 | UserBulletinDaoImpl.getUserBulletinReplayList() |
| **tracer 参数分析** | page.orderBy → ❌ 未使用（被硬编码覆盖） |
| 硬编码详情 | `sql + " order by replayDateStr desc"` |
| **结论** | ❌ **非漏洞** - 参数未实际使用 |

---

### [SQL-002] WHERE 条件 SQL 注入

| 检查项 | 结果 |
|:-------|:-----|
| 拼接代码位置 | UserDaoImpl.java:89 |
| 调用方法 | UserDaoImpl.searchUsers() |
| **tracer 参数分析** | searchBean.status → ✅ 被使用（`${status}` 拼接） |
| 参数来源 | HTTP 请求 searchJson.status |
| **结论** | ✅ **确认漏洞** - 参数直接拼接到 SQL |

tracer output fields that must be checked

tracer output field	Purpose
实际使用状态	Determine whether the parameter needs to be detected
是否被硬编码覆盖	Eliminate false positives in hardcoded scenarios
敏感操作类型	Determine the detection rule (SQL/command/SSRF, etc.)
硬编码覆盖详情	Output to the audit report as evidence

Trigger conditions (automatic invocation)

Scenario	Action
Parameter is passed through multiple layers	Call java-route-tracer to trace the complete call chain
Parameter type conversion	Call java-route-tracer to trace the parameter conversion process
Parent class/base class SQL execution	Call java-route-tracer to trace the inheritance chain
Variable name changes multiple times	Call java-route-tracer to trace variable name changes
Execution conditions are unclear	Call java-route-tracer to analyze branch conditions

java-route-tracer collaboration content

Parameters passed when calling:

• --route: corresponding HTTP route (e.g., /api/users/getUserById）
• --project: project path (used for decompilation and code analysis)

java-route-tracer returns:

• Complete call chain information (L1 → L2 → L3 → L4)
• Parameter variable name changes at each layer
• Branch condition analysis of the execution path
• Final SQL usage point location
• HTTP request template (usable with Burp Suite)

Collaboration flow:

java-sql-audit                    java-route-tracer
     │                                   │
     │  发现 SQL 注入点                   │
     │  检测到需要深度追踪的场景          │
     │                                   │
     └───────── Skill 工具调用 ──────────→│
                                         │
                                         │  追踪完整调用链
                                         │  分析分支条件
                                         │  输出参数流向
                                         │
     ←───────── 自动返回结果 ─────────────┘
     │
     │  结合追踪结果标注可利用性
     ▼

---

Report generation

Output a single comprehensive audit report file:

{project_name}_audit/sql_audit/
└── {route_name}/
    └── {project_name}_sql_audit_{timestamp}.md      # 综合审计报告

Route name description:

• The route name is extracted from the route path, removing the leading slash and special characters
• For example:/biz/ws/userService → biz_ws_userService
• For example:/api/user/login.action → api_user_login

---

Output format

Strictly follow the references/OUTPUT_TEMPLATE.md fill-in template to generate the output file.

• File name format: {project_name}_sql_audit_{YYYYMMDD_HHMMSS}.md
• Do not modify the template structure, do not add or remove sections, and do not adjust the order
• All [fill in] placeholders must be replaced with actual content
• General specification reference: java-shared/OUTPUT_STANDARD.md
• Vulnerability aggregation rule reference: java-shared/VULNERABILITY_GROUPING.md, multiple entry points with the same root cause are merged into one vulnerability ID and the "affected entry points" are listed; entry points with different conditions are split.

---

Verification checklist

Before marking the audit as complete, the following checks must be performed:

Code analysis check

• All DAO/Mapper classes have been analyzed
• All SQL configuration files (*Mapper.xml) have been parsed
• Each SQL operation has a parameterization status annotation

Execute condition checks (CRITICAL)

• Checked database type branches (isOracle/isMySQL)
• Analyzed code path reachability
• Annotated the exploitability status of each vulnerability

Vulnerability detection checks

• All JDBC concatenation patterns have been detected
• All MyBatis ${} usages have been detected
• All Hibernate HQL concatenations have been detected

Report completeness checks

• The comprehensive audit report has been generated and passes the self-check checklist at the end of OUTPUT_TEMPLATE.md

---

References

• OUTPUT_TEMPLATE.md - Output report fill-in template
• JDBC.md - Detailed guide to JDBC SQL injection auditing
• MYBATIS.md - Detailed guide to MyBatis SQL injection auditing
• HIBERNATE.md - Detailed guide to Hibernate SQL injection auditing
• DECOMPILE_STRATEGY.md - Decompilation strategy guide