Why Read-Only SQL Filters Are Not a Security Boundary

A SQL-prefix filter is input validation, not a security boundary. The database account, reachable hosts, credential storage, result cache, retention policy, and process identity determine what a supposedly read-only MCP server can expose.

The source is public at ttpears/mysql-mcp. I maintain the project. I audited version 3.1.0 at commit 824b546b, then shipped the cache fixes in version 3.1.1 at merge commit 7da7aa18. This is still a source audit, not a report from a production deployment.

What the server is trying to do

The project gives an MCP client access to MySQL schema inspection, read queries, table analysis, inventory, and cross-host correlation. One process can register a default database plus named hosts for systems such as CRM, support, marketing, or commerce. It can then return rows and analysis to an assistant.

That is useful precisely because the data is valuable. It is also why “the assistant cannot send UPDATE” is not a complete threat model. The process holds database credentials, sees returned rows, can query more than one system, and may keep copies on disk after the MCP response is gone.

The query check is a guardrail, not authorization

The application check is short. It trims the query, lowercases it, and accepts the request when the resulting text starts with one of five prefixes:

select
show
describe
explain
desc

That is useful feedback for ordinary tool calls. It rejects an obvious write before opening the connection, and the same check runs for each query in the cross-host tool. The server also rejects query strings longer than 10,000 characters.

It is not a SQL parser. It does not prove what a stored function does, reason about every form of server-side behavior, or inspect the effective privileges of the account. It also rejects legitimate read statements beginning with syntax outside the allowlist. I would treat the function as a narrow product rule, not the control that protects the database.

The current mysql2 dependency disables multiple statements by default, and this project does not enable them. That reduces one obvious class of surprises. It still does not turn a string-prefix test into database authorization.

The MySQL account is what stops a write

MySQL checks privileges for each request after accepting a connection. That is the control that must survive an application mistake. The project README recommends a dedicated account with SELECT and SHOW VIEW on the intended database. I agree with the direction, but I would make the scope more explicit than a copied example.

I would give the MCP process a distinct account, restrict it to the required schemas or tables, avoid global administrative privileges, limit the connecting host or network where practical, and run SHOW GRANTS as part of deployment verification. If cross-host analysis needs three databases, I would use three independently scoped credentials rather than one powerful account copied everywhere.

This is also why the source default deserves attention. When no username is supplied, the configuration falls back to root. A missing password may prevent a connection, but “the connection probably fails” is not a safety policy. I would make the account explicit before starting the process.

Version 3.1.0 added a convenient credential path

The release added support for MySQL option-file credentials. At startup, the server parses ~/.my.cnf and can use the ordinary [client] section for the default host. It also discovers named sections using forms such as [client-crm] and [client_crm].

The precedence is clear in the source: environment variables first, supplied configuration second, the option file third, and hardcoded defaults last. The process prints a conspicuous warning listing the file and sections it found. If any parsed section contains a password, the warning says those passwords will be used. Setting MCP_MYSQL_IGNORE_MY_CNF=true disables the fallback.

I added that warning because an option file is broader than one MCP configuration block. It may exist for a completely different command-line workflow. Starting the server under an established Unix account can therefore expose credentials the person launching the MCP client did not consciously select for this tool.

The warning is disclosure, not isolation. The process can still read whatever its operating-system identity can read. I want a dedicated service identity, an intentional home directory, and only the credential sections required for that deployment.

The cache is another copy of the database

Caching is enabled by default. After a query, the server writes a JSON file below ~/.mcp-mysql-cache. The object includes server and database context, the SQL string, the supplied parameters, the returned rows, timing information, and generated analysis. Schema scans and cross-host results also have cache paths.

That means a successful read does not end when the MCP client receives its answer. A second copy may now exist on the filesystem. The default maximum is 50 MB per cache file, not a total cache quota.

Version 3.1.0 created directories recursively and wrote files without passing an explicit mode. Node therefore applied its file-creation defaults and the process environment’s mask on systems that use POSIX permissions. Version 3.1.1 now creates and corrects cache directories to mode 0700 and cache files to 0600 on POSIX systems. I would still inspect the deployed directory because filesystem mounts, ACLs, backups, and the process identity sit outside this package.

The filename contains a timestamp, host and database context, operation name, and query-derived hash. The full SQL and rows remain inside the file. Hashing part of a filename is not redaction.

Version 3.1.0 did not enforce the 30-day retention setting

The old configuration read MCP_MYSQL_CACHE_RETENTION_DAYS, stored it as retentionDays, and defaulted it to 30. Nothing used that value after assignment. Individual cache reads could ignore an expired result, but ignoring a file is not deleting it. The raw JSON stayed on disk.

I fixed that in pull request #2. Version 3.1.1 walks the cache when the server starts and removes regular .json files older than the configured retention period. It does not follow symbolic links, does not touch other file types, and refuses an invalid retention value before scanning.

The same change added four focused tests: recursive expiry, symbolic-link handling, invalid-retention rejection, and POSIX directory/file modes. The pull-request build passed, the post-merge Linux job ran npm test and npm run build, and the release workflow published 3.1.1 to npm. That closes the source defect. It does not turn a cache of database rows into harmless data.

Multi-host access multiplies the blast radius

The cross-host feature is attractive because it can correlate results from separate business systems. It also places several credential sets and several data domains inside one process. A query result can combine identifiers from systems that normally have different administrators, retention rules, and access expectations.

I would not treat “read-only on every host” as permission to join every host. The questions are narrower:

• Which named source is required for this assistant?
• Which schemas and tables are required on that source?
• Can returned columns contain credentials, personal data, tokens, or regulated records?
• May data from two sources be combined in one response?
• May that combined result be written to the local cache?
• Who can read the process output, logs, configuration, and cache directory?

My preflight checklist

Why the next step was code, not spending

The server is MIT-licensed, and fixing retention and local permissions did not require buying a product. The next useful investment is engineering time: add tests around query policy and credential discovery, document a production-safe account example, and run the package against a disposable MySQL instance before calling the deployment guidance complete.

I would not recommend a hosted database, security product, or paid MCP platform from this source audit. I did not compare vendors, price operations, or run a production workload. There are no affiliate links here.

What the source audit did not test

I inspected version 3.1.0 at commit 824b546b315d1f6a5f3d3e3f6b89dbe37da29f59, installed its declared dependencies with pnpm without changing the lockfile, and compiled the TypeScript successfully. That version defined no test script. I then reviewed and merged the retention and permissions change at commit 7da7aa1891b0b9e88ec96ce6fe2592bcc90e453c. Version 3.1.1 has four cache-focused tests; GitHub’s exact lockfile-based Linux workflow passed both the tests and TypeScript build before the npm release. I did not connect to MySQL, load a real option file, execute a query, inspect a deployed cache directory, test TLS, run a container, simulate an attack, or benchmark a database.

Database privilege claims are checked against the official MySQL request-verification documentation, privilege reference, and option-file documentation. Filesystem behavior is bounded by the official Node.js filesystem documentation. Product-specific findings come from the repository source, README, package metadata, and commit history.

Disclosure: I maintain MySQL MCP.