MCP Server MySQL

Instructions

Implementation Reference

• src/index.ts:150-171 (handler)

  The asynchronous handler function for the 'query' tool. It validates the SQL query to ensure it is read-only, executes the SELECT query using the MySQL connection pool with optional parameters, formats the results, and returns both text and structured content.

  async ({ sql, params }) => {
    // Validate that this is a read-only query
    validateReadOnlyQuery(sql);
  
    const p = await getPool();
    const [rows] = await p.query<RowDataPacket[]>(sql, params || []);
  
    const output = {
      rows: rows as Record<string, unknown>[],
      rowCount: rows.length,
    };
  
    return {
      content: [
        {
          type: "text" as const,
          text: JSON.stringify(rows, null, 2),
        },
      ],
      structuredContent: output,
    };
  }

• src/index.ts:146-149 (schema)

  Zod input schema for the 'query' tool defining the required 'sql' string parameter and optional 'params' array for prepared statements.

  {
    sql: z.string().describe("SQL SELECT query to execute"),
    params: z.array(z.unknown()).optional().describe("Query parameters for prepared statement"),
  },

• src/index.ts:143-172 (registration)

  Registration of the 'query' tool on the MCP server using server.tool(), including name, description, input schema, and handler function.

  server.tool(
    "query",
    "Execute a SELECT query and return results. Use this for reading data.",
    {
      sql: z.string().describe("SQL SELECT query to execute"),
      params: z.array(z.unknown()).optional().describe("Query parameters for prepared statement"),
    },
    async ({ sql, params }) => {
      // Validate that this is a read-only query
      validateReadOnlyQuery(sql);
  
      const p = await getPool();
      const [rows] = await p.query<RowDataPacket[]>(sql, params || []);
  
      const output = {
        rows: rows as Record<string, unknown>[],
        rowCount: rows.length,
      };
  
      return {
        content: [
          {
            type: "text" as const,
            text: JSON.stringify(rows, null, 2),
          },
        ],
        structuredContent: output,
      };
    }
  );

• src/index.ts:46-73 (helper)

  Helper function used by the 'query' handler to validate that the SQL statement is read-only by checking against a list of forbidden modifying keywords.

  function validateReadOnlyQuery(sql: string): void {
    const normalizedSql = sql.trim().toUpperCase();
  
    // List of forbidden keywords for read-only queries
    const forbiddenKeywords = [
      "INSERT",
      "UPDATE",
      "DELETE",
      "DROP",
      "CREATE",
      "ALTER",
      "TRUNCATE",
      "RENAME",
      "REPLACE",
      "GRANT",
      "REVOKE",
      "LOCK",
      "UNLOCK",
    ];
  
    for (const keyword of forbiddenKeywords) {
      if (normalizedSql.startsWith(keyword)) {
        throw new Error(
          `${keyword} operations are not allowed in query tool. Use the execute tool for data modifications or appropriate DDL tools for schema changes.`
        );
      }
    }
  }