The Threat of SQL Injection#

When you use EXECUTE IMMEDIATE to run dynamic SQL, you open the door to SQL Injection. If you directly concatenate user input into the SQL string, a malicious user can manipulate the query structure.

Consider this search procedure:

PROCEDURE search_user(p_username VARCHAR2) IS
  v_sql VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  v_sql := 'SELECT COUNT(*) FROM users WHERE username = ''' || p_username || '''';
  EXECUTE IMMEDIATE v_sql INTO v_count;
END;

If a hacker provides the input admin' OR '1'='1, the string becomes: SELECT COUNT(*) FROM users WHERE username = 'admin' OR '1'='1'

This bypasses the security check entirely, returning data they shouldn't see. Worse, they could inject '; DROP TABLE users; --.

The Ultimate Defense: Bind Variables#

The only way to guarantee prevention of SQL injection on data values is to use Bind Variables. When you use bind variables, the database engine treats the input strictly as a literal value, never as executable code.

PROCEDURE search_user(p_username VARCHAR2) IS
  v_sql VARCHAR2(1000);
  v_count NUMBER;
BEGIN
  -- Using :usr as a placeholder
  v_sql := 'SELECT COUNT(*) FROM users WHERE username = :usr';
  
  -- Pass the variable safely using the USING clause
  EXECUTE IMMEDIATE v_sql INTO v_count USING p_username;
END;

Even if p_username contains malicious SQL, Oracle simply searches for a user whose literal name is "admin' OR '1'='1".

Sanitizing Identifiers (DBMS_ASSERT)#

Bind variables only work for values in the WHERE or VALUES clause. You cannot bind table names or column names.

If you are writing a dynamic utility that drops a table based on user input, you must sanitize the input using DBMS_ASSERT.

PROCEDURE drop_table(p_table_name VARCHAR2) IS
  v_safe_name VARCHAR2(128);
BEGIN
  -- Verify the table actually exists and is a valid SQL identifier
  -- If it's malicious (e.g. "dual; drop user sys;"), this will throw an exception!
  v_safe_name := DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name);
  
  EXECUTE IMMEDIATE 'DROP TABLE ' || v_safe_name;
END;

Execution Rights (AUTHID DEFINER vs CURRENT_USER)#

When User A creates a package, and User B executes it, whose privileges are used?

1. Definer's Rights (Default): The code executes with the privileges of the user who created the package. If User A has DROP ANY TABLE rights, and User B calls User A's package, User B temporarily gains the power to drop tables! This is a massive security risk if the package contains dynamic SQL.
2. Invoker's Rights: The code executes with the privileges of the user who called the package.

To enforce Invoker's rights, add AUTHID CURRENT_USER to your package spec.

CREATE OR REPLACE PACKAGE secure_admin_tools 
  AUTHID CURRENT_USER -- Code runs with the caller's permissions
AS
  PROCEDURE drop_my_table(p_table VARCHAR2);
END;
/

Data Encryption (DBMS_CRYPTO)#

For storing sensitive data like passwords or API keys, never store them in plain text. Use DBMS_CRYPTO to hash or encrypt them.

DECLARE
  v_password VARCHAR2(100) := 'MySecretPass123';
  v_hash RAW(256);
BEGIN
  -- Hash the password using SHA-256
  v_hash := DBMS_CRYPTO.HASH(
    src => UTL_I18N.STRING_TO_RAW(v_password, 'AL32UTF8'),
    typ => DBMS_CRYPTO.HASH_SH256
  );
  
  DBMS_OUTPUT.PUT_LINE('Hashed: ' || v_hash);
END;
/

Common Gotchas#

Key Takeaways#