# Navicat HTTP Tunnel Protocol

This document is derived from the project’s actual tunnel script:

`ntunnel_mysql.php`

That file is Navicat 16’s `ntunnel_mysql.php` (script version **206**), plus a GET `token` gate. It is **not** a custom JSON/business API.

Wire format below is taken from the PHP source. Items that the PHP does not define are marked **TODO**.

## 1. Compatibility with a deployed `ntunnel_mysql.php`

**Compatible at the protocol layer**, with high confidence:

| Observation | Meaning |
|---|---|
| Same binary header (`magic=1111`, version field, errno) | Standard Navicat tunnel response |
| Same POST fields (`actn`, `host`, `port`, `login`, `password`, `db`, `q[]`) | Standard Navicat tunnel request |
| Comment in PHP: `Navicat URL: https://ntunnel.example.com/index.php?token=YOUR_SECRET` | The `?token=` URL is an intentional extension |
| `$allowTestMenu = false` | Browser GET does not show the HTML tester unless token + missing POST is handled as binary error |

The public hostname/path (with or without `/ntunnel_mysql.php`) is a deployment detail (reverse proxy / rewrite). The client must POST to the configured URL **including** the `token` query string.

`--test` POSTs `actn=C` then `actn=Q` with `SELECT 1` (implemented in Phase 2).

## 2. HTTP layer

| Item | Value | Source |
|---|---|---|
| Method | `POST` | tester JS `xmlhttp.open("POST", ...)` and `$_POST[...]` |
| Request `Content-Type` | `application/x-www-form-urlencoded` | tester JS |
| Response `Content-Type` | `text/plain; charset=x-user-defined` | PHP `header(...)` |
| Body | **binary**, not JSON | `EchoHeader` / `GetBlock` |
| URL query | `token` is **required** | `isValidAccessToken()` reads `$_GET['token']` only |
| Token location | Query string only. Not POST, not `Authorization` | PHP |
| TLS | Use HTTPS when the URL is `https://`. Do not disable certificate verification unless explicitly configured. | product requirement |

GET without POST:

- Missing/wrong token → HTTP `403` text `403 Forbidden: invalid or missing token`
- Valid token, `$allowTestMenu = false` → binary header errno **202** `invalid parameters`

Tunnel POST with missing/wrong token → HTTP 200 (typical PHP) with binary errno **204** `invalid access token`.

## 3. POST parameters

Required by PHP (`isset`):

- `actn`
- `host`
- `port`
- `login`

Used if present:

| Field | Role | Notes |
|---|---|---|
| `actn` | Action | Only `"C"` and `"Q"` are handled |
| `host` | MySQL host **as seen from the PHP server** | Often `127.0.0.1` / `localhost` / docker name `mysql` |
| `port` | MySQL port | Empty is allowed; mysqli then uses default port |
| `login` | Remote MySQL user | |
| `password` | Remote MySQL password | May be empty. PHP does not `isset`-check it |
| `db` | Default database | Empty string skips `mysqli_select_db` |
| `q[]` | SQL list | Used only when `actn=Q`. Empty items are skipped |
| `encodeBase64` | `"1"` to Base64-decode each `q[]` | Optional. Intended for WAF bypass |

**Not a POST field in this script:**

- `charset` — PHP hardcodes `mysqli_set_charset($conn, 'UTF8')` after connect
- session id / cookie — none
- close / disconnect action — none

**TODO:** If Yingdao needs `utf8mb4`, send `SET NAMES utf8mb4` as the first `q[]` (or a preceding query in the same HTTP request). Do not invent a `charset` POST field unless a later capture shows Navicat sending one (this PHP ignores unknown fields).

## 4. `actn` values

Observed in this file:

| `actn` | Meaning |
|---|---|
| `C` | Connect + return server info. No SQL. |
| `Q` | Connect + execute each `q[]` |

Any other `actn` (including missing after the `isset` check has passed): PHP still connects, writes `EchoHeader`, then writes **no** conn-info and **no** result set.

There is **no** `actn=D` / close. The HTTP request is the entire session.

**TODO:** Confirm whether the live Navicat client ever sends other `actn` values. This PHP would ignore them after a successful connect.

## 5. Session / transaction model

**Stateless.** Every POST:

1. Validates token
2. `mysqli_connect(...)`
3. `mysqli_set_charset(..., 'UTF8')`
4. optional `mysqli_select_db`
5. `C` or `Q`
6. Script ends; PHP drops the MySQL connection

Consequences (not assumptions — this is what the PHP does):

