Developed in the 1990’s to augment the potential of SQL, PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation’s procedural extension for SQL and the Oracle relational database. It is a “declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution.”
“Hello World” in PL/SQL
PL/SQL combines SQL and aspects of other programming languages such as SQL and Java which allows developers to mix SQL statements with procedural constructs.
Essentially a procedural language, PL/SQL adds functionality for decision making, iteration and many more features such as other procedural programming languages. As a block-structured language PL/SQL programs are divided and written in logical blocks of code consisting of declarations, executable commands and exception handling.
PL/SQL program units are one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database.
Oracle uses both SQL and PL/SQL to access data within Oracle databases.
Oracle’s FAQ explains some of the main differentiators:
“While SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn’t include all the things that normal programming languages have, such as loops and IF…THEN…ELSE statements.”
Some of the differences between SQL and PL/SQL:
- SQL is executed one statement at a time. PL/SQL is executed as a block of code.
- SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
- SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
- You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.
Alongside SQL Injections (SQLi), Stored XSS and Reflected XSS, which affect many contemporary programming languages, PL/SQL applications also face threats from:
- Resource Injection
- Second Order SQL Injection
- Dangling Database Cursor
- Default Definer Rights in Package or Object Definition
- DoS By Sleep
- HTTP Response Splitting
- Improper Privilege Management
- Open Redirect
- Parameter Tampering
- Plaintext Storage of a Password
- Privacy Violation
- Trust Boundary Violation
- Use of Insufficiently Random Values
Checkmarx’s CxSAST, a static code analysis solution, stands out amongst PL/SQL testing solutions as not only the solution which will keep your PL/SQL code free from security and compliance issues, but also as the tool which will contribute to your organization’s advancement when it comes to application security maturity.
CxSAST works with the tools your developers are already using as it seamlessly integrates with most of the common development programs available at every stage of the SDLC. CxSAST’s features such as incremental code scanning and the best fix location made it ideal for any continuous integration continuous development (CICD) environment.
When vulnerabilities are detected in the PL/SQL code, CxSAST will not only identify the best fix location, but will also offer resources to the developer to understand how the attack vector work as well as remediation advice which will help them ensure similar mistakes are avoided in the future.