Skip to content

Examples of SQL Validation

Jump to bottom
Tobias edited this page Jul 26, 2022 · 4 revisions

SQL validation framework

Since 4.0 the JSQLParser framework includes a validaton framework.

The validation framework maps several types of validation, which are implementations of the interface ValidationCapability.

Currently the following implementations exist:

  • ParseCapability: Checks if a statement can be parsed (always included with Validation#validate())
  • FeatureSetValidation:
    • DatabaseType: Checks if a parsed statement is valid with respect to the supported database syntax
    • Version: Checks if a parsed statement is valid with respect to a specific database version.
    • FeaturesAllowed: Checks if a statement only uses syntax elements which are allowed.
  • DatabaseMetaDataValidation: Validates meta data such as names of tables, views, columns for their existence or non-existence

SQL Validation DatabaseType/Version

Check if a parsed statement is valid to the database selected

String sql = "DROP INDEX IF EXISTS idx_tab2_id;";

// validate statement if it's valid for all given databases.
Validation validation = new Validation(Arrays.asList(DatabaseType.SQLSERVER, DatabaseType.MARIADB,
                DatabaseType.POSTGRESQL, DatabaseType.H2), sql);
List<ValidationError> errors = validation.validate();

Check if a parsed statement is valid to a specific database version.

// validate statement if it's valid for all given databases.
Validation validation = new Validation(Arrays.asList(PostgresqlVersion.V10), sql);
List<ValidationError> errors = validation.validate();

SQL Validation FeaturesAllowed

Validate against pre-defined FeaturesAllowed.DML set

// validate against pre-defined FeaturesAllowed.DML set
String sql = "CREATE TABLE tab1 (id NUMERIC(10), val VARCHAR(30))";
Validation validation = new Validation(Arrays.asList(FeaturesAllowed.DML), sql);
List<ValidationError> errors = validation.validate();
// only DML is allowed, got error for using a DDL statement
log.error (errors);

Validate against pre-defined FeaturesAllowed.SELECT set

String sql = "SELECT * FROM myview v JOIN secondview v2 ON v.id = v2.ref";
Validation validation = new Validation(Arrays.asList(FeaturesAllowed.SELECT), sql);
List<ValidationError> errors = validation.validate();
// no errors, select - statement is allowed
if (errors.isEmpty()) {
	// do something else with the parsed statements
	Statements statements = validation.getParsedStatements();
}

Validate against your own FeatureSet

FeaturesAllowed exec = new FeaturesAllowed("EXECUTE", Feature.execute).unmodifyable();

Combine multiple pre-defined FeaturesAllowed set's

FeaturesAllowed myAllowedFeatures = new FeaturesAllowed("myAllowedFeatures")
	.add (FeaturesAllowed.DDL, FeaturesAllowed.DML);

SQL Validation DatabaseMetaDataValidation

Validates metadata such as names of tables, views, columns for their existence or non-existence

java.sql.Connection connection = ...;
String sql = "ALTER TABLE mytable ADD price numeric(10,5) not null";
Validation validation = new Validation(Arrays.asList(new JdbcDatabaseMetaDataCapability(connection, 	
// NamesLookup: Databases handle names differently
        NamesLookup.UPPERCASE)), sql);
List<ValidationError> errors = validation.validate();

// do something else with the parsed statements
Statements statements = validation.getParsedStatements();

// check for validation-errors
if (!errors.isEmpty()) {
    ...
}

SQL Validation with multiple ValidationCapability's

java.sql.Connection connection = ...;
String sql = "ALTER TABLE mytable ADD price numeric(10,5) not null";
Validation validation = new Validation(Arrays.asList(
    DatabaseType.POSTGRESQL, 
    new JdbcDatabaseMetaDataCapability(connection,NamesLookup.LOWERCASE)), sql);
List<ValidationError> errors = validation.validate();

Validate a Statement already parsed:

Statement statement = ...;
ValidationContext context = Validation.createValidationContext(
    new FeatureConfiguration(), Arrays.asList(DatabaseType.POSTGRESQL));
Map<ValidationCapability, Set<ValidationException>> errors = 
    Validation.validate(statement, context);

Further development:

For further development it is important to know that Feature 's are only constants to describe a feature. This feature must first be grouped for validation:

  • Version: within this group a feature is activated for a certain database version - that means the version supports this feature (e.g. PostgresqlVersion, OracleVersion, ...) For this it must be checked against the database-specifications for the individual databases whether the syntax of the feature and from which version a feature is supported.
  • FeaturesAllowed: within this group there are predefined FeatureSets which restrict the type of SQL statements allowed, e.g. FeaturesAllowed.UPDATE - all features which are allowed by the JSqlParser for an UPDATE statement (no matter which database).
  • The DatabaseType includes by default all features of the last version of the database and usually does not need to be adjusted.

The new constant must of course be checked in the corresponding AbstractValidator implementation: For this purpose there are methods which simply need to be passed the new feature as a parameter, (i.e. #validateOptionalFeature, #validateFeature, ... ).

If a new feature also contains metadata like names of tables, views, columns or similar the content must be validated by the appropriate validators, which in turn use the predefined method #validateName to check metadata against a database catalog. (see also NamedObject)

Clone this wiki locally