Custom SQL Query in the Report Designer for Web

• May 27, 2025
• 3 minutes to read

Query Builder component users can create only secure SELECT SQL queries. You can allow users to create custom queries and edit SQL queries directly.

Important

The use of custom SQL queries can lead to inadvertent or unauthorized modifications to your data/database structure. The default validation mechanism only allows custom queries that contain SELECT statements (except for SELECT INTO clauses) and blocks any SQL keywords that can potentially be used for data modification (like REPLACE, UPDATE, INSERT, DELETE, and other SQL statements). Despite this precaution, this validation is not considered safe as it does not prevent the execution of potentially harmful requests.

We strongly recommend that you implement additional custom SQL query verification. However, do not use it as the only security precaution. Ensure that you follow best practices and implement the appropriate user read/write privileges at the database level.

Enable Custom SQL Query

To allow users to specify custom SQL queries in a reporting web application, use the following code:

ASP.NET Web Forms or ASP.NET MVC

Call the static DefaultReportDesignerContainer.EnableCustomSql method at application startup:

Global.asax.cs

using DevExpress.XtraReports.Web.ReportDesigner;
// ...
protected void Application_Start(object sender, EventArgs e) {
    DefaultReportDesignerContainer.EnableCustomSql();
    // ...
}

Global.asax.vb

Imports DevExpress.XtraReports.Web.ReportDesigner
' ...
Protected Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
    DefaultReportDesignerContainer.EnableCustomSql()
    ' ...
End Sub

ASP.NET Core

Call the ReportDesignerConfigurationBuilder.EnableCustomSql method at application startup:

Program.cs

using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDevExpressControls();
builder.Services.AddMvc(); 

builder.Services.ConfigureReportingServices(configurator => {
    configurator.ConfigureReportDesigner(designerConfigurator => {
        designerConfigurator.EnableCustomSql();
    });
});

builder.Services.ConfigureReportingServices(configurator => {
    if(builder.Environment.IsDevelopment()) {
        configurator.UseDevelopmentMode();
    }
    configurator.ConfigureReportDesigner(designerConfigurator => {
    });
    configurator.ConfigureWebDocumentViewer(viewerConfigurator => {
        // Use cache for document generation and export.
        // This setting is necessary in asynchronous mode and when a report has interactive or drill-down features.
        viewerConfigurator.UseCachedReportSourceBuilder();
    });
});

var app = builder.Build();

Write Custom SQL Query

Use Data Source Wizard

In the Data Source Wizard Settings page, when custom SQL queries are enabled, the plus button invokes a context menu. Users can choose whether to run the Query Builder or write a custom SQL query.

Use Field List

In the Field List click the button next to the data source name to invoke the Create a Query or Select a Stored Procedure dialog.

When custom SQL queries are enabled, users can type a custom query.

Custom SQL Query Validation

Custom SQL queries are validated before their execution.

Although the default validation mechanism only allows custom queries that contain SELECT statements (except for SELECT INTO clauses), it is not considered safe as it does not prevent execution of potentially harmful requests.

For this reason, we strongly recommend that you take the following actions:

• Implement validation logic that allows users to execute only queries that meet certain criteria.
• Implement the appropriate user read/write privileges at the database level.

See the following topic for details: Custom SQL Query Validation (Web).