- `START TRANSACTION` / `COMMIT` **cannot** span two HTTP requests
- `SET` session variables do not persist across COM_QUERY unless we put them in the same POST `q[]` list
- `last_insert_id` is only meaningful inside that same HTTP response
- Local Yingdao connection state (`USE db`) must be stored **in mysql-tunnel** and sent as `db=` on every later request

Phase 1/2 client will therefore send `host/port/login/password/db` on **every** request.

## 6. Binary encoding primitives

All integers are **big-endian**.

```
GetLongBinary(n)  = pack("N", n)     // 4 bytes
GetShortBinary(n) = pack("n", n)     // 2 bytes
GetDummy(k)       = k zero bytes
```

`GetBlock(val)` (length-prefixed bytes, `strlen` = byte length):

| First byte | Meaning |
|---|---|
| `0x00`–`0xFD` | `len` then `len` bytes of data (`len < 254`) |
| `0xFE` | next 4 bytes = `len` (uint32 BE), then `len` bytes |
| `0xFF` | **NULL** (only used for row field values in `EchoData`) |

Empty string is `0x00` (length 0), not NULL.

**Limitation:** `pack("N", $insertid)` and `pack("N", $affectedrows)` are 32-bit. Values that do not fit are truncated/wrapped by PHP.

**TODO:** If a table uses `BIGINT AUTO_INCREMENT` beyond 2^32-1, the tunnel cannot return the full id. mysql-tunnel should surface the 32-bit value it actually received, not invent a 64-bit value.

## 7. Response: common header (`EchoHeader`)

Always 16 bytes, then an optional error block:

```
offset 0  uint32 BE  magic = 1111 (0x00000457)
offset 4  uint16 BE  version = 206 (0x00CE)   // this script; older copies used 202
offset 6  uint32 BE  errno
offset 10 6 bytes    zeros
offset 16 [GetBlock] error message if errno != 0
```

The built-in tester reads errno at offset 6 and the message at offset 16. mysql-tunnel must do the same.

Tunnel-level errno values defined by this PHP (not MySQL):

| errno | Message |
|---|---|
| 201 | unsupported php version |
| 202 | invalid parameters |
| 203 | MySQL not supported on the server |
| 204 | invalid access token |
| other | `mysqli_connect_errno` / `mysqli_errno` (real MySQL errors: 1045, 1049, 1146, 1064, ...) |

Parser should accept version **206** (this file) and **202** (older public copies). Reject unknown magic. **TODO:** whether to hard-fail on unexpected version numbers, or warn and continue if magic matches.

## 8. `actn=C` response

If header errno == 0:

```
GetBlock(host_info)     // e.g. "127.0.0.1 via TCP/IP"
GetBlock(proto_info)    // mysqli_get_proto_info is an int; PHP stringifies it, e.g. "10"
GetBlock(server_info)   // e.g. "8.0.36"
```

If header errno != 0: only the error `GetBlock` after the 16-byte header. No conn info.

## 9. `actn=Q` response

After a successful connect header (errno 0):

For each **non-empty** `q[i]`:

```
EchoResultSetHeader:
  uint32 BE errno
  uint32 BE affected_rows
  uint32 BE insert_id
  uint32 BE num_fields
  uint32 BE num_rows
  12 zero bytes

if errno > 0:
  GetBlock(mysql_error)
else if num_fields > 0:
  EchoFieldsHeader  (num_fields times)
  EchoData          (num_rows times)
else:
  GetBlock(mysqli_info) or GetBlock("")   // OK-style extra info string

separator:
  0x01 if this is not the last q[] index
  0x00 if this is the last q[] index
```

Empty `q[]` entries are `continue`’d **before** a result is written. If the last array slot is empty, the previous result may end with `0x01` and the stream never gets a final `0x00`.

**Implementation rule:** send only non-empty queries; prefer a single `q[]` per local `COM_QUERY`.

### Field header (`EchoFieldsHeader`, mysqli path)

Per column:

```
GetBlock(name)
GetBlock(table)
uint32 BE type     // mysqli_fetch_field_direct()->type  (MYSQLI_TYPE_*)
uint32 BE flags    // mysqli flags bitfield
uint32 BE length   // display/octet length
```

`MYSQLI_TYPE_*` values are the same numbers as MySQL protocol field types (e.g. `3=LONG`, `8=LONGLONG`, `246=NEWDECIMAL`, `253=VAR_STRING`, `245=JSON`). mysql-tunnel should copy these into Column Definition packets.

