/**
* Unit tests for executeSql.tool.ts - PARSE_STATEMENT validation
*
* Tests the validateWithParseStatement function's ability to:
* - Validate SQL syntax using IBM i's PARSE_STATEMENT
* - Enforce readonly mode restrictions
* - Fail closed on errors
*
* @module tests/unit/tools/executeSql.tool.test
*/
import { describe, it, expect, vi, beforeEach } from "vitest";
import {
JsonRpcErrorCode,
McpError,
} from "../../../src/types-global/errors.js";
import { createRequestContext } from "../../../src/utils/internal/requestContext.js";
// Mock the IBMiConnectionPool before importing the module
vi.mock("../../../src/ibmi-mcp-server/services/connectionPool.js", () => ({
IBMiConnectionPool: {
executeQuery: vi.fn(),
executeQueryWithPagination: vi.fn(),
},
}));
// Mock the logger
vi.mock("../../../src/utils/internal/logger.js", () => ({
logger: {
debug: vi.fn(),
info: vi.fn(),
warning: vi.fn(),
error: vi.fn(),
},
}));
// Now import the module after mocks are set up
import { IBMiConnectionPool } from "../../../src/ibmi-mcp-server/services/connectionPool.js";
import type { QueryResult } from "@ibm/mapepire-js";
// Helper to create mock QueryResult objects
function createMockQueryResult<T = unknown>(
data: T[] | null | undefined,
options?: {
success?: boolean;
sql_rc?: number;
execution_time?: number;
},
): QueryResult<T> {
return {
success: options?.success ?? true,
data: data as T[],
metadata: { column_count: 0, columns: [], job: "" },
has_results: (data && data.length > 0) ?? false,
update_count: 0,
id: "mock-query-id",
is_done: true,
sql_rc: options?.sql_rc ?? 0,
sql_state: "00000",
execution_time: options?.execution_time ?? 0,
} as QueryResult<T>;
}
// We need to access the private validateWithParseStatement function
// Since it's not exported, we'll need to test it through the executeSqlLogic function
// For now, let's create a standalone version for testing
// In a real scenario, you might export it for testing or use integration tests
/**
* Standalone version of validateWithParseStatement for testing
* This mirrors the implementation in executeSql.tool.ts
*/
async function validateWithParseStatement(
sql: string,
readOnly: boolean,
appContext: ReturnType<typeof createRequestContext>,
): Promise<void> {
const parseQuery = `
SELECT DISTINCT SQL_STATEMENT_TYPE
FROM TABLE(QSYS2.PARSE_STATEMENT(
SQL_STATEMENT => ?,
NAMING => '*SQL',
DECIMAL_POINT => '*PERIOD',
SQL_STRING_DELIMITER => '*APOSTSQL'
)) AS P
`.trim();
try {
const result = await IBMiConnectionPool.executeQuery(
parseQuery,
[sql],
appContext,
);
if (!result.data || result.data.length === 0) {
throw new McpError(
JsonRpcErrorCode.ValidationError,
"SQL syntax error: Query could not be parsed by IBM i",
{
query: sql.substring(0, 100) + (sql.length > 100 ? "..." : ""),
validationMethod: "parse_statement",
},
);
}
const firstRow = result.data[0] as { SQL_STATEMENT_TYPE?: string };
const statementType = (firstRow.SQL_STATEMENT_TYPE || "").toUpperCase();
if (readOnly && statementType !== "QUERY") {
throw new McpError(
JsonRpcErrorCode.ValidationError,
`Non-query statement '${statementType}' not allowed in read-only mode`,
{
query: sql.substring(0, 100) + (sql.length > 100 ? "..." : ""),
sqlStatementType: statementType,
readOnly: true,
validationMethod: "parse_statement",
},
);
}
} catch (error) {
if (error instanceof McpError) {
throw error;
}
throw new McpError(
JsonRpcErrorCode.ValidationError,
"SQL validation failed: Unable to execute PARSE_STATEMENT",
{
query: sql.substring(0, 100) + (sql.length > 100 ? "..." : ""),
validationMethod: "parse_statement",
originalError: error instanceof Error ? error.message : String(error),
},
);
}
}
describe("PARSE_STATEMENT Runtime Validation", () => {
const context = createRequestContext();
const mockExecuteQuery = vi.mocked(IBMiConnectionPool.executeQuery);
beforeEach(() => {
vi.clearAllMocks();
});
describe("Read-Only Mode - Valid Queries", () => {
it("should allow SELECT queries in readonly mode", async () => {
// Mock successful PARSE_STATEMENT result for SELECT
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
await expect(
validateWithParseStatement(
"SELECT * FROM QIWS.QCUSTCDT",
true,
context,
),
).resolves.toBeUndefined();
expect(mockExecuteQuery).toHaveBeenCalledWith(
expect.stringContaining("PARSE_STATEMENT"),
["SELECT * FROM QIWS.QCUSTCDT"],
context,
);
});
it("should allow complex SELECT with CTEs in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
const complexQuery = `
WITH sales_summary AS (
SELECT region, SUM(amount) as total
FROM sales
GROUP BY region
)
SELECT * FROM sales_summary WHERE total > 1000
`;
await expect(
validateWithParseStatement(complexQuery, true, context),
).resolves.toBeUndefined();
});
it("should allow SELECT with UNION in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
const unionQuery = `
SELECT name FROM employees
UNION
SELECT name FROM contractors
`;
await expect(
validateWithParseStatement(unionQuery, true, context),
).resolves.toBeUndefined();
});
});
describe("Read-Only Mode - Blocked Queries", () => {
it("should reject INSERT statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "INSERT" }]),
);
await expect(
validateWithParseStatement(
"INSERT INTO users (name) VALUES ('test')",
true,
context,
),
).rejects.toThrow(McpError);
try {
await validateWithParseStatement(
"INSERT INTO users (name) VALUES ('test')",
true,
context,
);
} catch (error) {
if (error instanceof McpError) {
expect(error.code).toBe(JsonRpcErrorCode.ValidationError);
expect(error.message).toContain("Non-query statement 'INSERT'");
expect(error.message).toContain("not allowed in read-only mode");
expect(error.details).toMatchObject({
sqlStatementType: "INSERT",
readOnly: true,
validationMethod: "parse_statement",
});
}
}
});
it("should reject UPDATE statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "UPDATE" }]),
);
await expect(
validateWithParseStatement(
"UPDATE users SET name = 'test' WHERE id = 1",
true,
context,
),
).rejects.toThrow(McpError);
});
it("should reject DELETE statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "DELETE" }]),
);
await expect(
validateWithParseStatement(
"DELETE FROM users WHERE id = 1",
true,
context,
),
).rejects.toThrow(McpError);
});
it("should reject MERGE statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "MERGE" }]),
);
await expect(
validateWithParseStatement(
"MERGE INTO target USING source ON target.id = source.id",
true,
context,
),
).rejects.toThrow(McpError);
});
it("should reject CREATE statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "CREATE" }]),
);
await expect(
validateWithParseStatement("CREATE TABLE test (id INT)", true, context),
).rejects.toThrow(McpError);
});
it("should reject DROP statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "DROP" }]),
);
await expect(
validateWithParseStatement("DROP TABLE test", true, context),
).rejects.toThrow(McpError);
});
it("should reject ALTER statements in readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "ALTER" }]),
);
await expect(
validateWithParseStatement(
"ALTER TABLE test ADD COLUMN name VARCHAR(50)",
true,
context,
),
).rejects.toThrow(McpError);
});
});
describe("Non-Read-Only Mode", () => {
it("should allow SELECT queries in non-readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
await expect(
validateWithParseStatement(
"SELECT * FROM QIWS.QCUSTCDT",
false,
context,
),
).resolves.toBeUndefined();
});
it("should allow INSERT statements in non-readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "INSERT" }]),
);
await expect(
validateWithParseStatement(
"INSERT INTO users (name) VALUES ('test')",
false,
context,
),
).resolves.toBeUndefined();
});
it("should allow UPDATE statements in non-readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "UPDATE" }]),
);
await expect(
validateWithParseStatement(
"UPDATE users SET name = 'test' WHERE id = 1",
false,
context,
),
).resolves.toBeUndefined();
});
it("should allow DELETE statements in non-readonly mode", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "DELETE" }]),
);
await expect(
validateWithParseStatement(
"DELETE FROM users WHERE id = 1",
false,
context,
),
).resolves.toBeUndefined();
});
});
describe("Syntax Error Handling", () => {
it("should reject queries with syntax errors (empty result)", async () => {
// Empty result from PARSE_STATEMENT indicates syntax error
mockExecuteQuery.mockResolvedValue(createMockQueryResult([]));
await expect(
validateWithParseStatement("SELECT * FROMM invalid", true, context),
).rejects.toThrow(McpError);
try {
await validateWithParseStatement(
"SELECT * FROMM invalid",
true,
context,
);
} catch (error) {
if (error instanceof McpError) {
expect(error.code).toBe(JsonRpcErrorCode.ValidationError);
expect(error.message).toContain("SQL syntax error");
expect(error.message).toContain("could not be parsed");
expect(error.details).toMatchObject({
validationMethod: "parse_statement",
});
}
}
});
it("should reject queries with malformed SQL", async () => {
mockExecuteQuery.mockResolvedValue(createMockQueryResult([]));
await expect(
validateWithParseStatement("INVALID SQL QUERY ;;;", true, context),
).rejects.toThrow(McpError);
});
it("should truncate long queries in error messages", async () => {
mockExecuteQuery.mockResolvedValue(createMockQueryResult([]));
const longQuery = "SELECT * FROM table WHERE " + "x = 1 AND ".repeat(50);
try {
await validateWithParseStatement(longQuery, true, context);
} catch (error) {
if (error instanceof McpError) {
expect(error.details?.query).toHaveLength(103); // 100 chars + "..."
expect(error.details?.query).toContain("...");
}
}
});
});
describe("Fail-Closed Error Handling", () => {
it("should fail closed on PARSE_STATEMENT execution error", async () => {
mockExecuteQuery.mockRejectedValue(new Error("Connection failed"));
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).rejects.toThrow(McpError);
try {
await validateWithParseStatement("SELECT * FROM users", true, context);
} catch (error) {
if (error instanceof McpError) {
expect(error.code).toBe(JsonRpcErrorCode.ValidationError);
expect(error.message).toContain("Unable to execute PARSE_STATEMENT");
expect(error.details).toMatchObject({
validationMethod: "parse_statement",
originalError: "Connection failed",
});
}
}
});
it("should fail closed on unexpected errors", async () => {
mockExecuteQuery.mockRejectedValue("Unexpected string error");
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).rejects.toThrow(McpError);
});
it("should re-throw McpError as-is", async () => {
const originalError = new McpError(
JsonRpcErrorCode.DatabaseError,
"Custom database error",
{ custom: "details" },
);
mockExecuteQuery.mockRejectedValue(originalError);
try {
await validateWithParseStatement("SELECT * FROM users", true, context);
// Should not reach here
expect.fail("Should have thrown an error");
} catch (error) {
// McpErrors are re-thrown as-is (not wrapped)
expect(error).toBe(originalError);
if (error instanceof McpError) {
expect(error.code).toBe(JsonRpcErrorCode.DatabaseError);
expect(error.message).toBe("Custom database error");
}
}
});
});
describe("Edge Cases", () => {
it("should handle queries with missing SQL_STATEMENT_TYPE", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{}]), // No SQL_STATEMENT_TYPE field
);
// Should treat empty string as non-QUERY in readonly mode
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).rejects.toThrow(McpError);
});
it("should handle lowercase statement types", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "query" }]), // lowercase
);
// Should convert to uppercase and match
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).resolves.toBeUndefined();
});
it("should handle null data in result", async () => {
mockExecuteQuery.mockResolvedValue(createMockQueryResult(null));
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).rejects.toThrow(McpError);
});
it("should handle undefined data in result", async () => {
mockExecuteQuery.mockResolvedValue(createMockQueryResult(undefined));
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).rejects.toThrow(McpError);
});
it("should handle SQL with trailing semicolons", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
// SQL with trailing semicolon should work (semicolons are sanitized before validation)
// Note: Sanitization happens in executeSqlLogic before calling validateWithParseStatement
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).resolves.toBeUndefined();
});
it("should handle SQL with multiple trailing semicolons", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
// Multiple semicolons should also work (sanitized in executeSqlLogic)
await expect(
validateWithParseStatement("SELECT * FROM users", true, context),
).resolves.toBeUndefined();
});
});
describe("PARSE_STATEMENT Query Structure", () => {
it("should use correct PARSE_STATEMENT parameters", async () => {
mockExecuteQuery.mockResolvedValue(
createMockQueryResult([{ SQL_STATEMENT_TYPE: "QUERY" }]),
);
await validateWithParseStatement("SELECT * FROM test", true, context);
// Verify the PARSE_STATEMENT query structure
const callArgs = mockExecuteQuery.mock.calls[0];
expect(callArgs[0]).toContain("PARSE_STATEMENT");
expect(callArgs[0]).toContain("SQL_STATEMENT =>");
expect(callArgs[0]).toContain("NAMING => '*SQL'");
expect(callArgs[0]).toContain("DECIMAL_POINT => '*PERIOD'");
expect(callArgs[0]).toContain("SQL_STRING_DELIMITER => '*APOSTSQL'");
expect(callArgs[0]).toContain("DISTINCT SQL_STATEMENT_TYPE");
expect(callArgs[1]).toEqual(["SELECT * FROM test"]);
});
});
});
