PeteFinnigan.com Limited Products, Services, Training and Information

PL/SQL secure?

Home/PL/SQL secure?

Is your PL/SQL secure?

This is my presentation to the UKOUG in December on is your PL/SQL secure?

Slide 1 - Is Your PL/SQL Secure?

Slide 2 - Legal notice

Is Your PL/SQL Secure?

Published by
PeteFinnigan.com Limited
Tower Court
3 Oakdale Road
York
England, YO30 4XL

Copyright © 2025 by PeteFinnigan.com Limited

No part of this publication may be stored in a retrieval system, reproduced or transmitted in any form by any means, electronic, mechanical, photocopying, scanning, recording, or otherwise except as permitted by local statutory law, without the prior written permission of the publisher. In particular this material may not be used to provide training of any type or method. This material may not be translated into any other language or used in any translated form to provide training. Requests for permission should be addressed to the above registered address of PeteFinnigan.com Limited in writing.

Limit of Liability / Disclaimer of warranty. This information contained in this course and this material is distributed on an “as-is” basis without warranty. Whilst every precaution has been taken in the preparation of this material, neither the author nor the publisher shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the instructions or guidance contained within this course.

TradeMarks. Many of the designations used by manufacturers and resellers to distinguish their products are claimed as trademarks. Linux is a trademark of Linus Torvalds, Oracle is a trademark of Oracle Corporation. All other trademarks are the property of their respective owners. All other product names or services identified throughout the course material are used in an editorial fashion only and for the benefit of such companies with no intention of infringement of the trademark. No such use, or the use of any trade name, is intended to convey endorsement or other affiliation with this course.

Slide 3 - Background

Pete Finnigan – Background, Who Am I?

Oracle Security specialist and researcher
CEO and founder of PeteFinnigan.com Limited in February 2003
Writer of the longest running Oracle security blog
Author of the Oracle Security step-by-step guide and “Oracle Expert Practices”, “Oracle Incident Response and Forensics” books
Oracle ACE for security
Member of the OakTable, SYM 42
Speaker at various conferences UKOUG, PSOUG, BlackHat, more..
Published many times, see http://www.petefinnigan.com for links

Slide 4 - Agenda

PL/SQL
Security of PL/SQL
Attacks involving PL/SQL
Securing PL/SQL high level
Testing PL/SQL
Fixing your code
Summing Up

Slide 5 - PL/SQL

PL/SQL

Slide 6 - Databases Use PL/SQL

We see PL/SQL in most databases
In your business logic
Third party applications that use PL/SQL such as EBS
Commercial tools you use that manage / monitor the database
Tools your DBAs and Support create that manage / monitor the database

Slide 7 - Security of PL/SQL

Security of PL/SQL

Slide 8 - Three Security Domains in Secure Code

Secure code is much more than SQL Injection or other syntax based issues. Secure code is also about good design
Design of / and privilege of the business code as its deployed
Access to dangerous logic or resources must be controlled
Syntax based issues / errors
The first two need education and understanding and recognition
The last can be by following checks/rules

Slide 9 - What If Your Database Is absolutely locked Down?

We can completely lock down Oracle to prevent breaches or data loss THEN
The only possible attacks would be
Corrupt employees can use apps to access data
Someone can gain access to the building and access unattended IT
An attacker can exploit the applications PL/SQL business or security code
Security of code is not just about syntax
You must design security

Slide 10 - Attacks Involving PL/SQL

Attacks Involving PL/SQL

Slide 11 - Simple SQL Injection Example

We use SQL Injection to access data we cannot see and use functions we cannot access

Demo: Run simple.sql

Slide 12 - Permissions Issue

If we grant access to code and data then SQL Injection is not needed BUT RESULT IS SAME!

Demo: Run perm.sql

Slide 13 - Expose Internal Secrets (Encryption Key)

We can read PL/SQL source code, understand IPR, steal cards and steal the encryption key

Demo: Hack encryption

Slide 14 - Summary of Hacking

We can steal data not just with SQL injection
We can simply use a PL/SQL interface that gets the data for us
We could steal encrypted data and re-implement the PL/SQL “off-line” to steal it
We could use resources to access data – i.e. use PL/SQL -> Java -> data files

Slide 15 - Code Areas to Consider

Injection (SQL, PL/SQL, JavaScript, XSS,….)
Replacement
Comments that cause issue:
FUDGE, BODGE, FIXME, ****, ####, TODO, BUG, TICKET…
Comments that describe security functions and how they work – i.e. encryption key location or password mechanism
Use of
Undocumented packages – i.e. %_FFI, KUPP$...
Deprecated methods and functions, i.e. DBMS_JOB
Vulnerable functions – i.e. CPU, PSU
Dangerous, i.e. UTL_MAIL
Open Interface, i.e. DBMS_DDL
Magic values, i.e. passwords, keys, IP Address

There are many more areas that we can consider as weaknesses in PL/SQL. Some are highlighted here

Slide 16 - More Areas to Consider - 2

Values passed to dangerous code, i.e. key passed to DBMS_CRYPTO
Resources not managed – opened and not closed
Dependencies – i.e. secure one layer and expose others
Debug, Trace, Wrapping can be used to inject
Schemas accessible so bypassing security logic
Synonyms
Non direct paths to code
Multi layer code
Exceptions, lack of, hiding, never raised, never caught

Slide 17 - More Areas to Consider - 3

Standalone procedures
Exceptions – Oracle codes, code and error numbers
Unused variables, parameters, non-initialised
Duplication
Unused or unreachable code
Ref cursors
Non-standard concatenation or home grown SQL injection solutions
Quoted identifiers
Use of C or Java
Code size

Slide 18 - Securing PL/SQL at a High Level

