Custom SQL Query in Report Designer for Web

• Apr 19, 2024
• 3 minutes to read

Users that use the built-in Query Builder component can create only secure SELECT SQL queries. You can allow users to create custom queries and edit SQL queries directly.

Important

Unrestricted execution of custom queries enables your users to voluntarily modify a connected database. Avoid enabling this option unless you are certain about the expected results.

Custom SQL queries are validated before their execution. Although the default validation mechanism only allows custom queries containing SELECT statements (except for SELECT INTO clauses), it cannot be considered safe as it does not prevent the execution of potentially harmful requests. We recommend that you implement your own validation logic that permits only the execution of specific query types.

Do not reduce the default restrictions when implementing query validation.

To enable unrestricted execution of custom queries (for example, those containing DELETE, INSERT, PROCEDURE, and UPDATE statements), set the static SqlDataSource.DisableCustomQueryValidation property to true.

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 start:

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 on application start:

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 a 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 execution. The default validation mechanism allows only queries with SELECT statements (except for SELECT INTO clauses) but does not prevent execution of potentially harmful requests. To improve security, implement validation logic that allows users to execute only queries that meet certain criteria. Review the following topic for information on how to implement custom validation: Custom SQL Query Validation (Web).