execute-query
Execute SQL queries on a Microsoft SQL Server database using the MSSQL MCP Server. Specify queries and optional result limits to retrieve data securely and efficiently.
Instructions
執行 SQL 查詢語句
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | 結果筆數限制 (預設: 100) | |
| query | Yes | 要執行的 SQL 查詢語句 |
Implementation Reference
- src/index.ts:364-427 (handler)The primary handler function for the 'execute-query' tool. It checks connection, validates the query for safety using validateReadOnlyQuery, adds a TOP limit, executes the query via MSSQLManager.executeQuery, formats the result as a table using formatResultAsTable, and returns the output or error.
async ({ query, limit }) => { try { if (!mssqlManager.isConnected()) { return { content: [ { type: 'text' as const, text: '錯誤: 尚未連接到資料庫伺服器。請先使用 connect-database 工具建立連接。' } ] } } // 驗證查詢安全性 const validation = validateReadOnlyQuery(query) if (!validation.isValid) { return { content: [ { type: 'text' as const, text: `查詢被拒絕: ${validation.reason}` } ] } } // 加入 TOP 限制 const finalQuery = addTopLimit(query, limit) const result = await mssqlManager.executeQuery(finalQuery) if (result.recordset.length === 0) { return { content: [ { type: 'text' as const, text: '查詢執行成功，但沒有返回任何資料。' } ] } } // 使用改進的表格格式化 const tableOutput = formatResultAsTable(result.recordset, limit) return { content: [ { type: 'text' as const, text: `查詢執行成功:\n\n${tableOutput}` } ] } } catch (error) { return { content: [ { type: 'text' as const, text: `查詢執行失敗: ${formatError(error)}` } ] } } } - src/index.ts:359-362 (schema)Zod input schema for the 'execute-query' tool, defining the required 'query' string parameter and optional 'limit' number (default 100).
inputSchema: { query: z.string().describe('要執行的 SQL 查詢語句'), limit: z.number().optional().default(100).describe('結果筆數限制 (預設: 100)'), } - src/index.ts:354-428 (registration)MCP server registration of the 'execute-query' tool, including name, title, description, input schema, and handler function.
server.registerTool( 'execute-query', { title: '執行查詢', description: '執行 SQL 查詢語句', inputSchema: { query: z.string().describe('要執行的 SQL 查詢語句'), limit: z.number().optional().default(100).describe('結果筆數限制 (預設: 100)'), } }, async ({ query, limit }) => { try { if (!mssqlManager.isConnected()) { return { content: [ { type: 'text' as const, text: '錯誤: 尚未連接到資料庫伺服器。請先使用 connect-database 工具建立連接。' } ] } } // 驗證查詢安全性 const validation = validateReadOnlyQuery(query) if (!validation.isValid) { return { content: [ { type: 'text' as const, text: `查詢被拒絕: ${validation.reason}` } ] } } // 加入 TOP 限制 const finalQuery = addTopLimit(query, limit) const result = await mssqlManager.executeQuery(finalQuery) if (result.recordset.length === 0) { return { content: [ { type: 'text' as const, text: '查詢執行成功，但沒有返回任何資料。' } ] } } // 使用改進的表格格式化 const tableOutput = formatResultAsTable(result.recordset, limit) return { content: [ { type: 'text' as const, text: `查詢執行成功:\n\n${tableOutput}` } ] } } catch (error) { return { content: [ { type: 'text' as const, text: `查詢執行失敗: ${formatError(error)}` } ] } } } ) - src/database.ts:95-113 (helper)MSSQLManager.executeQuery method: executes the SQL query using the mssql library's ConnectionPool.request().query() and returns structured QueryResult.
async executeQuery(query: string): Promise<QueryResult> { if (!this.isConnected() || !this.pool) { throw new Error('尚未建立資料庫連接') } try { const request = this.pool.request() const result = await request.query(query) return { recordset: result.recordset || [], recordsets: result.recordsets ? (Array.isArray(result.recordsets) ? result.recordsets.map(rs => Array.from(rs)) : [Array.from(result.recordsets as any)]) : [], rowsAffected: Array.isArray(result.rowsAffected) ? result.rowsAffected : [result.rowsAffected || 0], output: result.output || {} } } catch (error) { throw new Error(`執行查詢失敗: ${error instanceof Error ? error.message : String(error)}`) } } - src/utils.ts:50-132 (helper)Utility function that validates the SQL query for read-only compliance, blocks dangerous keywords, checks permissions via environment variables, and prevents multi-statement queries.
export function validateReadOnlyQuery(query: string): { isValid: boolean; reason?: string } { const trimmedQuery = query.trim().toLowerCase() // 檢查是否為空查詢 if (!trimmedQuery) { return { isValid: false, reason: '查詢語句不能為空' } } // 檢查是否包含分號後的額外語句（防止 SQL 注入） const statements = query.split(';').filter(stmt => stmt.trim()) if (statements.length > 1) { return { isValid: false, reason: '不允許執行多個 SQL 語句' } } // 檢查環境變數權限設定 const ALLOW_INSERT = process.env.MSSQL_ALLOW_INSERT === 'true' const ALLOW_UPDATE = process.env.MSSQL_ALLOW_UPDATE === 'true' const ALLOW_DELETE = process.env.MSSQL_ALLOW_DELETE === 'true' const DANGER_MODE = process.env.MSSQL_DANGER_MODE === 'true' // 如果啟用了 danger mode，允許所有操作 if (DANGER_MODE) { process.stderr.write('[WARNING] Danger mode is enabled - allowing all SQL operations\n') return { isValid: true } } // 定義永遠危險的關鍵字（即使在 danger mode 下也應該謹慎） const alwaysDangerousKeywords = [ 'drop', 'truncate', 'alter', 'create', 'grant', 'revoke', 'deny', 'execute', 'exec', 'sp_', 'xp_', 'backup', 'restore', 'rename' ] // 定義可選權限關鍵字 const permissionKeywords = { insert: { allowed: ALLOW_INSERT, keyword: 'insert' }, update: { allowed: ALLOW_UPDATE, keyword: 'update' }, delete: { allowed: ALLOW_DELETE, keyword: 'delete' }, merge: { allowed: ALLOW_INSERT && ALLOW_UPDATE, keyword: 'merge' }, replace: { allowed: ALLOW_INSERT && ALLOW_DELETE, keyword: 'replace' } } // 檢查永遠危險的關鍵字 for (const keyword of alwaysDangerousKeywords) { const pattern = new RegExp(`\\b${keyword}\\b`, 'i') if (pattern.test(trimmedQuery)) { return { isValid: false, reason: `查詢包含高危險關鍵字: ${keyword.toUpperCase()}。此操作被禁止。` } } } // 檢查需要權限的關鍵字 for (const [key, config] of Object.entries(permissionKeywords)) { const pattern = new RegExp(`\\b${config.keyword}\\b`, 'i') if (pattern.test(trimmedQuery)) { if (!config.allowed) { return { isValid: false, reason: `查詢包含 ${config.keyword.toUpperCase()} 操作，但未授予此權限。請設定環境變數 MSSQL_ALLOW_${key.toUpperCase()}=true 來啟用。` } } // 如果有權限，記錄警告 process.stderr.write(`[INFO] Executing ${config.keyword.toUpperCase()} operation with permission\n`) return { isValid: true } } } // 檢查是否為 SELECT 查詢 if (!trimmedQuery.startsWith('select') && !trimmedQuery.startsWith('with')) { // 如果不是 SELECT 但也不包含上述關鍵字，可能是其他查詢類型 return { isValid: false, reason: '僅允許 SELECT 查詢、WITH 子句查詢，或已授權的 DML 操作' } } return { isValid: true } }