// Copyright (c) Microsoft Corporation.
// Licensed under the MIT License.
using System.CommandLine;
using System.CommandLine.Parsing;
using AzureMcp.Core.Commands;
using AzureMcp.Core.Services.Telemetry;
using AzureMcp.MySql.Commands;
using AzureMcp.MySql.Json;
using AzureMcp.MySql.Options;
using AzureMcp.MySql.Options.Database;
using AzureMcp.MySql.Services;
using Microsoft.Extensions.Logging;
namespace AzureMcp.MySql.Commands.Database;
public sealed class DatabaseQueryCommand(ILogger<DatabaseQueryCommand> logger) : BaseDatabaseCommand<DatabaseQueryOptions>(logger)
{
private const string CommandTitle = "Query MySQL Database";
private readonly Option<string> _queryOption = MySqlOptionDefinitions.Query;
public override string Name => "query";
public override string Description => "Executes a safe, read-only SQL SELECT query against a database on Azure Database for MySQL Flexible Server. Use this tool to explore or retrieve table data without modifying it. Rejects non-SELECT statements (INSERT/UPDATE/DELETE/REPLACE/MERGE/TRUNCATE/ALTER/CREATE/DROP), multi-statements, comments hiding writes, transaction control (BEGIN/COMMIT/ROLLBACK), INTO OUTFILE, and other destructive keywords. Only a single SELECT is executed to ensure data integrity. Best practices: List needed columns (avoid SELECT *), add WHERE filters, use LIMIT/OFFSET for paging, ORDER BY for deterministic results, and avoid unnecessary sensitive data. Example: SELECT id, name, status FROM customers WHERE status = 'Active' ORDER BY name LIMIT 50;";
public override string Title => CommandTitle;
public override ToolMetadata Metadata => new() { Destructive = false, ReadOnly = true };
protected override void RegisterOptions(Command command)
{
base.RegisterOptions(command);
command.AddOption(_queryOption);
}
protected override DatabaseQueryOptions BindOptions(ParseResult parseResult)
{
var options = base.BindOptions(parseResult);
options.Query = parseResult.GetValueForOption(_queryOption);
return options;
}
public override async Task<CommandResponse> ExecuteAsync(CommandContext context, ParseResult parseResult)
{
try
{
var options = BindOptions(parseResult);
if (!Validate(parseResult.CommandResult, context.Response).IsValid)
{
return context.Response;
}
IMySqlService mysqlService = context.GetService<IMySqlService>() ?? throw new InvalidOperationException("MySQL service is not available.");
List<string> result = await mysqlService.ExecuteQueryAsync(options.Subscription!, options.ResourceGroup!, options.User!, options.Server!, options.Database!, options.Query!);
context.Response.Results = result?.Count > 0 ?
ResponseResult.Create(
new DatabaseQueryCommandResult(result),
MySqlJsonContext.Default.DatabaseQueryCommandResult) :
null;
}
catch (Exception ex)
{
_logger.LogError(ex, "An exception occurred executing query.");
HandleException(context, ex);
}
return context.Response;
}
public record DatabaseQueryCommandResult(List<string> Results);
}
