Provide Custom Query Validation in Report Designer

• Jul 31, 2018
• 4 minutes to read

This document describes how to provide custom validation for SQL queries. Note that only custom SQL queries manually written by an end user when custom SQL editing is enabled are validated. For more information, refer to the General Security Considerations document.

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 does not reduce the default restrictions and permits only execution of specific query kinds.

Validation of Custom Queries Written in the Data Source Wizard

When custom SQL editing is enabled, end-users can manually write SQL queries in the SQL Data Source Wizard. You can provide custom validation for such queries by creating a custom wizard customization service implementing the ISqlDataSourceWizardCustomizationService interface. The ISqlDataSourceWizardCustomizationService.CustomQueryValidator property returns an object implementing the ICustomQueryValidator interface, which defines the required validation logic.

C#

using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Wizard.Services;
using DevExpress.DataAccess.Web;
// ...

public class MyCustomValidator : ICustomQueryValidator {
    public bool Validate(DataConnectionParametersBase connectionParameters, string sql, ref string message) {
        // Add your custom validation logic here.
        // The method should return true if the query is valid and false otherwise.
    }
}

public class CustomSqlDataSourceWizardCustomizationService : ISqlDataSourceWizardCustomizationService {
    public ICustomQueryValidator CustomQueryValidator {
        get { return new MyCustomValidator(); }
    }

    public bool IsCustomSqlDisabled {
        get { return false; }
    }
}

VB.NET

Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Wizard.Services
Imports DevExpress.DataAccess.Web
' ...

Public Class MyCustomValidator
    Implements ICustomQueryValidator
    Public Function Validate(ByVal connectionParameters As DataConnectionParametersBase, ByVal sql As String, ByRef message As String) As Boolean
        ' Add your custom validation logic here.
        ' The method should return true if the query is valid and false otherwise.
    End Function
End Class

Public Class CustomSqlDataSourceWizardCustomizationService
    Implements ISqlDataSourceWizardCustomizationService
    Public ReadOnly Property CustomQueryValidator() As ICustomQueryValidator
        Get
            Return New MyCustomValidator()
        End Get
    End Property

    Public ReadOnly Property IsCustomSqlDisabled() As Boolean
        Get
            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
' ...
Protected Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
    DefaultReportDesignerContainer.RegisterSqlDataSourceWizardCustomizationService(Of CustomSqlDataSourceWizardCustomizationService)()
    ' ...
End Sub

Validation of Custom Queries When Saving Reports

By default, the Web Report Designer prohibits saving reports with custom SQL queries. An attempt to save such a report causes an error that displays the corresponding query names. This can occur in the following cases:

• If you open a report that already contains custom SQL queries and then try to save it to the storage or switch to Print Preview.
• If you provide your report with one of the predefined data sources that includes a custom SQL query and then try to save this report or switch to Print Preview.

To enable the Web Report Designer to save reports with custom SQL queries, provide a custom query validation by implementing the ICustomQueryValidator interface.

C#

public class MyCustomValidator : ICustomQueryValidator {
    public bool Validate(DataConnectionParametersBase connectionParameters, string sql, ref string message) {
        // Add your custom validation logic here. 
        // The method should return true if the query is valid and false otherwise. 
    }
} 

VB.NET

Public Class MyCustomValidator Inherits ICustomQueryValidator
    Public Function Validate(ByVal connectionParameters As DataConnectionParametersBase, ByVal sql As String, ByRef message As String) As Boolean
       ' Add your custom validation logic here.
       ' The method should return true if the query is valid and false otherwise.
    End Function
End Class

Then, register this class using the DefaultReportDesignerContainer.Register method at the application startup.

C#

void Application_Start(object sender, EventArgs e) {
    DefaultReportDesignerContainer.Register<ICustomQueryValidator, MyCustomValidator>();
}

VB.NET

Private Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
    DefaultReportDesignerContainer.Register(Of ICustomQueryValidator, MyCustomValidator)()
End Sub

Important

Calling the DefaultReportDesignerContainer.EnableCustomSql method also allows the Report Designer to save reports with custom SQL queries, but in this case, no validation mechanism is used. If you want to validate these SQL queries, you also have to implement the ICustomQueryValidator interface and register it in your application as demonstrated above.