Oracle Bind Variables
Version 11.1
General Information
Note: Without further
specification bind variables are assumed to be of character type. Replace :b1 with
TO_DATE (:b1) if working with date values or TO_NUMBER (:b1) if numeric values.
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_$mystat 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 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, gv$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 .. 5000
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 .. 5000
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 ) 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('Pete Finnigan', 'i dont know');
exec validate_user('Dan Morgan', '''i_dont_know'' or ''x = x''');