Enable Custom SQL in Report Designer

• Aug 07, 2019
• 2 minutes to read

This document describes how to enable custom SQL editing on the query creation page of the SQL Data Source Wizard.

Important

For security reasons, enabling custom SQL editing is not recommended if your web reporting application can be accessed by untrusted parties. Refer to the General Security Considerations document for more information.

To enable custom SQL editing, call the static DefaultReportDesignerContainer.EnableCustomSql method on application start as shown in the code sample below.

C#

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

VB.NET

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

You can also accomplish this task by implementing an ISqlDataSourceWizardCustomizationService. The ISqlDataSourceWizardCustomizationService.IsCustomSqlDisabled property indicates whether or not custom SQL editing should be disabled by default.

C#

using DevExpress.DataAccess.Web;
// ...
public class CustomSqlDataSourceWizardCustomizationService : ISqlDataSourceWizardCustomizationService {
    public DevExpress.DataAccess.Wizard.Services.ICustomQueryValidator CustomQueryValidator {
        get { return new MyCustomQueryValidator(); }
    }

    public bool IsCustomSqlDisabled {
        get { return false; } // Enable custom SQL editing.
    }
}

VB.NET

Imports DevExpress.DataAccess.Web
' ...
Public Class CustomSqlDataSourceWizardCustomizationService
    Implements ISqlDataSourceWizardCustomizationService
    Public ReadOnly Property CustomQueryValidator() As DevExpress.DataAccess.Wizard.Services.ICustomQueryValidator
        Get
            Return New MyCustomQueryValidator()
        End Get

    public Boolean IsCustomSqlDisabled
        Get ' Enable custom SQL editing.
            Return False
        End Get
    End Property
End Class

To register custom SQL Data Source Wizard customization service, pass it as a type parameter to the static DefaultReportDesignerContainer.RegisterSqlDataSourceWizardCustomizationService<T> method on application start.

C#

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

VB.NET

Imports DevExpress.XtraReports.Web.ReportDesigner
' ...

Private Sub Application_Start(sender As Object, e As EventArgs)
    DefaultReportDesignerContainer.RegisterSqlDataSourceWizardCustomizationService(Of CustomSqlDataSourceWizardCustomizationService)()
End Sub

Important

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 execution of potentially harmful requests. For this reason, we strongly recommend that you implement your own validation logic that permits only execution of specific query kinds. To learn more, see the Provide Custom Query Validation in Report Designer document.