Executing SQL statements without bind variables is very much like compiling a subroutine before each method call. Imagine shipping Java source code to your customers where, before calling a method in a class, they had to invoke the Java compiler, compile the class, run the method, and then throw away the bytecode.
Next time they wanted to execute the same method, they would do the same thing: compile it, run it, and throw it away. You would never consider doing this in your application; you should never consider doing this in your database either.
Another impact of not using bind variables, for developers employing string concatenation, is security—specifically something called SQL injection. If you are not familiar with this term, I encourage you to put aside this book for a moment and, using the search engine of your choice, look up SQL injection.
There are over five million hits returned for it as I write this edition. The problem of SQL injection is well documented.
Note SQL injection is a security hole whereby the developer accepts input from an end user and concatenates that input into a query, then compiles and executes that query. In effect, the developer accepts snippets of SQL code from the end user, then compiles and executes those snippets. That approach allows the end user to potentially modify the SQL statement so that it does something the application developer never intended. It’s almost like leaving a terminal open with a SQL*Plus session logged in and connected with SYSDBA privileges. You are just begging someone to come by and type in some command, compile it, and then execute it. The results can be disastrous.
It is a fact that if you do not use bind variables, that if you use the string concatenation technique in PROC2 shown earlier, your code is subject to SQL injection attacks and must be carefully reviewed.
And it should be reviewed by people who don’t actually like the developer who wrote the code—because the code must be reviewed critically and objectively. If the reviewers are peers of the code author, or worse, friends or subordinates, the review will not be as critical as it should be. Developed code that does not use bind variables must be viewed with suspicion—it should be the exceptional case where bind variables are not used, not the norm.
To demonstrate how insidious SQL injection can be, I present this small example.
First, I’ll create a user in my pluggable database that has the DBA role:
$ sqlplus system/foo@PDB1
Note If the DBA role isn’t grantable in your PDB, then grant the PDB_DBA role to the pwd_mgr user instead.
Next, connect to the user and create a small procedure:
SQL> conn pwd_mgr/pwd_mgr_pwd@PDB1
Procedure created.
Note This code prints out only five records at most. It was developed to be executed in an “empty” schema. A schema with lots of existing tables could cause various effects that differ from the results shown next.
One effect could be that you don’t see the table I’m trying to show you in the example—that would be because we print out only five records.
Another might be a numeric or value error—that would be due to a long table name. None of these facts invalidate the example; they could all be worked around by someone wanting to steal your data.
Now, most developers I know would look at that code and say that it’s safe from SQL injection. They would say this because the input to the routine must be an Oracle DATE variable, a 7-byte binary format representing a century, year, month, day, hour, minute, and second. There is no way that DATE variable could change the meaning of my SQL statement. As it turns out, they are very wrong. This code can be “injected”—modified at runtime, easily—by anyone who knows how (and, obviously, there are people who know how!).
If you execute the procedure the way the developer “expects” the procedure to be executed, this is what you might expect to see:
SQL> set serverout on
SQL> exec inj( sysdate ) select *
from all_users where created = ’05-FEB-21′
PL/SQL procedure successfully completed.
This result shows the SQL statement being safely constructed—as expected. So, how could someone use this routine in a nefarious way? Well, suppose you’ve got another developer in this project—the evil developer.
The developers have access to execute that procedure, to see the users created in the database today, but they don’t have access to any of the other tables in the schema that owns this procedure.
Now, they don’t know what tables exist in this schema—the security team has decided “security via obscurity” is good—so they don’t allow anyone to publish the table names anywhere.
