(mysql-mcp): (REPLACE() string function blocked as mutating operation in SELECT queries)

[shahar-shabtay-ni]

Describe the bug

Bug Report: REPLACE() Function Incorrectly Rejected in Readonly Mode

Summary

The MySQL MCP server incorrectly rejects SELECT queries containing the REPLACE() string function when readonly mode is enabled. The REPLACE() function is a safe, read-only MySQL string function that should be allowed in SELECT statements, but it's being blocked because REPLACE is included in the mutating keywords detection list.

Problem

When executing SELECT queries that use the REPLACE() string function (e.g., SELECT REPLACE(column, ' ', '_') FROM table), the MCP server rejects them with an error indicating mutating operations are not allowed, even though these are perfectly safe read-only queries.

Root Cause

The issue is in the mutating keyword detection logic. The file mutable_sql_detector.py includes REPLACE in the MUTATING_KEYWORDS set:

Problematic Code:

# Line 23 in mutable_sql_detector.py
MUTATING_KEYWORDS = {
    'INSERT',
    'UPDATE',
    'DELETE',
    'REPLACE',  # ← This catches both REPLACE statement AND REPLACE() function
    'TRUNCATE',
    # ... other keywords
}

The regex pattern MUTATING_PATTERN matches REPLACE as a word boundary, which incorrectly flags:

• ✅ REPLACE INTO table ... (correctly rejected - this is a mutating statement)
• ❌ SELECT REPLACE(column, 'old', 'new') FROM table (incorrectly rejected - this is a safe function)

Expected Behavior

SELECT queries using the REPLACE() string function should be allowed in readonly mode, as they are read-only operations that do not modify data.

Actual Behavior

SELECT queries containing REPLACE() are rejected with an error message indicating mutating operations are not allowed.

Example Queries

❌ Currently Rejected (but should be allowed):

-- Example: Simple string replacement in SELECT
SELECT REPLACE(product_name, 'Old', 'New') as normalized_name
FROM products
WHERE category = 'electronics';

-- Example 3: Multiple REPLACE functions
SELECT 
    REPLACE(REPLACE(email, '@old-domain.com', '@new-domain.com'), '.', '_') as normalized_email
FROM users;

✅ Correctly Rejected (mutating statement):

-- This should continue to be rejected
REPLACE INTO users (id, name) VALUES (1, 'John');

Suggested Fix

The detection logic should distinguish between:

1. REPLACE statement (mutating) - REPLACE INTO table ...
2. REPLACE() function (read-only) - SELECT REPLACE(column, ...) FROM table

Proposed Solution:

• Update the regex pattern to only match REPLACE when it's followed by INTO (indicating a REPLACE statement)
• Or exclude REPLACE from MUTATING_KEYWORDS and add a specific pattern for REPLACE INTO statements

Impact

This bug prevents users from:

• Normalizing string values in WHERE clauses
• Transforming data in SELECT statements
• Using common MySQL string manipulation functions in readonly queries

Environment

• MCP Server Version: Latest (commit 4a07e72)
• File: src/mysql-mcp-server/awslabs/mysql_mcp_server/mutable_sql_detector.py
• Line: 23 (MUTATING_KEYWORDS definition)

References

• MySQL REPLACE() Function Documentation
• MySQL REPLACE Statement Documentation
• Problematic code:

  mcp/src/mysql-mcp-server/awslabs/mysql_mcp_server/mutable_sql_detector.py

  Line 23 in 4a07e72

  'REPLACE',

Expected Behavior

Expected Behavior

When readonly mode is enabled, SELECT queries containing the REPLACE() string function should execute successfully. The REPLACE() function is a MySQL string manipulation function that:

• Does not modify database data
• Only transforms values in the query result set
• Is safe to use in readonly mode

Examples that should work:

• SELECT REPLACE(column, 'old', 'new') FROM table
• WHERE REPLACE(bucket_name, ' ', '_') = 'normalized_value'
• Any SELECT query using REPLACE() as a string function

Only REPLACE INTO statements (which modify data) should be rejected in readonly mode.

Current Behavior

## Actual Behavior SELECT queries containing `REPLACE()` are rejected with an error message indicating mutating operations are not allowed.

Reproduction Steps

Reproduction Steps - do SELECT query with REPLACE() inside it.
SELECT REPLACE(product_name, 'Old', 'New') as normalized_name
FROM products
WHERE category = 'electronics';

Possible Solution

Suggested Fix

Distinguish between REPLACE statement (mutating) and REPLACE() function (read-only):

1. Remove REPLACE from MUTATING_KEYWORDS set
2. Add specific regex pattern to detect REPLACE INTO statements only:

   REPLACE_STATEMENT_REGEX = re.compile(r'(?i)\bREPLACE\s+INTO\b')

3. Check for REPLACE INTO separately in detect_mutating_keywords() function

This allows REPLACE() function in SELECT queries while still blocking REPLACE INTO statements.

Even - REPLACE and the ( should be fine.

Additional Information/Context

No response

OS

linux

Server

mysql-mcp-server

Server Version

No response

Region experiencing the issue

us-east-1

Other information

No response

Service quota

• I have reviewed the service quotas for this construct