Looking for a correct way to validate mssql Query

Question

public Mono<String> queryValidation(String connectionId, String query) {
    return connectionConfigRepository.findByConnectionId(connectionId)
        .switchIfEmpty(Mono.error(new RuntimeException("Connection not found: " + connectionId)))
        .flatMap(config -> {
            DatabaseConnectionDetails details = config.getDatabaseConnectionDetails();

            return createConnectionFactory(details)
                .flatMap(factory -> Mono.from(factory.create())
                    .flatMap(conn -> {
                        String escaped = query.replace("'", "''");
                        String validationQuery = "EXEC sp_describe_first_result_set N'" + escaped + "', NULL, 1;";

                        return Flux.from(conn.createStatement(validationQuery).execute())
                            .flatMap(result -> Flux.from(result.map((row, meta) -> "ok"))) // force row parsing
                            .then(Mono.just("Valid Query"))
                            .onErrorResume(e -> Mono.just("Invalid Query: " + e.getMessage()))
                            .doFinally(signal -> conn.close());
                    })
                );
        });
}

I tried this way where I am utilising the .execute() to get the errors and differentiate between a valid and an invalid query but unfortunately its giving me "positive response" in both cases. Its because even Invalid Queries are executed and they return errors along with a success token.

I need to find a way to validate mssql queries, manual Validation will take a lot of time and resources.

Also i tried JSQLParser, but for some reason it didn't work. Any suggestions or advices?

This seems like the wrong approach. Queries can fail even if they are written properly, so checking the syntax of the query might still result in problems. You should focus on getting a proper error response when there is one. If it is somehow hidden, that is your problem which needs addressing. — Neil
– Neil, Commented Sep 2 at 9:52
The query is also at risk from SQL Injection. It should use parameterised queries. — Martin Costello
– Martin Costello, Commented Sep 2 at 9:57

Mahdi Zarei · Accepted Answer · 2025-09-02 09:55:16Z

You can use SET NOEXEC ON and afterwards turn it off. Like:

String validationBatch = "SET NOEXEC ON; " + query + "; SET NOEXEC OFF;";

This way you give can only process if the query is valid and it compiles and then you can detect any server errors during compilation.

Always turn NOEXEC off afterwards.

This is edited code:

public Mono<String> validateQuery(String connectionId, String query) {
  return connectionConfigRepository.findByConnectionId(connectionId)
      .switchIfEmpty(Mono.error(new RuntimeException("Connection not found: " + connectionId)))
      .flatMap(cfg -> createConnectionFactory(cfg.getDatabaseConnectionDetails()))
      .flatMap(factory -> Mono.usingWhen(
          Mono.from(factory.create()),
          conn -> {
            String validationBatch = "SET NOEXEC ON; " + query + "; SET NOEXEC OFF;";
            return Flux.from(conn.createStatement(validationBatch).execute())
                .flatMap(result -> Mono.firstWithSignal(
                    result.getRowsUpdated().onErrorResume(__ -> Mono.empty()),
                    result.map((row, md) -> 1).count().thenReturn(0)
                ))
                .then(Mono.just("Valid Query"))
                .onErrorResume(e -> Mono.just("Invalid Query: " + e.getMessage()));
          },
          conn -> Mono.from(conn.close())
      ));
}

Collectives™ on Stack Overflow

Looking for a correct way to validate mssql Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related