PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

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''');
 
Related Topics
Delete
Insert
Native Dynamic SQL
Update
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [361 users online]    © 2010 psoug.org