Apache Doris MCP Server 0.6.0 Bugs Discussion
Hey everyone,
I've encountered a few bugs in Apache Doris MCP Server version 0.6.0, and I wanted to share my findings and discuss potential solutions. I've managed to resolve some of these issues, but others still need attention. Let's dive in!
1. SQL Injection Protection Misjudgments
So, the first bug I found is related to SQL injection protection. Basically, the system sometimes incorrectly flags valid SQL queries as potential SQL injection attempts. This can happen when your SQL statements include comments using -- or even in common WHERE clause scenarios like where dt between '2025-07-01' and '2025-07-31'. To address this, I've tinkered with the code, specifically the regex matching logic found in doris_mcp_server/utils/security.py.
Diving Deeper into the Issue
The current approach uses regular expressions to identify potential SQL injection attempts. While regex can be useful, it's not foolproof. In fact, it can be quite challenging to create a regex pattern that accurately identifies all malicious SQL while also allowing legitimate queries. This is why the system sometimes misinterprets valid SQL, leading to false positives.
Consider the example of comments (--). A simple regex might flag any occurrence of -- as a potential injection attempt. However, comments are a perfectly valid part of SQL syntax and are often used to improve readability and maintainability. Similarly, date range queries using BETWEEN are very common, and a regex that's too strict might flag these as well.
Why Regex Isn't Always the Best Solution
The main problem with using regex for SQL injection protection is that it operates on the textual representation of the SQL query. It doesn't actually understand the SQL. This means it can be easily tricked by clever attackers who can craft malicious SQL that bypasses the regex filter. A more robust approach would involve parsing the SQL and understanding its structure and intent.
Moving Towards a Better Solution
In my opinion, a better approach to SQL injection protection would involve using a proper SQL parser. A parser can break down the SQL query into its individual components (e.g., keywords, operators, table names, column names) and identify potentially malicious patterns. This approach is much more accurate and less prone to false positives. Alternatively, parameterized queries (also known as prepared statements) can be used. These allow you to pass data separately from the SQL query, preventing attackers from injecting malicious code.
2. Token-Based Authorization Failures
Next up, we've got some trouble with token-based authorization. Here's the deal: I configured database user "a" in the .env file and enabled token-based authorization. Then, I configured database user "b" in the tokens.json file. The expectation is that when using the exec_query tool with the token associated with user "b," it should use user "b"'s credentials. However, it consistently defaults to user "a." So, user "b," despite being bound to the token, is not being used, which is definitely not the intended behavior.
Understanding the Authorization Flow
To really get to the bottom of this, it's important to understand how the authorization flow should work. When token-based authorization is enabled, the system should first check for a valid token. If a valid token is found, it should then identify the user associated with that token and use that user's credentials for database access. The configuration in tokens.json is meant to map tokens to specific users.
Potential Causes of the Issue
There are several potential reasons why this might be failing. It could be a problem with how the token is being extracted from the request, or how it's being validated against the tokens.json file. It's also possible that there's a precedence issue, where the user configured in .env is taking priority over the token-based user.
Debugging Steps
To debug this, I'd recommend a few steps. First, verify that the token being sent with the exec_query request is actually the correct token associated with user "b" in tokens.json. Then, trace the code execution to see how the system is handling the token and identifying the user. Pay close attention to the logic that reads the .env file and the tokens.json file, and how it prioritizes them.
Importance of Secure Authorization
Correctly implemented authorization is crucial for the security of any system. If users are not being authenticated and authorized properly, it can lead to serious security vulnerabilities, such as unauthorized access to data or the ability to execute malicious commands. This is why it's so important to ensure that token-based authorization is working as expected.
3. db_name Parameter Ignored in exec_query Tool
Moving on, I've noticed that when using the exec_query tool, the db_name parameter seems to be ignored. No matter what db_name I specify, it always defaults to the db_name configured in the .env file. This makes it impossible to query different databases using the tool, which is a major limitation.
Why This Parameter is Important
The db_name parameter is essential for multi-tenant systems or scenarios where you need to access multiple databases from the same application. Without the ability to specify the database name, you're effectively limited to working with a single database, which significantly reduces the flexibility and usefulness of the exec_query tool.
Identifying the Root Cause
To understand why this is happening, we need to examine the code that handles the db_name parameter in the exec_query tool. It's likely that there's a bug in the logic that reads or uses this parameter. It might be that the parameter is not being parsed correctly, or that the default value from .env is always being used regardless of the provided parameter.
Suggested Fix
A possible fix would be to ensure that the code first checks if the db_name parameter is provided in the exec_query call. If it is, that value should be used. Only if the parameter is not provided should the system fall back to the default value from .env.
4. Inconsistent MCP Server Port Configuration
This one's a bit of a confusing issue. There's an inconsistency between the MCP Server port specified in start_server.sh and the one in .env. The start_server.sh script still uses MCP_PORT to launch the Doris MCP Server, so when you check the process using ps -aux | grep mcp, it shows --port 3000 even if you've changed the port to, say, 4000 in .env. While the actual port used is indeed 4000 (because the code uses SERVER_PORT), the displayed process information can be misleading.
The Importance of Consistency
Inconsistencies like this can lead to confusion and make troubleshooting more difficult. If the displayed port doesn't match the actual port, it can waste time trying to debug issues that don't exist or overlooking the real problem.
A Simple Fix
The solution here is quite straightforward: update the start_server.sh script to use SERVER_PORT instead of MCP_PORT when launching the server. This will ensure that the displayed port matches the actual port being used.
5. Queries Starting with WITH Return No Results
Lastly, I've discovered that SQL queries starting with the WITH clause don't return any results. This is because the code in doris_mcp_server/utils/db.py is filtering these results out. The WITH clause is commonly used for Common Table Expressions (CTEs), which are a powerful way to simplify complex queries, so this is a significant limitation.
Why CTEs Are Important
Common Table Expressions (CTEs) allow you to define temporary named result sets within a single SQL statement. This can make complex queries much easier to read, write, and maintain. They're especially useful when you need to reuse the same subquery multiple times within a larger query.
The Filtering Code
The code that's causing this issue is likely intended to filter out certain types of queries or results. However, it's too broad and is inadvertently filtering out queries that use CTEs. The specific code snippet mentioned in the original post (https://github.com/apache/doris-mcp-server/blob/2613912df3c1531473e23c543aedd5dfadd8eae6/doris_mcp_server/utils/db.py#L99) should be reviewed to understand why it's filtering these queries and whether the filtering logic can be adjusted to allow CTEs.
Removing the Limitation
The ideal solution would be to modify the filtering logic so that it doesn't block queries starting with WITH. This would allow users to take full advantage of CTEs and write more complex and efficient SQL queries.
Conclusion
So, there you have it – my findings on these five bugs in Apache Doris MCP Server 0.6.0. I've already addressed some of these, but others require further investigation and solutions. I hope this discussion sparks some ideas and helps us collectively improve the stability and functionality of the MCP Server. Let's work together to make Apache Doris even better!
Thanks for reading, and please share your thoughts and experiences in the comments below! Let's discuss these issues and find the best solutions together. Happy coding, guys!