# AS/400 MCP Server

[![tests](https://github.com/LorenteFacundo/python-mcp-as400/actions/workflows/tests.yml/badge.svg)](https://github.com/LorenteFacundo/python-mcp-as400/actions/workflows/tests.yml)
[![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-blue.svg)](https://www.python.org/downloads/)
[![License: MIT](https://img.shields.io/badge/License-MIT-green.svg)](LICENSE)
[![MCP](https://img.shields.io/badge/MCP-compatible-purple.svg)](https://modelcontextprotocol.io/)

**English** · [Español](README.es.md)

An [MCP](https://modelcontextprotocol.io/) server that lets AI assistants (Claude, Cursor, any MCP client) work with **IBM i (AS/400)** systems: query Db2 for i, read RPG/COBOL/CL source, compile programs and read spooled files. Every input goes through strict validation, so the assistant can analyze everything without being able to break anything.

## Why I built it

A lot of critical business logic still runs on IBM i, and the people who know it are few and busy. Understanding the impact of a change means reading thousands of tables and programs by hand. I wanted an AI assistant to do that reading for me, but connecting an LLM to a production system is only acceptable if it **cannot** change data or objects. This server is that bridge.

## Results

I presented it at BDT Global, where it was approved as a billable initiative and piloted with a client:

| | Manual | With the MCP server |
|---|---|---|
| Task estimated by a senior developer | 5 days | **3 hours** (13x) |
| Impact analysis across **6,000+ tables** | 6 hours | **20 minutes** (18x), and it found 2 tables missed by hand |

## Tools

| Tool | What it does | Example |
|---|---|---|
| `execute_sql` | Read-only SQL (`SELECT`, `WITH`, `VALUES`) | `SELECT * FROM QSYS2.SYSTABLES FETCH FIRST 5 ROWS ONLY` |
| `execute_cl_command` | Allow-listed CL commands | `DSPFD FILE(MYLIB/MYFILE) OUTPUT(*OUTFILE) OUTFILE(QTEMP/FDA)` |
| `read_source_member` | Reads a source member | `biblioteca='MYLIB', archivo_fuente='QRPGLESRC', miembro='MYPGM'` |
| `compile_program` | Compiles RPG, CL, COBOL or C | `library='DEVLIB', source_file='QRPGLESRC', member='MYPGM'` |
| `read_spoolfile` | Lists or reads spooled files (compile listings, job logs) | `program_name='MYPGM'` |

## Security model

The philosophy: **analysis and compilation yes, destruction never.** Validation lives in [`validation.py`](validation.py), has no dependencies, and is covered by [72 tests](tests/test_validation.py) that run in CI on every push.

**Read-only SQL**
- Only `SELECT`, `WITH` and `VALUES`; no `;`, so no statement stacking.
- Blocks data-change table references hidden inside a `SELECT` (`SELECT * FROM FINAL TABLE (DELETE ...)`).
- Blocks Db2 for i functions with side effects: `QCMDEXC` (runs any CL command), `HTTP_*` / `HTTP*CLOB` (data exfiltration), `IFS_WRITE*`, `END_JOB` and others.
- Ignores string literals and quoted identifiers when checking keywords, so `WHERE MSG = 'DELETE'` or a `LAST_UPDATE` column still work.
- Adds `FETCH FIRST 1000 ROWS ONLY` when there is no limit.

**CL commands: strict allow list**
- Allowed: `DSP*`, `WRK*`, `CHKOBJ`, compilation (`CRTBNDRPG`, `CRTSQLRPGI`, `CRTBNDCBL`, ...), library list and overrides.
- Everything else is rejected: `DLTOBJ`, `DLTLIB`, `CLRPFM`, `RUNSQLSTM`, `SBMJOB`, `CALL`, `GRTOBJAUT`, ...
- Qualified commands (`QSYS/DLTLIB`) are rejected.
- `OUTFILE(...)` must point to `QTEMP`, so `DSP*` commands cannot create or replace files in real libraries.

**Everything else**
- Library, file, member, user and spooled-file names are validated against the IBM i naming rules before they reach any SQL.
- Error messages never include credentials or connection strings.

> **Defense in depth.** These checks reduce risk, they don't replace IBM i security. Connect with a user profile that only has the authorities it needs (ideally `*USE` on the data and a development library for compiles).

### Found by the tests

Writing the test suite (v1.1) exposed real gaps in v1.0, now fixed:
- `read_spoolfile` concatenated `user` and `spoolfile_name` into SQL. Combined with `QSYS2.QCMDEXC`, that allowed running any CL command.
- `execute_sql` accepted `VALUES QSYS2.QCMDEXC(...)`, `FINAL TABLE (DELETE ...)` and `HTTP_POST(...)`.
- `DSP*` commands could write their `OUTFILE` to any library.

## Getting started

**Requirements:** Python 3.10+, the [IBM i Access ODBC Driver](https://www.ibm.com/support/pages/ibm-i-access-client-solutions), and a user on an IBM i system.

```bash
git clone https://github.com/LorenteFacundo/python-mcp-as400.git
cd python-mcp-as400
python -m venv venv
source venv/bin/activate          # Windows: venv\Scripts\activate
pip install -e .
cp .env.example .env              # then fill in DB_HOST, DB_USER, DB_PASS
```

The server talks MCP over stdio. Add it to your client, for example Claude Desktop (`claude_desktop_config.json`):

```json
{
  "mcpServers": {
    "as400": {
      "command": "python",
      "args": ["/path/to/python-mcp-as400/server.py"],
      "env": {
        "DB_HOST": "your-ibmi-host.com",
        "DB_USER": "YOUR_USER",
        "DB_PASS": "YOUR_PASSWORD"
      }
    }
  }
}
```

Or with Claude Code: `claude mcp add as400 -- python /path/to/python-mcp-as400/server.py`

## Example prompts

- *"Which tables in MYLIB have a column called CUSTNO?"* → `execute_sql` over `QSYS2.SYSCOLUMNS`
- *"Which programs use the file CUSTOMERS?"* → `DSPPGMREF ... OUTFILE(QTEMP/REFS)` + `execute_sql`
- *"Explain what CALCPRICE does"* → `read_source_member`
- *"Compile CALCPRICE and tell me what failed"* → `compile_program` + `read_spoolfile`

## Development

```bash
pip install -e ".[dev]"
pytest
```

| File | Purpose |
|---|---|
| `server.py` | MCP tools and the ODBC connection |
| `validation.py` | Security checks (no dependencies) |
| `tests/` | Tests for every check, including real attack strings |

## License

[MIT](LICENSE)