Oracle Bind Variables

Oracle Bind Variables Version 11.2.0.3

General Information

Without further specification bind variables are assumed to be of character type. Replace :b1 with TO_DATE(:b1) if working with date values: TO_NUMBER(:b1) if numeric values.

Data Dictionary Objects

DBA_HIST_SQLBIND	V$SQL_BIND_CAPTURE	V$SQL_BIND_METADATA
DBMS_ASSERT	V$SQL_BIND_DATA	WRH$_SQL_BIND_METADATA

Bind Variable Usage

set linesize 121
col sql_text format a100

SELECT sql_text
FROM gv$sql
WHERE sql_text LIKE '%:B%'
AND rownum < 21;

Bind Variable Values

col value_string format a60

SELECT DISTINCT hash_value, value_string
FROM gv$sql_bind_capture
WHERE rownum < 501
ORDER BY 1;

Bind Variable Demo

This demonstration is a merging of demos developed independently by Dan Morgan and Tom Kyte

conn / as sysdba

GRANT select ON gv_$statname TO uwclass;
GRANT select ON gv_$latch TO uwclass;
GRANT select ON gv_$sql TO uwclass;
GRANT select ON gv_$sqltext_with_newlines TO uwclass;
GRANT select ON v_$mystat TO uwclass;
GRANT alter system TO uwclass;

conn uwclass/uwclass

CREATE TABLE run_stats (
runid VARCHAR2(15),
name VARCHAR2(80),
value INT);

CREATE OR REPLACE VIEW stats AS
SELECT 'STAT...' || a.name NAME, b.value
FROM gv$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || NAME, gets
FROM gv$latch;

CREATE TABLE t (x INT);

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH SHARED_POOL;

INSERT INTO run_stats
SELECT 'before', stats.*
FROM stats;

set timing on

-- not using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 50000
LOOP
    EXECUTE IMMEDIATE 'SELECT ' || i || ' INTO :b1 FROM dual'
    INTO x;
END LOOP;
END;
/

INSERT INTO run_stats
SELECT 'after 1', stats.*
FROM stats;

-- using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 50000
LOOP
    EXECUTE IMMEDIATE 'SELECT :b1 FROM dual'
    INTO x
    USING i;
END LOOP;
END;
/

set timing off

INSERT INTO run_stats
SELECT 'after 2', stats.*
FROM stats;

col name format a35

SELECT a.name, b.value-a.value RUN1, c.value-b.value RUN2, ((c.value-b.value)-(b.value-a.value)) DIFF
FROM run_stats a, run_stats b, run_stats c
WHERE a.name = b.name
AND b.name = c.name
AND a.runid = 'before'
AND b.runid = 'after 1'
AND c.runid = 'after 2'
AND (c.value-a.value) > 0
AND (c.value-b.value) <> (b.value-a.value)
ORDER BY ABS((c.value-b.value)-(b.value-a.value));

SELECT sql_fulltext
FROM gv$sql s, gv$sqltext_with_newlines n
WHERE s.hash_value = n.hash_value
AND n.sql_text LIKE '%dual%'
ORDER BY last_active_time;

SQL Injection Variable Demo

This example is based on a demo created by Tom Kyte and published in the Jan/Feb 2005 issue of Oracle Magazine

CREATE TABLE user_table (
username VARCHAR2(30),
password VARCHAR2(30));

INSERT INTO user_table
(username, password)
VALUES
('Tom Kyte', 'top_secret_password');

COMMIT;

SELECT * FROM user_table;

-- not using bind variables: valid attempt
SQL> Accept Uname prompt "Enter username: "
Enter username: Tom Kyte

SQL> Accept Pword prompt "Enter pass: "
Enter pass: top_secret_password

SELECT COUNT(*)
FROM user_table
WHERE username = '&Uname'
AND password = '&Pword';

-- not using bind variables: SQL injection
SQL> Accept Uname prompt "Enter username: "
Enter username: Tom Kyte

SQL> Accept Pword prompt "Enter pass: "
Enter pass: i_dont_know ' or 'x ' ='x

SELECT COUNT(*)
FROM user_table
WHERE username = '&Uname'
AND password = '&Pword';

-- using bind variables
CREATE OR REPLACE PROCEDURE validate_user(uname VARCHAR2, upasswd VARCHAR2) AUTHID DEFINER IS
i NATURAL;
BEGIN
SELECT COUNT(*)
INTO i
FROM user_table
WHERE username = uname
AND password = upasswd;

IF i > 0 THEN
dbms_output.put_line('Access Granted');
ELSE
dbms_output.put_line('Access Denied');
END IF;
END validate_user;
/

set serveroutput on

exec validate_user('Tom Kyte', 'top_secret_password');

exec validate_user('Mark Rittman', 'i dont know');

exec validate_user('Dan Morgan', '''i_dont_know'' or ''x = x''');

DBMS_ASSERT Demo

CREATE TABLE pwd (
userid NUMBER(5),
password VARCHAR2(20));

INSERT INTO pwd (userid, password) VALUES (1, 'Ellison');
INSERT INTO pwd (userid, password) VALUES (2, 'Hurd');
INSERT INTO pwd (userid, password) VALUES (3, 'Katz');

CREATE OR REPLACE PROCEDURE get_pwd(uid IN pwd.userid%TYPE, tab IN user_tables.table_name%TYPE, retval OUT pwd.password%TYPE) AUTHID DEFINER IS
ret pwd.password%TYPE;
str CONSTANT VARCHAR2(60) := 'SELECT a.password FROM sqlobj a WHERE a.userid = :b1';

stmt CONSTANT VARCHAR2(90) := REPLACE(str, 'sqlobj', SYS.DBMS_ASSERT.SQL_OBJECT_NAME(tab));
BEGIN
NULL;
dbms_output.put_line(stmt);
EXECUTE IMMEDIATE stmt INTO retval USING uid;
END get_pwd;
/

DECLARE
x VARCHAR2(200);
BEGIN
get_pwd(2, 'PWD', x);
dbms_output.put_line(x);
END;
/

DECLARE
x VARCHAR2(200);
BEGIN
get_pwd(2, 'BAD', x);
dbms_output.put_line(x);
END;
/