mysql-tunnel/docs/PROTOCOL.md

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 SELECTs 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