Validate SQL Queries using PL/SQL Package

Introduction

We built a dynamic reporting solution that allows end users to store and execute SQL queries with bind variables in Oracle APEX. While this approach provides great flexibility, it also introduces an important challenge: how do we ensure that user-edited SQL queries are valid and secure. If users are allowed to modify existing SQL queries, the application must validate the query before saving or executing it.

Why we need to do

The validation should ensure that:

  1. The SQL statement is a valid SELECT query
  2. The query syntax is correct
  3. All referenced tables and columns are valid
  4. The query does not contain any DDL statements such as CREATE, ALTER, DROP, or TRUNCATE
  5. The query does not contain any DML statements such as INSERT, UPDATE, DELETE, or MERGE

To address these requirements, a reusable PL/SQL package was created that performs comprehensive SQL validation. The package verifies the query syntax, ensures that only SELECT statements are allowed, and blocks any unauthorized SQL operations.

How do we solve

Step 1: Create Package
Package Specification

create or replace PACKAGE sql_validation_pkg AS
  FUNCTION validate_select_query(
    p_sql IN VARCHAR2
  ) RETURN VARCHAR2;
END sql_validation_pkg;
/

Package Body

create or replace PACKAGE BODY sql_validation_pkg AS

  FUNCTION validate_select_query(
    p_sql IN VARCHAR2
  ) RETURN VARCHAR2
  IS
    l_sql       VARCHAR2(32767);
    l_cursor    INTEGER;
  BEGIN

    -- 1. Check for empty input
    IF p_sql IS NULL OR TRIM(p_sql) IS NULL THEN
      RETURN 'SQL Query cannot be empty.';
    END IF;

    -- Trim whitespace and remove trailing semicolon if present (standard in dynamic SQL)
    l_sql := TRIM(p_sql);
    IF SUBSTR(l_sql, -1) = ';' THEN
      l_sql := SUBSTR(l_sql, 1, LENGTH(l_sql) - 1);
    END IF;

    -- 2. Basic security check: Only allow SELECT or WITH statements.
    IF NOT (UPPER(l_sql) LIKE 'SELECT %' OR UPPER(l_sql) LIKE 'WITH %') THEN
      RETURN 'Security Violation: Only SELECT or WITH queries are permitted.';
    END IF;

    -- 3. Additional block against potentially harmful DML/DDL keywords
    IF REGEXP_LIKE(UPPER(l_sql), '\b(INSERT|UPDATE|DELETE|DROP|TRUNCATE|ALTER|CREATE|GRANT|REVOKE|MERGE)\b') THEN
      RETURN 'Security Violation: Query contains unauthorized modification keywords.';
    END IF;

    -- 4. Parse the SQL to validate syntax
    BEGIN
      l_cursor := sys.dbms_sql.open_cursor;
      sys.dbms_sql.parse(l_cursor, l_sql, sys.dbms_sql.native);
      sys.dbms_sql.close_cursor(l_cursor);
      RETURN NULL; -- NULL indicates SQL is valid (no errors)
    EXCEPTION
      WHEN OTHERS THEN
        IF sys.dbms_sql.is_open(l_cursor) THEN
          sys.dbms_sql.close_cursor(l_cursor);
        END IF;
        RETURN SQLERRM; -- Return the Oracle ORA-XXXXX error message
    END;

  END validate_select_query;

END sql_validation_pkg;
/

Step 2: Create validation (PL/SQL Function Body Returning Error Text)

declare
  l_error_msg varchar2(4000);
begin
  l_error_msg := sql_validation_pkg.validate_select_query(:P34_QUERIES);
  return l_error_msg; -- If this returns NULL, the validation passes
end;

Step 3: Save and Run the application.

Conclusion

By implementing a dedicated PL/SQL validation package, we can ensure that every user-defined query is thoroughly validated before it is saved or executed. The package verifies the SQL syntax, confirms that only valid SELECT statements are allowed, checks for invalid objects or columns, and blocks DDL and DML statements such as CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, and MERGE. This validation layer provides a secure and reliable foundation for dynamic reporting in Oracle APEX. It not only protects the database from unauthorized operations but also improves the user experience by identifying errors early and providing meaningful validation feedback.

Recent Posts