Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Show Errors


Oracle Tips by Burleson Consulting

 

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

In this function there is only one set of logic, that which build up the SQL statement.  If a parameter is specified the relevant context variable is set and it is referenced in the additional piece of the statement.  As a result of using the context only a single execute statement is needed, regardless of the number or order of bind variables.

Running the get_row_count_test.sql script against this variation of the function produces the same return values, but the SQL statements now contain references to the context.

SQL> @get_row_count_test.sql
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1
get_row_count=5
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 AND code_1 =
SYS_CONTEXT('parameter','code_1')
get_row_count(p_code_1 => 'A')=2
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 AND code_2 =
SYS_CONTEXT('parameter','code_2')
get_row_count(p_code_2 => 'Z')=3
-----------------------------------
SELECT COUNT(*) INTO :b_number FROM dynamic_binds WHERE 1=1 AND code_1 =
SYS_CONTEXT('parameter','code_1') AND code_2 = SYS_CONTEXT('parameter','code_2')
get_row_count(p_code_1 => 'B', p_code_2 => 'Y')=1

PL/SQL procedure successfully completed.

From this we can see that we’ve managed to simplify the code to an acceptable level, while still retaining the benefits of using bind variables as each combination of parameters will only result in a single variation of the SQL statement, regardless of the value assigned to the context variables.

SQL Injection attacks

The term SQL injection is used to describe the process whereby user input can subvert queries sent by badly coded applications and alter their normal behavior. 

If your application uses bind variables you will never see it, but if you rely on concatenation of user input to form SQL statements you may have problems you don’t yet know about.

The sql_injection.sql script provides a simple and self-contained example to illustrate the problem.

sql_injection.sql

CREATE TABLE user_authenticaton (
  username  VARCHAR2(20),
  password  VARCHAR2(20)
);

INSERT INTO user_authenticaton (username, password) VALUES ('tim_hall', 'password');
COMMIT;

ACCEPT username PROMPT 'Username:'
ACCEPT password PROMPT 'Password:'

SET SERVEROUTPUT ON
DECLARE
  l_number    NUMBER;
  l_username  user_authenticaton.username%TYPE := '&username';
  l_password  user_authenticaton.password%TYPE := '&password';
BEGIN
  DBMS_OUTPUT.put_line('Using concatenated strings:');
  EXECUTE IMMEDIATE
   'SELECT COUNT(*) INTO :l_number
    FROM   user_authenticaton
    WHERE  username = ''&username''
    AND    password = ''&password'''
    INTO l_number;

  IF l_number > 0 THEN
    DBMS_OUTPUT.put_line('Authenticated');
  ELSE
    DBMS_OUTPUT.put_line('Not Authenticated');
  END IF;

  DBMS_OUTPUT.put_line('Using bind variables:');
  EXECUTE IMMEDIATE
   'SELECT COUNT(*) INTO :l_number
    FROM   user_authenticaton
    WHERE  username = :username
    AND    password = :password'
    INTO l_number USING l_username, l_password;   

  IF l_number > 0 THEN
    DBMS_OUTPUT.put_line('Authenticated');
  ELSE
    DBMS_OUTPUT.put_line('Not Authenticated');
  END IF;
END;
/

DROP TABLE user_authenticaton;

This script creates a table containing authentication details, prompts the user to enter their username and password details and authenticates those details against the table.  The authentication process is done twice, once using string concatenation and once using bind variables.  First we run the script and enter valid credentials.

SQL> @sql_injection.sql

Table created.

1 row created.

Commit complete.

Username:tim_hall
Password:password
old   3:   l_username  user_authenticaton.username%TYPE := '&username';
new   3:   l_username  user_authenticaton.username%TYPE := 'tim_hall';
old   4:   l_password  user_authenticaton.password%TYPE := '&password';
new   4:   l_password  user_authenticaton.password%TYPE := 'password';
old  10:     WHERE  username = ''&username''
new  10:     WHERE  username = ''tim_hall''
old  11:     AND    password = ''&password'''
new  11:     AND    password = ''password'''
Using concatenated strings:
Authenticated
Using bind variables:
Authenticated


PL/SQL procedure successfully completed.

Table dropped.

As we would expect, both methods have correctly authenticated our login credentials.  Next we run the script and enter invalid credentials.

SQL> @sql_injection.sql

Table created.

1 row created.

Commit complete.

Username:tim_hall
Password:guest
old   3:   l_username  user_authenticaton.username%TYPE := '&username';
new   3:   l_username  user_authenticaton.username%TYPE := 'tim_hall';
old   4:   l_password  user_authenticaton.password%TYPE := '&password';
new   4:   l_password  user_authenticaton.password%TYPE := 'guest';
old  10:     WHERE  username = ''&username''
new  10:     WHERE  username = ''tim_hall''
old  11:     AND    password = ''&password'''
new  11:     AND    password = ''guest'''
Using concatenated strings:
Not Authenticated
Using bind variables:
Not Authenticated

PL/SQL procedure successfully completed.

Table dropped.

This time we see that both methods refuse to authenticate invalid login credentials.  So far so good!

Now, using our knowledge of SQL and string handling we try a sneaky trick.

SQL> @sql_injection.sql

Table created.

1 row created.

Commit complete.

Username:tim_hall
Password:a'' or ''1'' = ''1
old   3:   l_username  user_authenticaton.username%TYPE := '&username';
new   3:   l_username  user_authenticaton.username%TYPE := 'tim_hall';
old   4:   l_password  user_authenticaton.password%TYPE := '&password';
new   4:   l_password  user_authenticaton.password%TYPE := 'a'' or ''1'' = ''1';
old  10:     WHERE  username = ''&username''
new  10:     WHERE  username = ''tim_hall''
old  11:     AND    password = ''&password'''
new  11:     AND    password = ''a'' or ''1'' = ''1'''
Using concatenated strings:
Authenticated
Using bind variables:
Not Authenticated


PL/SQL procedure successfully completed.

Table dropped.

An incorrect password was entered and the string concatenation method authenticated us, while the bind variable solution did the job properly.

The result of the string concatenation method was that the following statement was sent to the server.

SELECT COUNT(*) INTO :l_number
FROM   user_authenticaton
WHERE  username = 'tim_hall'
AND    password = 'a' or '1' = '1'

The addition of the OR statement totally undermines the original intent of the query.

  • SQL injection is often discussed in relation to internet applications, so much so that you could be fooled into thinking this is the only place the problem exists.  In actual fact, any application that concatenates user input to form an SQL statement is at threat.

The bind variable solution worked correctly because the statement it sent to the server was unaffected by the contents of the password variable.  It returned the expected result because it could not find a username of “tim_hall” with a password of “a'' or ''1'' = ''1” in the table. 

In the next section we will touch on the subject of bulk binds, but for a full explanation of their usage see chapter three.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational