Show Errors

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.