query
Run SELECT queries on PostgreSQL databases securely using SQL commands and parameters. Explore schemas, analyze data, and retrieve results efficiently with this MCP tool.
Instructions
Execute a SELECT query
Input Schema
TableJSON Schema
| Name | Required | Description | Default |
|---|---|---|---|
| params | No | Query parameters (optional) | |
| sql | Yes | SQL SELECT query (use $1, $2, etc. for parameters) |
Implementation Reference
- Core handler function that validates input, executes the SQL query via model, logs performance, and formats results. This implements the main logic of the 'query' tool.
async executeQuery(query, params = []) { const startTime = Date.now(); try { // Validate input this.validateQueryInput(query, params); const results = await this.queryModel.executeQuery(query, params); logQuery(query, params); logPerformance('query_execution', Date.now() - startTime); return this.formatResults(results); } catch (error) { if (error instanceof ValidationError) { throw error; } throw new Error(`Query execution failed: ${error.message}`); } } - Input validation and schema checking for SQL queries, ensuring read-only, no injection risks, no unsafe functions.
validateQuery(sql) { if (!sql || typeof sql !== 'string') { return { isValid: false, errors: ['Query cannot be empty'] }; } const errors = []; // Check if query is read-only if (!this.isReadOnly(sql)) { errors.push('Only read-only queries are allowed'); } // Check for commenting out parts of the query (potential SQL injection techniques) if (/--.*$/m.test(sql)) { errors.push('SQL comments are not allowed'); } // Check for multiple statements (potential SQL injection) if (/;\s*\w+/i.test(sql)) { errors.push('Multiple SQL statements are not allowed'); } // Check for potentially unsafe functions const unsafeFunctions = [ /\bcopy\s*\(/i, /\bpg_read_file\s*\(/i, /\bpg_read_binary_file\s*\(/i, /\bpg_sleep\s*\(/i, /\bpg_terminate_backend\s*\(/i ]; for (const pattern of unsafeFunctions) { if (pattern.test(sql)) { errors.push('Query contains potentially unsafe functions'); break; } } return { isValid: errors.length === 0, errors }; } - mcp-psql/routes/query-routes.js:8-12 (registration)Registers the 'query' tool endpoint as POST /query with rate limiting and validation middleware.
router.post('/query', rateLimit('query', 100), validateQuery, queryController.executeQuery ); - mcp-psql/models/query-model.js:16-52 (helper)Database-level helper that executes the actual SQL query with timeout, row limits, and metadata extraction.
async executeQuery(sql, params = [], timeout = config.query.maxExecutionTime) { const client = await dbConnector.getClient(); try { // Set statement timeout await client.query(`SET statement_timeout TO ${timeout};`); const startTime = Date.now(); const result = await client.query(sql, params); const executionTime = Date.now() - startTime; // Limit the number of rows returned const limitedRows = result.rows.slice(0, config.query.maxRowsReturned); const rowsLimited = result.rows.length > config.query.maxRowsReturned; return { rows: limitedRows, rowCount: result.rowCount, fields: result.fields.map(field => ({ name: field.name, dataTypeID: field.dataTypeID, dataType: this._getDataTypeName(field.dataTypeID) })), metadata: { executionTime, rowsLimited, totalRows: result.rowCount, returnedRows: limitedRows.length, query: sql, params } }; } catch (error) { throw error; } finally { client.release(); }