Custom SQL Query Validation (Web)
- 7 minutes to read
The validation mechanisms for custom SQL queries serve to strengthen application security and prevent malicious code execution.
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.
#Manage Access Control at the Database Level
Do not use custom SQL query verification as the only security precaution. We recommend that you manage database security at the database level through appropriate access configuration and related options. By setting permissions within the database, you ensure that only authorized users and processes can access or modify data.
#When Custom SQL Queries Are Validated
The Web Report component validates a custom SQL query 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 Create |
The scenarios and validation mechanisms are described in detail below.
#Report Designer / Data Source Wizard
A user creates or 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 Queries section.
A user 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) and blocks any SQL keywords that can potentially be used for data modification.
If you use custom SQL queries with restricted statements like REPLACE
, UPDATE
, INSERT
, DELETE
, and others, you receive a validation error:
A custom SQL Query should contain only SELECT Statements
To resolve the issue, implement your custom validation mechanism. 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’s CustomQueryValidator property.
Warning
If you implement and register a custom validation service, the End-User Report Designer uses the ISql
The following code illustrates how to implement the validator and the service:
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; }
}
}
To register the service, use the following code:
#ASP.NET Web Forms or ASP.NET MVC
Call the static DefaultReportDesignerContainer.RegisterSqlDataSourceWizardCustomizationService<T> method at application startup:
using DevExpress.XtraReports.Web.ReportDesigner;
protected void Application_Start(object sender, EventArgs e) {
DefaultReportDesignerContainer.RegisterSqlDataSourceWizardCustomizationService<CustomSqlDataSourceWizardCustomizationService>();
}
#ASP.NET Core
Call the ReportDesignerConfigurationBuilder.RegisterSqlDataSourceWizardCustomizationService method in the application startup file:
using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;
var builder = WebApplication.CreateBuilder(args);
builder.Services.ConfigureReportingServices(configurator => {
configurator.ConfigureReportDesigner(designerConfigurator => {
designerConfigurator.RegisterDataSourceWizardConfigFileConnectionStringsProvider();
designerConfigurator.RegisterSqlDataSourceWizardCustomizationService<CustomSqlDataSourceWizardCustomizationService>();
});
});
var app = builder.Build();
#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 with a custom SQL query to the report and tries to save the report or 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:
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.
}
}
Register the validator as follows:
#ASP.NET Web Forms or ASP.NET MVC
Call the DefaultReportDesignerContainer.Register method at application startup.
void Application_Start(object sender, EventArgs e) {
DefaultReportDesignerContainer.Register<ICustomQueryValidator MyCustomValidator>();
}
#ASP.NET Core
Use services.AddTransient
to register a custom ICustomQueryValidator implementation:
using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDevExpressControls();
builder.Services.AddTransient<ICustomQueryValidator, MyCustomValidator>();
builder.Services.AddMvc();
var app = builder.Build();
Important
Enabling custom SQL query also allows the End-User Report Designer to save reports that contain custom SQL queries without validation.
#Document Viewer, Report Designer / Preview - Document Generation
The 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) and blocks any SQL keywords that can potentially be used for data modification (like REPLACE
, UPDATE
, INSERT
, DELETE
, and other SQL statements).
If you use custom SQL queries with restricted statements, and receive a validation error (A custom SQL Query should contain only SELECT Statements), you can disable validation with the SqlDataSource.DisableCustomQueryValidation property to resolve the issue. However, we do not recommend it due to security risks.
To validate custom SQL queries throughout the application, use the static SqlDataSource.ValidateCustomSqlQueryGlobal event. Subscribe to this event at application startup, 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.
To prohibit custom query execution in the Document Viewer or the Report Designer’s Print Preview, set the SqlDataSource.AllowCustomSqlQueries property to false
.