Custom SQL Query Validation

• Jun 25, 2020
• 6 minutes to read

Web Reporting provides validation mechanisms for custom SQL queries to strengthen the application security and prevents malicious code execution.

A custom SQL query validation is performed in the following scenarios:

Context	Scenario
Report Designer / Data Source Wizard	A user creates/edits a custom query and saves the query.
Report Designer	A user executes the Save or Save as…, or Preview commands which deserialize the report layout.
Report Designer / Preview, Document Viewer	A user generates a document from a report. A document is created when a user invokes the designer’s Preview, loads a report in the Document Viewer, or when an application calls the CreateDocument method.

The scenarios and validation mechanisms are detaled below.

Report Designer / Data Source Wizard

A user creates/edits a custom SQL query in the Data Source Wizard, as described in the following topics:

• Full-screen Wizard - Create a Query or Select a Stored Procedure
• Pop-up Wizard - Choose predefined queries and/or create custom queries section.

Users can write custom SQL queries in the SQL Data Source Wizard if custom SQL editing is enabled.

When a user saves a custom SQL query, the validation service processes the query text. The default validation service allows only queries with SELECT statements (except for SELECT INTO clauses).

A custom validation service implements the ISqlDataSourceWizardCustomizationService interface. The validation logic is defined in an object with the ICustomQueryValidator interface. The validator object should be assigned to the service CustomQueryValidator property.

Note

If you implement and register a custom validation service, the End-User Report Designer uses the ISqlDataSourceWizardCustomizationService.IsCustomSqlDisabled property value to determine whether to enable custom SQL. The EnableCustomSql method is unnecessary.

The following code illustrates how to implement the validator and the service:

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.
        // Return true if the query is valid; otherwise, return false.
    }
}

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.
        ' Return true if the query is valid; otherwise, return false.
    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 the service, use the following code:

ASP.NET Web Forms or ASP.NET MVC

Call the static DefaultReportDesignerContainer.RegisterSqlDataSourceWizardCustomizationService<T> method on application start:

Global.asax.cs

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

Global.asax.vb

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

ASP.NET Core

Call the ReportDesignerConfigurationBuilder.RegisterSqlDataSourceWizardCustomizationService method on application start:

Startup.cs

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

public class Startup {
//... 
  public void ConfigureServices(IServiceCollection services) {
      services.AddDevExpressControls();
      services.AddMvc();
      services.ConfigureReportingServices(configurator => {
          configurator.ConfigureReportDesigner(designerConfigurator => {
              designerConfigurator.RegisterDataSourceWizardConfigFileConnectionStringsProvider();
              designerConfigurator.RegisterSqlDataSourceWizardCustomizationService<CustomSqlDataSourceWizardCustomizationService>();
          });
      });
  }
//...
}

Report Designer / Save and Preview

The default configuration of the Web End-User Report Designer does not allow you to save reports with custom SQL queries. An attempt to save such a report throws an exception and displays the “Query <query_name> is not allowed“ error. The most common cases are:

• A user opens a report that contains custom SQL queries, modifies it and tries to save it to the storage or to display the Print Preview.
• A user adds a predefined data source that includes a custom SQL query to the report and tries to save the report or to display the Print Preview.

To enable the Web End-User Report Designer to save reports with custom SQL queries or display their Print Preview, use a custom query validator that implements 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 return 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 return false otherwise.
    End Function
End Class

Register the validator as follows:

ASP.NET Web Forms or ASP.NET MVC

Call 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

ASP.NET Core

Use services.AddTransient to register a custom ICustomQueryValidator implementation:

Startup.cs

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

public class Startup {
//... 
  public void ConfigureServices(IServiceCollection services) {
      services.AddDevExpressControls();
      services.AddTransient<ICustomQueryValidator, MyCustomValidator>();
      services.AddMvc();
      // ...

  }
//...
}

Important

If you enable custom SQL query, it also enables the End-User Report Designer to save reports that contain custom SQL queries without validation.

Document Viewer, Report Designer / Preview - Document Generation

Document generation process executes queries to retrieve data. A document is generated from a report in the following cases :

• when the Document Viewer loads a report,
• when the End-User Report Designer displays a report’s Preview
• when an application calls the XtraReport.CreateDocument or CachedReportSource.CreateDocumentAsync methods

Custom SQL queries are validated before execution. The default validation mechanism allows only custom queries with SELECT statements (except for SELECT INTO clauses)

Note

You can disable validation with the SqlDataSource.DisableCustomQueryValidation property.

To validate custom SQL queries throughout the application, use the static SqlDataSource.ValidateCustomSqlQueryGlobal event. Subscribe to this event at the application start, validate the query contained in the e.CustomSqlQuery property, and set the e.Valid property to true if the query is valid.

You can validate custom queries for an individual SQL data source with the SqlDataSource.ValidateCustomSqlQuery event of a particular SqlDataSource instance.

Tip

To prohibit custom query execution in the Document Viewer or the Report Designer’s Print Preview, set the SqlDataSource.AllowCustomSqlQueries property to false.