Securing PL/SQL at a High Level

Slide 19 - Secure PL/SQL from Different Vectors

Where
PL/SQL itself is the attack vector
Where the attacker gains access due to database security issues
Design issues (User, Data, Access) allow
PL/SQL can let you down even if the database is secure
Success as an attacker gives access to more: IPR, code function…
An attacker can run code when not intended by apps

Slide 20 - Testing PL/SQL

Testing PL/SQL

Slide 21 - Automate

Automate testing
Create a policy / plan
Create a database security standard
Create a secure code standard
Test database security
Test PL/SQL code security
Manually do the things that cannot be automated BUT as part of a development and release process

Slide 22 - Create a Policy

The first step in establishing secure coding practices for PL/SQL is to create a secure coding standards for all PL/SQL development
What does could it include?
Industry best practice
SANS
OWASP
PCI
What format should it take?
It should be short
Simple to follow
Each “rule” should be distinct to avoid interpretation issues
Define testing against the policy
This could be manual or automated
No testing should take place until a standard exists otherwise any testing would be completely random

You cannot secure with random actions, create a plan and work to it

Slide 23 - Testing PL/SQL Code

Three options exist to test your own code but it should be driven by a policy

Manually review code by eye
Use a commercial tool to scan PL/SQL code such as Fortify or PFCLCode from PeteFinnigan.com Limited
Use a free tool to scan PL/SQL code such as VCG or simple SQL and PL/SQL scripts
Using any tool (free or commercial) out of the box has value
But: ensure that you test to your own defined standards
Also be aware that some tools are expensive
Also be aware that some tools do not find what you need to test for

Analysis involves two levels of issue; static “grep” like for dangerous code or IPR and flow analysis for dynamic SQL or injection
Flow analysis could be by tainting or by processing an AST

Slide 24 - Manual Review

Develop a review cycle that includes security
Cycle
Code is written
Scan / review for security issues
Function test and integration
Scan / review again for security issues
This should fit into the normal development process as additional tasks that check for potential security issues

Slide 25 - PFCLCode – Commercial Code Scanner

PFCLCode – Commercial Code Scanner

Slide 26 - Simple Automated Code Analysis

A simple code analysis can be done with queries against the database
Analyse DBA_ARGUMENTS to locate public interfaces for PL/SQL that can be access by code
Use script newcodea.sql to locate code that contains dangerous constructs such as DDL, dangerous packages such as DBMS_DDL or dynamic SQL such as EXECUTE IMMEDIATE, DBMS_SQL, DBMS_SYS_SQL and OPEN FOR
These checks can identify code that could be analysed by hand to assess if issues exist
The new PL-Scope interface also can provide value to analyse identifiers used in PL/SQL code.

Demo: Run analyse.sql

Slide 27 - Fixing Your Code

Fixing Your Code

Slide 28 - Security Best Practice in PL/SQL

Design code for security
Separate logic / function (code) and data into domains
Design permissions (grants)
Design for users who access the code
i.e. we cannot as developers control users as once code is deployed we can have no further say in how its used
But we can code in logic to prevent bad use
Use role detection
Use privilege detection
Use context based security in our code
This cannot realistically be done for all code though

Slide 29 - Security API as a Design Pattern

Identify the dangerous thing. i.e. use of ALTER USER, File, Link, Dangerous package…
Create a separate schema to own that thing
Grant the privilege to the separate schema not globally
Create an API to limit how that privilege is used – i.e. enforce least use / rights
Choose carefully definer / invoker / inherit
Grant just the API to the other users who need access to the dangerous rights
Add context based controls
the “accessible by” clause from 12c or “who_called_me” before that
Add additional controls to the schema to prevent bypass
Password locked, schema only, limit with DDL triggers…
Filter inputs, white list and black list

Slide 30 - Obfuscate Code and License Code (Protect IPR)

Obfuscation
Randomise the code identifiers,
Remove comments, whitespace etc
Deeper obfuscations
License features could have many forms; Types
Time/ date based
Place – DBID, DBNAME, Network adaptor, Server, hardware, number of CPU’s...
Data based – such as SYS create date or DB create date
Person based
Context based – where in code,
Privilege based/enabled
i.e. Run on Tuesday between 6 and 8 pm when user is “FRED” and role “BLOB” is enabled and DB is PROD and ....

Slide 31 - Example Fix: Basic Security Filtering

White List inputs – fixed lists are best – YELLOW, GREEN, RED..
Black List inputs
C style comments such as /* or
PL/SQL comments - -
Or unusual characters such as space
Some techniques to use include:
Use DBMS_ASSERT.ENQUOTE_LITERAL – This will make safe any statement that could be exploited due to use of function names or unbalanced single quotes
Use DBMS_ASSERT.SIMPLE_SQL_NAME to validate objects passed in
Assert that any date or numeric format is specified against a hard coded format specifier
Do not use quoted identifiers

Slide 32 - Example Fix: Static Concatenation

Where dynamic SQL text must be used then the dynamic SQL should be built from static text and safe SQL statement text
Static text is PL/SQL CONSTANT text
Safe SQL statement text is text that consists of static text and also asserted text with dbms_assert.enquote_literal or dbms_assert.simple_sql_name
The sample shows an example of these ideas

Demo: Run static.sql

Slide 33 - Example Fix: Bind Variables

Where dynamic SQL or PL/SQL does not involve objects then bind variables should instead be used as this effectively prevents injection
The sample shows a solution to simple.sql using bind variables.
NOTE: The overall solutions are multi-faceted
We should use bind variables or explicit cursors
We should use static template text
We should assert input from attack
We should filter and test input to be from a narrow range

Demo: Run bind.sql

Slide 34 - Summing Up