Legacy `mysql_field_type()` string mapping exists only on the old `mysql_*` path. Prefer treating incoming type as a numeric MySQL type.

### Row data (`EchoData`)

Per row, per field:

- SQL `NULL` → single byte `0xFF`
- otherwise → `GetBlock(byte string)`

`mysqli_fetch_row` returns strings (and NULL). **BIGINT and DECIMAL stay text.** Do not parse them as Go `int64` / `float64`.

## 10. Mapping to local MySQL protocol (later phases)

| Tunnel result | Local packet |
|---|---|
| `num_fields > 0` | Result Set: column count, column defs, rows, EOF/OK |
| `num_fields == 0` and errno 0 | OK packet: `affected_rows`, `last_insert_id` |
| query errno > 0 | ERR packet: SQLSTATE unknown unless we map common errno; message from `GetBlock` |
| connect errno > 0 | ERR packet to Yingdao (or fail `--test`) |
| HTTP 403 / non-binary body | Local ERR with a tunnel-layer message; never include token/password |

**TODO:** SQLSTATE is not in the tunnel response. Use `HY000` unless we add a local errno→SQLSTATE table.

**TODO:** `mysqli_info()` string (`Records: n Duplicates: m ...`) is not required by the MySQL OK packet. Can be ignored or logged.

## 11. SHOW / DESC / DML

This PHP runs **any** SQL string through `mysqli_real_query`. `SHOW DATABASES`, `SHOW TABLES`, `DESC t`, `SHOW COLUMNS`, `INSERT/UPDATE/DELETE/REPLACE` are all just `actn=Q`. mysql-tunnel should forward them unchanged.

`mysqli_real_query` + `mysqli_store_result` loads the full result into PHP memory. Large `SELECT`s become large HTTP bodies.

**TODO:** max result size / timeout behavior of the live gateway. Client-side `request_timeout` is our safety valve.

## 12. Sequence

```
Yingdao                    mysql-tunnel                 ntunnel_mysql.php              MySQL
  |                             |                              |                        |
  |-- TCP connect ------------->|                              |                        |
  |<-- Handshake ---------------|                              |                        |
  |-- Auth -------------------->|                              |                        |
  |<-- OK (local auth) ---------|                              |                        |
  |                             |                              |                        |
  |-- COM_QUERY SELECT 1 ------>|                              |                        |
  |                             |-- POST actn=Q + token ------>|                        |
  |                             |                              |-- connect ------------>|
  |                             |                              |-- SET charset UTF8 --->|
  |                             |                              |-- select_db ---------->|
  |                             |                              |-- real_query --------->|
  |                             |                              |<-- rows ---------------|
  |                             |<-- binary result set --------|                        |
  |<-- MySQL result set --------|                              |                        |
  |                             |                              |   (PHP connection dropped)
```

Connect check (`--test` / first use):

```
mysql-tunnel --test
  POST actn=C  (same URL + token + host/port/login/password/db)
    -> header errno 0 + ConnInfo  => Tunnel + Remote MySQL OK
  POST actn=Q  q[]=SELECT 1
    -> 1 field, 1 row            => Query OK
```

## 13. Open TODOs (do not guess)

1. Does a reverse proxy / WAF wrap or modify the PHP output (gzip, extra HTTP headers, HTML error pages)?
2. Does Navicat send `encodeBase64=1` by default? Default in mysql-tunnel: **off**, configurable.
3. Does Navicat send a `charset` POST field? This PHP ignores it.
4. Should `utf8mb4` be applied via `SET NAMES` in the same `q[]` batch? (Client already does this when charset is not utf8/utf8mb3.)
5. Multi-statement in one `COM_QUERY` (`a; b;`) vs one `q[]` vs multiple `q[]`.
6. `COM_INIT_DB` / `USE`: local session `db` vs extra SQL. Current behavior: update session `db` and send `db=` on later requests.
7. Prepared statements: tunnel has no binary protocol. Later: emulate prepare locally or send equivalent SQL.
8. `insert_id` / `affected_rows` 32-bit wrap.
9. Trailing `0x01` when the last `q[]` is empty.
10. Whether HTTP keep-alive to the PHP front-end is allowed (PHP itself is still one MySQL connect per request).

## 14. Implementation rule for mysql-tunnel

Speak **this** protocol only:

- Keep `token` on the URL query string
- POST `application/x-www-form-urlencoded`
- Parse binary `EchoHeader` / `GetBlock`
- Never log token, password, or the raw query string of the tunnel URL