2 | HOW TO WRITE SQL INJECTION PROOF PL/SQL 10-May-2017
INTRODUCTION
At the time of writing, An internet search for “SQL injection” gets about 4
million hits. The topic excites interest and superstitious fear. This whitepaper
dymystifies the topic and explains a straightforward approach to writing database
PL/SQL programs that provably guarantees their immunity to SQL injection.
The scope of the discussion is strictly limited to PL/SQL units that are stored in
the database. Similar principles apply in the discussion of languages, such as C or
Java, used to implement client-side programs, but it is very much harder to
control access to such programs. It is even harder to ensure that access to the
database is made only using such client-side programs.
In order best to understand the discussion, the reader must have sound
understanding of the various ways that SQL may be executed from a database
PL/SQL unit. The whitepaper Doing SQL from PL/SQL: Best and Worst Practices
1
,
addresses this topic in detail. Its study is, therefore, recommended as a
prerequisite for the study of this whitepaper. In particular, the Doing SQL from
PL/SQL whitepaper argues for the strategy that bans direct SQL access to the
database and exposes it to the client only via a strictly minimal PL/SQL API
2
. If
this strategy is adopted, then the proofing against SQL injection is the sole
responsibility of database PL/SQL; and a sufficient solution is possible in this
regime.
Of course, one cannot avoid what one cannot define — and so we start with the
section “Definition of SQL injection” on page 4. We use this definition, in the
section “How can SQL injection happen?” on page 13, to examine some famous
examples of code that is vulnerable. We also examine some counter-examples in
order to prove our definition of SQL injection.
The discussion in these two sections leads to the understanding that
SQL injection is possible only when a PL/SQL subprogram executes a
SQL statement whose text it has created at run time using what, here, we can
loosely call unchecked user input
3
. Clearly, then, the best way to avoid
SQL injection is to execute only SQL statements whose text derives entirely
from the source code of the PL/SQL program that executes it.
However, when the watertight approach will not meet the requirements, it is,
after all, necessary to handle user input — and to do so safely. A careful study of
this topic is presented in the section “Ensuring the safety of a SQL literal or a simple
SQL name” on page 20.
The material in these first three sections supports the rationale for, and the
understanding of, what follows in the section “Rules for cost-effective, guaranteed
prevention of SQL injection” on page 29. Indeed, if this paper makes an original
contribution, it is in the development of the conceptual framework, and the
associated terms of art, that then allow the rules to be stated compactly and
1. Doing SQL from PL/SQL: Best and Worst Practices is published on the Oracle Technology
Network website. You can find it easily with Internet search.
2. This is discussed in the section “Expose the database to clients only via a PL/SQL API” on page 29.
3. This notion will be formally defined in the section “Dynamic text” on page 36.
Make sure that you’re reading the
latest copy of this paper. Check the
URL given at the top of each page.