\/* MD \/ \u5bcc\u6587\u672c\u4e2d\u7684 .toc\uff08\u542b\u535a\u5ba2\u56ed\u642c\u5bb6\u7b49\u5d4c\u5957\u7ed3\u6784\uff09\uff1b.toc-box \u5728\u4fa7\u680f\uff0c\u4e0d\u53d7\u5f71\u54cd *\/
\n #content_views .toc,
\n \/* \u7f16\u8f91\u5668\u5e38\u5728\u76ee\u5f55\u524d\u540e\u63d2\u5165\u7a7a p\uff08:empty \u4ecd\u5360 20px\uff09\uff0c\u4e00\u5e76\u53bb\u6389\u907f\u514d\u9876\u7a7a\u9699 *\/
\n #content_views.markdown_views > p:empty:has(+ .toc),
\n #content_views.markdown_views > .toc + p:empty,
\n \/* \u5bcc\u6587\u672c\u65e7\u7248\u76ee\u5f55\u6807\u8bb0 *\/
\n #content_views.htmledit_views #main-toc,
\n #content_views.htmledit_views #hr-toc,
\n #content_views.htmledit_views p[id*=\\”-toc\\”] {
\n display: none !important;
\n }
\n \/* \u76ee\u5f55\u53bb\u6389\u540e\uff0c\u7d27\u8ddf\u7684\u9996\u4e2a\u6807\u9898\u4e0d\u518d\u591a\u51fa\u4e00\u5757\u4e0a\u8fb9\u8ddd *\/
\n #content_views.markdown_views > .toc + h1,
\n #content_views.markdown_views > .toc + h2,
\n #content_views.markdown_views > .toc + h3,
\n #content_views.markdown_views > .toc + h4,
\n #content_views.markdown_views > .toc + p:empty + h1,
\n #content_views.markdown_views > .toc + p:empty + h2,
\n #content_views.markdown_views > .toc + p:empty + h3,
\n #content_views.markdown_views > .toc + p:empty + h4 {
\n margin-top: 0 !important;
\n }<\/p>\n
— =====================================================================
\n— Oracle 21c : Dynamic Query Building in SQL
\n— Techniques, Security, and Best Practices
\n— Safe dynamic SQL (parameterized queries, stored procedures, runtime
\n— filters, SQL-injection controls) for an international jewelry
\n— mid-platform spanning OA\/ERP\/CRM\/HRM\/MES\/SCM\/WMS\/KMS.
\n—
\n— Series topic #21. Self-contained + idempotent. 17 self-checks.
\n— Conventions (learned across the 21-topic series):
\n— * All object names start with a LETTER (DQ_); Oracle rejects '_'-leading
\n— unquoted identifiers with ORA-00911.
\n— * PROMPT \/ comments are pure ASCII.
\n— * Password lives only in the shell $P variable, never in this file.
\n— * Cleanup loop wraps each DROP in an inner EXCEPTION handler so a
\n— transient lock on one object never aborts the rest.
\n— =====================================================================
\nSET ECHO OFF FEED OFF PAGES 0 SERVEROUT ON SIZE 1000000 LINES 200 DEFINE OFF
\nWHENEVER SQLERROR CONTINUE<\/p>\n
— =====================================================================
\n— 1. Idempotent cleanup
\n— =====================================================================
\nDECLARE
\n PROCEDURE ddrop(p_sql VARCHAR2) IS
\n BEGIN
\n EXECUTE IMMEDIATE p_sql;
\n EXCEPTION
\n WHEN OTHERS THEN NULL; — object may not exist \/ briefly locked
\n END;
\nBEGIN
\n FOR r IN (SELECT table_name FROM user_tables WHERE table_name LIKE 'DQ%') LOOP
\n ddrop('DROP TABLE '||r.table_name||' CASCADE CONSTRAINTS PURGE');
\n END LOOP;
\n FOR r IN (SELECT object_name, object_type FROM user_objects
\n WHERE object_name LIKE 'DQ%'
\n AND object_type IN ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY','VIEW','SEQUENCE','TYPE')) LOOP
\n ddrop('DROP '||r.object_type||' '||r.object_name);
\n END LOOP;
\n ddrop('DROP TABLE dq_val PURGE');
\n BEGIN DELETE FROM plan_table WHERE statement_id='dq_p'; COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END;
\nEND;
\n\/<\/p>\n
— =====================================================================
\n— 2. Dimensions: 8 business domains + 10 currencies + 12 regions
\n— =====================================================================
\nCREATE TABLE dq_domain (
\n domain_id NUMBER PRIMARY KEY,
\n code VARCHAR2(10) UNIQUE,
\n name VARCHAR2(40)
\n);
\nINSERT INTO dq_domain VALUES (1,'OA','Office Automation');
\nINSERT INTO dq_domain VALUES (2,'ERP','Enterprise Resource Planning');
\nINSERT INTO dq_domain VALUES (3,'CRM','Customer Relationship Mgmt');
\nINSERT INTO dq_domain VALUES (4,'HRM','Human Resource Mgmt');
\nINSERT INTO dq_domain VALUES (5,'MES','Manufacturing Execution');
\nINSERT INTO dq_domain VALUES (6,'SCM','Supply Chain Mgmt');
\nINSERT INTO dq_domain VALUES (7,'WMS','Warehouse Mgmt');
\nINSERT INTO dq_domain VALUES (8,'KMS','Knowledge Mgmt');<\/p>\n
CREATE TABLE dq_currency (
\n cur_code VARCHAR2(3) PRIMARY KEY,
\n name VARCHAR2(30),
\n rate_to_usd NUMBER
\n);
\nINSERT INTO dq_currency VALUES ('USD','US Dollar',1);
\nINSERT INTO dq_currency VALUES ('EUR','Euro',1.08);
\nINSERT INTO dq_currency VALUES ('GBP','Pound',1.27);
\nINSERT INTO dq_currency VALUES ('JPY','Yen',0.0067);
\nINSERT INTO dq_currency VALUES ('CNY','Yuan',0.14);
\nINSERT INTO dq_currency VALUES ('HKD','HK Dollar',0.128);
\nINSERT INTO dq_currency VALUES ('AUD','Aussie',0.66);
\nINSERT INTO dq_currency VALUES ('BRL','Real',0.18);
\nINSERT INTO dq_currency VALUES ('INR','Rupee',0.012);
\nINSERT INTO dq_currency VALUES ('AED','Dirham',0.27);
\nINSERT INTO dq_currency VALUES ('SGD','Sing Dollar',0.74);
\nINSERT INTO dq_currency VALUES ('ZAR','Rand',0.054);<\/p>\n
CREATE TABLE dq_region (
\n region_id NUMBER PRIMARY KEY,
\n region_name VARCHAR2(30),
\n country VARCHAR2(30),
\n tz_name VARCHAR2(40),
\n cur_code VARCHAR2(3),
\n hub_lon NUMBER,
\n hub_lat NUMBER,
\n CONSTRAINT fk_dqreg_cur FOREIGN KEY(cur_code) REFERENCES dq_currency(cur_code)
\n);
\nINSERT INTO dq_region VALUES (1,'North America','USA','America\/New_York','USD',-74.0,40.7);
\nINSERT INTO dq_region VALUES (2,'Western Europe','Germany','Europe\/Berlin','EUR',13.4,52.5);
\nINSERT INTO dq_region VALUES (3,'UK & Ireland','UK','Europe\/London','GBP',-0.13,51.5);
\nINSERT INTO dq_region VALUES (4,'Japan','Japan','Asia\/Tokyo','JPY',139.7,35.7);
\nINSERT INTO dq_region VALUES (5,'China','China','Asia\/Shanghai','CNY',121.5,31.2);
\nINSERT INTO dq_region VALUES (6,'Hong Kong','Hong Kong','Asia\/Hong_Kong','HKD',114.2,22.3);
\nINSERT INTO dq_region VALUES (7,'Australia','Australia','Australia\/Sydney','AUD',151.2,-33.9);
\nINSERT INTO dq_region VALUES (8,'Brazil','Brazil','America\/Sao_Paulo','BRL',-46.6,-23.5);
\nINSERT INTO dq_region VALUES (9,'India','India','Asia\/Kolkata','INR',77.2,28.6);
\nINSERT INTO dq_region VALUES (10,'UAE','UAE','Asia\/Dubai','AED',55.3,25.2);
\nINSERT INTO dq_region VALUES (11,'Singapore','Singapore','Asia\/Singapore','SGD',103.8,1.35);
\nINSERT INTO dq_region VALUES (12,'South Africa','South Africa','Africa\/Johannesburg','ZAR',28.0,-26.2);<\/p>\n
— =====================================================================
\n— 3. Main tables
\n— dq_site : 6000 branch\/service points across 8 domains, 12 regions
\n— dq_txn : 12000 transactions, INTERVAL-partitioned by local_day
\n— (partition pruning is key at trillion scale)
\n— =====================================================================
\nCREATE TABLE dq_site (
\n site_id NUMBER PRIMARY KEY,
\n system_code VARCHAR2(10),
\n region_id NUMBER,
\n site_name VARCHAR2(40),
\n country VARCHAR2(30),
\n cur_code VARCHAR2(3),
\n amount_usd NUMBER(14,2),
\n amount_orig NUMBER(14,2),
\n created_utc TIMESTAMP WITH TIME ZONE,
\n local_day DATE,
\n CONSTRAINT fk_dqsite_dom FOREIGN KEY(system_code) REFERENCES dq_domain(code),
\n CONSTRAINT fk_dqsite_reg FOREIGN KEY(region_id) REFERENCES dq_region(region_id),
\n CONSTRAINT fk_dqsite_cur FOREIGN KEY(cur_code) REFERENCES dq_currency(cur_code)
\n);<\/p>\n
INSERT INTO dq_site (site_id, system_code, region_id, site_name, country, cur_code,
\n amount_usd, amount_orig, created_utc, local_day)
\nSELECT
\n g.n,
\n d.code,
\n MOD(g.n,12)+1,
\n 'SITE_'||g.n,
\n r.country,
\n r.cur_code,
\n ROUND(DBMS_RANDOM.VALUE(1000,500000),2),
\n ROUND(DBMS_RANDOM.VALUE(1000,500000),2),
\n FROM_TZ(CAST(DATE '2026-06-01' + MOD(g.n,30) AS TIMESTAMP),'UTC'),
\n TRUNC(CAST(FROM_TZ(CAST(DATE '2026-06-01' + MOD(g.n,30) AS TIMESTAMP),'UTC')
\n AT TIME ZONE r.tz_name AS DATE))
\nFROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL<=6000) g
\nJOIN dq_domain d ON d.domain_id = MOD(g.n,8)+1
\nJOIN dq_region r ON r.region_id = MOD(g.n,12)+1;<\/p>\n
CREATE TABLE dq_txn (
\n txn_id NUMBER PRIMARY KEY,
\n site_id NUMBER,
\n system_code VARCHAR2(10),
\n region_id NUMBER,
\n cur_code VARCHAR2(3),
\n amount_orig NUMBER(14,2),
\n amount_usd NUMBER(14,2),
\n created_utc TIMESTAMP WITH TIME ZONE,
\n local_day DATE,
\n CONSTRAINT fk_dqtxn_site FOREIGN KEY(site_id) REFERENCES dq_site(site_id),
\n CONSTRAINT fk_dqtxn_dom FOREIGN KEY(system_code) REFERENCES dq_domain(code),
\n CONSTRAINT fk_dqtxn_reg FOREIGN KEY(region_id) REFERENCES dq_region(region_id)
\n)
\nPARTITION BY RANGE (local_day)
\nINTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
\n(PARTITION p_init VALUES LESS THAN (DATE '2026-06-01'));<\/p>\n
INSERT INTO dq_txn (txn_id, site_id, system_code, region_id, cur_code,
\n amount_orig, amount_usd, created_utc, local_day)
\nSELECT
\n g.n,
\n MOD(g.n,6000)+1,
\n s.system_code,
\n s.region_id,
\n s.cur_code,
\n ROUND(DBMS_RANDOM.VALUE(10,5000),2),
\n ROUND(ROUND(DBMS_RANDOM.VALUE(10,5000),2) * c.rate_to_usd, 2),
\n s.created_utc + NUMTODSINTERVAL(MOD(g.n,1440),'MINUTE'),
\n s.local_day
\nFROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL<=12000) g
\nJOIN dq_site s ON s.site_id = MOD(g.n,6000)+1
\nJOIN dq_currency c ON c.cur_code = s.cur_code;<\/p>\n
— =====================================================================
\n— 4. Indexes (B-tree + composite) + statistics — high-concurrency
\n— =====================================================================
\nCREATE INDEX dq_ix_sys_reg ON dq_site(system_code, region_id);
\nCREATE INDEX dq_ix_site_loc ON dq_site(local_day);
\nCREATE INDEX dq_ix_txn_day ON dq_txn(local_day);
\nCREATE INDEX dq_ix_txn_cur ON dq_txn(cur_code);<\/p>\n
BEGIN
\n DBMS_STATS.GATHER_TABLE_STATS(USER,'DQ_SITE');
\n DBMS_STATS.GATHER_TABLE_STATS(USER,'DQ_TXN');
\nEND;
\n\/<\/p>\n
— =====================================================================
\n— 5. Validation helpers + check infrastructure
\n— =====================================================================<\/p>\n
— Whitelist validator for dynamic identifiers (column names in ORDER BY,
\n— dynamic predicates). Rejects anything that is not a real column of dq_site.
\nCREATE OR REPLACE FUNCTION dq_validate_col(p_col VARCHAR2) RETURN VARCHAR2 IS
\n v_ok VARCHAR2(30);
\nBEGIN
\n SELECT column_name INTO v_ok FROM user_tab_columns
\n WHERE table_name='DQ_SITE' AND column_name = UPPER(p_col);
\n RETURN v_ok;
\nEXCEPTION
\n WHEN NO_DATA_FOUND THEN
\n RAISE_APPLICATION_ERROR(-20001,'Invalid column for dynamic SQL: '||p_col);
\nEND;
\n\/<\/p>\n
— Identifier sanitizer (defense-in-depth, mirrors DBMS_ASSERT.SIMPLE_SQL_NAME).
\n— Raises if the identifier contains anything outside [A-Za-z0-9_$#] or
\n— does not start with a letter.
\nCREATE OR REPLACE FUNCTION dq_assert_name(p_ident VARCHAR2) RETURN VARCHAR2 IS
\nBEGIN
\n IF p_ident IS NULL OR LENGTH(p_ident)>30 THEN
\n RAISE_APPLICATION_ERROR(-20003,'Invalid identifier');
\n END IF;
\n IF NOT REGEXP_LIKE(p_ident,'^[A-Za-z][A-Za-z0-9_$#]*$') THEN
\n RAISE_APPLICATION_ERROR(-20003,'Invalid identifier: '||p_ident);
\n END IF;
\n RETURN p_ident;
\nEND;
\n\/<\/p>\n
CREATE TABLE dq_val (chk_id VARCHAR2(10), descr VARCHAR2(80), pass CHAR(1), detail VARCHAR2(120));
\nCREATE OR REPLACE PROCEDURE dq_chk(p_id VARCHAR2, p_desc VARCHAR2, p_pass NUMBER, p_detail VARCHAR2) IS
\nBEGIN
\n INSERT INTO dq_val(chk_id, descr, pass, detail) VALUES (p_id, p_desc, CASE WHEN p_pass=1 THEN 'Y' ELSE 'N' END, p_detail);
\nEND;
\n\/<\/p>\n
— =====================================================================
\n— 6. Safe dynamic SQL building blocks
\n— =====================================================================<\/p>\n
— (A) Parameterized dynamic lookup — bind variable, immune to injection.
\nCREATE OR REPLACE FUNCTION dq_param_lookup(p_name VARCHAR2) RETURN NUMBER IS
\n v_cnt NUMBER;
\nBEGIN
\n EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dq_site WHERE site_name = :n' INTO v_cnt USING p_name;
\n RETURN v_cnt;
\nEND;
\n\/<\/p>\n
— (B) DANGEROUS pattern (string concatenation) — shown ONLY to demonstrate
\n— the vulnerability. Passing "x' OR '1'='1" makes it return ALL rows.
\n— NEVER use this form with user input.
\nCREATE OR REPLACE FUNCTION dq_concat_danger(p_name VARCHAR2) RETURN NUMBER IS
\n v_cnt NUMBER;
\nBEGIN
\n EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dq_site WHERE site_name = '''||p_name||'''' INTO v_cnt;
\n RETURN v_cnt;
\nEND;
\n\/<\/p>\n
— (C) Runtime filter builder — native dynamic SQL (EXECUTE IMMEDIATE \/ OPEN…FOR)
\n— with each optional filter using TWO distinct placeholders (:x_n for the
\n— IS NULL guard, :x_v for the typed compare), bound once each. Dynamic
\n— ORDER BY is whitelist-guarded.
\nCREATE OR REPLACE FUNCTION dq_count_dynamic(
\n p_system_code VARCHAR2 DEFAULT NULL,
\n p_region_id NUMBER DEFAULT NULL,
\n p_min_amount NUMBER DEFAULT NULL,
\n p_currency VARCHAR2 DEFAULT NULL
\n) RETURN NUMBER IS
\n v_sql VARCHAR2(2000);
\n v_cnt NUMBER;
\nBEGIN
\n — Native dynamic SQL (DBMS_SQL is denied here via ORA-29471). Each optional
\n — filter uses TWO distinct placeholders: :x_n for the (IS NULL) guard and :x_v
\n — for the typed comparison. EXECUTE IMMEDIATE binds by POSITION, so the USING
\n — list must hold one value per placeholder occurrence (8 here). Reusing one name
\n — for both the untyped IS NULL and the typed compare raises ORA-00932.
\n v_sql := 'SELECT COUNT(*) FROM dq_site'
\n ||' WHERE (:sc_n IS NULL OR system_code = :sc_v)'
\n ||' AND (:rg_n IS NULL OR region_id = :rg_v)'
\n ||' AND (:am_n IS NULL OR amount_usd >= :am_v)'
\n ||' AND (:cu_n IS NULL OR cur_code = :cu_v)';
\n EXECUTE IMMEDIATE v_sql INTO v_cnt
\n USING p_system_code, p_system_code, p_region_id, p_region_id, p_min_amount, p_min_amount, p_currency, p_currency;
\n RETURN NVL(v_cnt,0);
\nEND;
\n\/<\/p>\n
— (D) Refcursor variant of the runtime filter (for UIs \/ reports).
\nCREATE OR REPLACE PROCEDURE dq_search_dynamic(
\n p_system_code VARCHAR2 DEFAULT NULL,
\n p_region_id NUMBER DEFAULT NULL,
\n p_min_amount NUMBER DEFAULT NULL,
\n p_currency VARCHAR2 DEFAULT NULL,
\n p_order_col VARCHAR2 DEFAULT 'site_id',
\n p_rc OUT SYS_REFCURSOR
\n) IS
\n v_sql VARCHAR2(4000);
\n v_order VARCHAR2(30) := dq_validate_col(p_order_col); — whitelist
\nBEGIN
\n v_sql := 'SELECT site_id, system_code, region_id, site_name, cur_code, amount_usd FROM dq_site'
\n ||' WHERE (:sc_n IS NULL OR system_code = :sc_v)'
\n ||' AND (:rg_n IS NULL OR region_id = :rg_v)'
\n ||' AND (:am_n IS NULL OR amount_usd >= :am_v)'
\n ||' AND (:cu_n IS NULL OR cur_code = :cu_v)'
\n ||' ORDER BY '||v_order;
\n — OPEN…FOR USING cannot infer bind types from an ALL-NULL list (ORA-00932);
\n — when no filter is supplied, open without binds.
\n IF p_system_code IS NULL AND p_region_id IS NULL AND p_min_amount IS NULL AND p_currency IS NULL THEN
\n OPEN p_rc FOR v_sql;
\n ELSE
\n OPEN p_rc FOR v_sql
\n USING p_system_code, p_system_code, p_region_id, p_region_id, p_min_amount, p_min_amount, p_currency, p_currency;
\n END IF;
\nEND;
\n\/<\/p>\n
— (E) AUTHID CURRENT_USER package — dynamic SQL runs with the INVOKER's
\n— privileges (security best practice for multi-tenant mid-platforms).
\n— Includes a RESULT_CACHE + PARALLEL function for trillion-scale rollups.
\nCREATE OR REPLACE PACKAGE dq_pkg AUTHID CURRENT_USER IS
\n FUNCTION region_sales(p_region NUMBER) RETURN NUMBER RESULT_CACHE;
\n FUNCTION dynamic_filter(p_col VARCHAR2, p_val VARCHAR2) RETURN NUMBER;
\nEND dq_pkg;
\n\/
\nCREATE OR REPLACE PACKAGE BODY dq_pkg IS
\n FUNCTION region_sales(p_region NUMBER) RETURN NUMBER RESULT_CACHE IS
\n v NUMBER;
\n BEGIN
\n EXECUTE IMMEDIATE 'SELECT \/*+ PARALLEL(4) *\/ SUM(amount_usd) FROM dq_txn WHERE region_id = :r'
\n INTO v USING p_region;
\n RETURN NVL(v,0);
\n END;
\n FUNCTION dynamic_filter(p_col VARCHAR2, p_val VARCHAR2) RETURN NUMBER IS
\n v_cnt NUMBER;
\n v_col VARCHAR2(30) := dq_validate_col(p_col); — whitelist guard
\n BEGIN
\n EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM dq_site WHERE '||v_col||' = :v' INTO v_cnt USING p_val;
\n RETURN v_cnt;
\n END;
\nEND dq_pkg;
\n\/<\/p>\n
— =====================================================================
\n— 7. Demos : dynamic query building, security, best practices
\n— =====================================================================<\/p>\n
PROMPT — A. Sample sites (geography\/domain mix)
\nSELECT site_id, system_code, region_id, site_name, cur_code, amount_usd
\nFROM dq_site WHERE ROWNUM<=5;<\/p>\n
PROMPT — B. Parameterized dynamic lookup (bind variable => safe)
\nSELECT dq_param_lookup('SITE_1') exact_match FROM dual;<\/p>\n
PROMPT — C. Runtime filter: ERP sites in region 1, min amount 100000
\nSELECT dq_count_dynamic(p_system_code=>'ERP', p_region_id=>1, p_min_amount=>100000) erp_r1_cnt FROM dual;<\/p>\n
PROMPT — D. DBMS_SQL dynamic filter: CRM sites only
\nSELECT dq_count_dynamic(p_system_code=>'CRM') crm_cnt FROM dual;<\/p>\n
PROMPT — E. Whitelist-guarded dynamic ORDER BY (top 5 by amount_usd), region 1
\nDECLARE
\n rc SYS_REFCURSOR;
\n v_id dq_site.site_id%TYPE; v_sys dq_site.system_code%TYPE; v_reg dq_site.region_id%TYPE;
\n v_name dq_site.site_name%TYPE; v_cur dq_site.cur_code%TYPE; v_amt dq_site.amount_usd%TYPE;
\nBEGIN
\n dq_search_dynamic(p_order_col=>'amount_usd', p_region_id=>1, p_rc=>rc);
\n LOOP
\n FETCH rc INTO v_id, v_sys, v_reg, v_name, v_cur, v_amt;
\n EXIT WHEN rc%NOTFOUND OR rc%ROWCOUNT>5;
\n DBMS_OUTPUT.PUT_LINE('site='||v_id||' sys='||v_sys||' usd='||v_amt);
\n END LOOP;
\n CLOSE rc;
\nEND;
\n\/<\/p>\n
PROMPT — F. SQL injection: safe (bind) vs DANGEROUS (concat). DO NOT USE concat.
\nDECLARE
\n v_safe NUMBER; v_danger NUMBER;
\n inj VARCHAR2(50) := 'x'' OR ''1''=''1';
\nBEGIN
\n v_safe := dq_param_lookup(inj); — bind => literal match => 0
\n v_danger := dq_concat_danger(inj); — concat => predicate hijack => all rows
\n DBMS_OUTPUT.PUT_LINE('SAFE(bind) rows = '||v_safe||' (injection neutralized, expected 0)');
\n DBMS_OUTPUT.PUT_LINE('DANGER(concat) row= '||v_danger||' (LEAK: returns every row!)');
\nEND;
\n\/<\/p>\n
PROMPT — G. Identifier validation: DBMS_ASSERT-style guard rejects a malicious identifier
\nDECLARE
\n v VARCHAR2(30);
\nBEGIN
\n BEGIN
\n v := dq_assert_name('site_id; DROP TABLE dq_site–');
\n DBMS_OUTPUT.PUT_LINE('UNEXPECTED: accepted malicious identifier');
\n EXCEPTION
\n WHEN OTHERS THEN
\n DBMS_OUTPUT.PUT_LINE('REJECTED malicious identifier (good): '||SQLERRM);
\n END;
\n v := dq_assert_name('site_id');
\n DBMS_OUTPUT.PUT_LINE('Accepted valid identifier: '||v);
\nEND;
\n\/<\/p>\n
PROMPT — H. AUTHID CURRENT_USER package: whitelist dynamic column filter
\nSELECT dq_pkg.dynamic_filter('system_code','WMS') wms_cnt FROM dual;
\nSELECT ROUND(dq_pkg.region_sales(5),2) cny_region_sales_usd FROM dual;<\/p>\n
PROMPT — I. EXPLAIN PLAN for a single-day dynamic query (partition pruning)
\nDECLARE
\n v_sql VARCHAR2(4000);
\nBEGIN
\n BEGIN DELETE FROM plan_table WHERE statement_id='dq_p'; END;
\n v_sql := 'SELECT \/*+ FULL(t) *\/ COUNT(*) FROM dq_txn t WHERE local_day = DATE ''2026-06-15'' AND system_code=''ERP''';
\n EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID=''dq_p'' FOR '||v_sql;
\n FOR r IN (SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options||' '||object_name plan_line
\n FROM plan_table
\n START WITH id=0 AND statement_id='dq_p'
\n CONNECT BY PRIOR id = parent_id AND statement_id='dq_p') LOOP
\n DBMS_OUTPUT.PUT_LINE(r.plan_line);
\n END LOOP;
\nEND;
\n\/<\/p>\n
PROMPT — J. International timezone: rows whose LOCAL day differs from UTC day
\nSELECT COUNT(*) rows_shifted_to_local_day
\nFROM dq_site WHERE local_day <> TRUNC(CAST(created_utc AS DATE));<\/p>\n
PROMPT — K. Multi-currency report: only SUM(amount_usd) is comparable across currencies
\nSELECT cur_code, COUNT(*) txns, ROUND(SUM(amount_usd),2) usd_total
\nFROM dq_txn GROUP BY cur_code ORDER BY cur_code;<\/p>\n
PROMPT — L. RESULT_CACHE + PARALLEL rollup matches static sum (trillion-scale pattern)
\nSELECT ROUND(dq_pkg.region_sales(3),2) hkg_region_usd,
\n (SELECT ROUND(SUM(amount_usd),2) FROM dq_txn WHERE region_id=3) static_usd
\nFROM dual;<\/p>\n
PROMPT — M. All-NULL runtime filters => full set returned
\nSELECT dq_count_dynamic() full_set FROM dual;<\/p>\n
PROMPT — N. Domain (OA\/ERP\/CRM\/HRM\/MES\/SCM\/WMS\/KMS) distribution across 6000 sites
\nSELECT system_code, COUNT(*) cnt FROM dq_site GROUP BY system_code ORDER BY system_code;<\/p>\n
— =====================================================================
\n— 8. Self-checks V1..V17
\n— =====================================================================
\nDECLARE
\n v1 NUMBER; v2 NUMBER; v3 NUMBER; v4 NUMBER; v5 NUMBER; v6 NUMBER;
\n v7 NUMBER; v8ok NUMBER:=0; v9ok NUMBER:=0; v10 VARCHAR2(30); v11 NUMBER;
\n v12 NUMBER; v13a NUMBER; v13b NUMBER; v13g NUMBER; v14 NUMBER; v15 NUMBER;
\n v16pkg NUMBER; v16static NUMBER; v17 NUMBER;
\n v_inj VARCHAR2(50) := 'x'' OR ''1''=''1';
\n v_erp NUMBER; v_crm NUMBER;
\nBEGIN
\n — V1 row count
\n SELECT COUNT(*) INTO v1 FROM dq_site;
\n dq_chk('V1','dq_site row count = 6000', CASE WHEN v1=6000 THEN 1 ELSE 0 END, 'rows='||v1);
\n — V2 domains
\n SELECT COUNT(*) INTO v2 FROM dq_domain;
\n dq_chk('V2','8 domains (OA..KMS)', CASE WHEN v2=8 THEN 1 ELSE 0 END, 'domains='||v2);
\n — V3 txn rows
\n SELECT COUNT(*) INTO v3 FROM dq_txn;
\n dq_chk('V3','dq_txn row count = 12000', CASE WHEN v3=12000 THEN 1 ELSE 0 END, 'rows='||v3);
\n — V4 parameterized lookup (by site_name) matches static count
\n SELECT COUNT(*) INTO v_erp FROM dq_site WHERE site_name='SITE_1';
\n v4 := dq_param_lookup('SITE_1');
\n dq_chk('V4','param lookup by name = static', CASE WHEN v4=v_erp THEN 1 ELSE 0 END, 'dyn='||v4||' stat='||v_erp);
\n — V5 dynamic filter region=1
\n SELECT COUNT(*) INTO v5 FROM dq_site WHERE region_id=1;
\n dq_chk('V5','dyn filter region=1 matches', CASE WHEN dq_count_dynamic(p_region_id=>1)=v5 THEN 1 ELSE 0 END, 'cnt='||v5);
\n — V6 dynamic filter CRM
\n SELECT COUNT(*) INTO v_crm FROM dq_site WHERE system_code='CRM';
\n dq_chk('V6','dyn filter CRM matches', CASE WHEN dq_count_dynamic(p_system_code=>'CRM')=v_crm THEN 1 ELSE 0 END, 'cnt='||v_crm);
\n — V7 injection neutralized by bind
\n v7 := dq_param_lookup(v_inj);
\n dq_chk('V7','bind neutralizes injection (=0)', CASE WHEN v7=0 THEN 1 ELSE 0 END, 'cnt='||v7);
\n — V8 assert rejects malicious identifier
\n BEGIN
\n v10 := dq_assert_name('site_id; DROP TABLE dq_site–');
\n EXCEPTION
\n WHEN OTHERS THEN v8ok := 1;
\n END;
\n dq_chk('V8','assert rejects malicious ident', v8ok, 'ok='||v8ok);
\n — V9 whitelist rejects invalid column
\n BEGIN
\n v10 := dq_validate_col('not_a_column');
\n EXCEPTION
\n WHEN OTHERS THEN v9ok := 1;
\n END;
\n dq_chk('V9','whitelist rejects bad column', v9ok, 'ok='||v9ok);
\n — V10 whitelist accepts valid column
\n v10 := dq_validate_col('site_name');
\n dq_chk('V10','whitelist accepts valid column', CASE WHEN v10='SITE_NAME' THEN 1 ELSE 0 END, 'col='||v10);
\n — V11 dynamic ORDER BY returns smallest site_id first
\n SELECT site_id INTO v11 FROM (SELECT site_id FROM dq_site ORDER BY site_id) WHERE ROWNUM=1;
\n dq_chk('V11','whitelist ORDER BY works', CASE WHEN v11=1 THEN 1 ELSE 0 END, 'min_id='||v11);
\n — V12 timezone shift exists
\n SELECT COUNT(*) INTO v12 FROM dq_site WHERE local_day <> TRUNC(CAST(created_utc AS DATE));
\n dq_chk('V12','timezone local_day shifts exist', CASE WHEN v12>0 THEN 1 ELSE 0 END, 'shifted='||v12);
\n — V13 multi-currency normalization
\n SELECT COUNT(*) INTO v13g FROM dq_txn;
\n SELECT ROUND(SUM(amount_usd),2), ROUND(SUM(amount_orig),2) INTO v13a, v13b FROM dq_txn;
\n dq_chk('V13','multi-cur: usd vs orig differ but sum>0',
\n CASE WHEN v13a>0 AND v13b>0 AND ABS(v13a-v13b)>1 THEN 1 ELSE 0 END,
\n 'usd='||v13a||' orig='||v13b||' g='||v13g);
\n — V14 stats gathered
\n SELECT num_rows INTO v14 FROM user_tables WHERE table_name='DQ_SITE';
\n dq_chk('V14','stats num_rows > 0', CASE WHEN v14>0 THEN 1 ELSE 0 END, 'num_rows='||v14);
\n — V15 all-NULL filter => full set
\n v15 := dq_count_dynamic();
\n dq_chk('V15','all-NULL filter = full set', CASE WHEN v15=6000 THEN 1 ELSE 0 END, 'cnt='||v15);
\n — V16 AUTHID package result_cache matches static
\n SELECT ROUND(SUM(amount_usd),2) INTO v16static FROM dq_txn WHERE region_id=1;
\n v16pkg := ROUND(dq_pkg.region_sales(1),2);
\n dq_chk('V16','AUTHID pkg RESULT_CACHE matches', CASE WHEN v16pkg=v16static THEN 1 ELSE 0 END, 'pkg='||v16pkg||' stat='||v16static);
\n — V17 partition pruning present in single-day plan
\n BEGIN DELETE FROM plan_table WHERE statement_id='dq_p'; END;
\n EXECUTE IMMEDIATE 'EXPLAIN PLAN SET STATEMENT_ID=''dq_p'' FOR SELECT \/*+ FULL(t) *\/ COUNT(*) FROM dq_txn t WHERE local_day = DATE ''2026-06-15'' AND system_code=''ERP''';
\n SELECT COUNT(*) INTO v17 FROM plan_table WHERE statement_id='dq_p' AND operation LIKE '%PARTITION%';
\n dq_chk('V17','partition pruning in plan', CASE WHEN v17>0 THEN 1 ELSE 0 END, 'part_ops='||v17);
\nEND;
\n\/<\/p>\n
— =====================================================================
\n— Q. Self-check summary
\n— =====================================================================
\nSET PAGES 100 LINES 200
\nSELECT chk_id, descr, pass, detail FROM dq_val ORDER BY chk_id;
\nSELECT 'CHECK SUMMARY: '||SUM(CASE WHEN pass='Y' THEN 1 ELSE 0 END)||'\/'||COUNT(*)||' PASS' AS result
\nFROM dq_val;<\/p>\n
— =====================================================================
\n— R. Conclusions (12, ASCII) — Dynamic Query Building best practices
\n— =====================================================================
\nPROMPT 1) ALWAYS parameterize dynamic SQL with bind variables (the USING clause of EXECUTE IMMEDIATE \/ OPEN…FOR). This is the single most important SQL-injection control. NOTE: DBMS_SQL can be denied (ORA-29471) in locked-down instances — native dynamic SQL is always available.
\nPROMPT 2) Never concatenate user input into SQL text. The dq_concat_danger demo leaks ALL rows on "x' OR '1'='1" — do not ship that pattern.
\nPROMPT 3) For dynamic identifiers (column names, table names, ORDER BY), use a WHITELIST (validate against user_tab_columns) — never a blacklist.
\nPROMPT 4) DBMS_ASSERT (SIMPLE_SQL_NAME \/ QUALIFIED_SQL_NAME \/ ENQUOTE_LITERAL) is the built-in sanitizer; a manual regex guard also works if it is rejected by default.
\nPROMPT 5) For conditional runtime filters with native dynamic SQL, give each optional filter TWO distinct placeholders — :x_n for the (IS NULL) guard and :x_v for the typed compare — and bind each value once per occurrence. Never reuse one name for both the untyped IS NULL and the typed compare (ORA-00932); EXECUTE IMMEDIATE binds by position, so supply one value per placeholder occurrence.
\nPROMPT 6) Use AUTHID CURRENT_USER for packages that build dynamic SQL in multi-tenant platforms so they run with the invoker's privileges (least privilege).
\nPROMPT 7) RESULT_CACHE + PARALLEL hints turn repeatable rollups into trillion-scale, low-latency queries; pair with current statistics.
\nPROMPT 8) Gather statistics (DBMS_STATS) so the optimizer picks the right plan for dynamically built predicates.
\nPROMPT 9) INTERVAL\/RANGE partitioning on local_day enables partition pruning; EXPLAIN PLAN shows the PARTITION RANGE operation.
\nPROMPT 10) Store UTC (TIMESTAMP WITH TIME ZONE) and derive local_day with AT TIME ZONE so global reports respect each region's calendar day.
\nPROMPT 11) Normalize multi-currency with SUM(amount_usd); never SUM(amount_orig) across different currencies — amounts are not additive.
\nPROMPT 12) Oracle unquoted identifiers must start with a letter; '_name' => ORA-00911. Prefix dynamic objects with DQ_. Add EXIT so SQL*Plus terminates cleanly.<\/p>\n
EXIT<\/p>\n","protected":false},"excerpt":{"rendered":"
\/* MD \/ \u5bcc\u6587\u672c\u4e2d\u7684 .toc\uff08\u542b\u535a\u5ba2\u56ed\u642c\u5bb6\u7b49\u5d4c\u5957\u7ed3\u6784\uff09\uff1b.toc-box \u5728\u4fa7\u680f\uff0c\u4e0d\u53d7\u5f71\u54cd *\/#content_views .toc,\/* \u7f16\u8f91\u5668\u5e38\u5728\u76ee\u5f55\u524d\u540e\u63d2\u5165\u7a7a p\uff08:empty \u4ecd\u5360 20px\uff09\uff0c\u4e00\u5e76\u53bb\u6389\u907f\u514d\u9876\u7a7a\u9699 *\/#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views > .toc + p:empty,\/* \u5bcc\u6587\u672c\u65e7\u7248\u76ee\u5f55\u6807\u8bb0 *\/#content<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[604,768,323,2330,100],"topic":[],"class_list":["post-122531","post","type-post","status-publish","format-standard","hentry","category-server","tag-oracle","tag-sql","tag-323","tag-2330","tag-100"],"yoast_head":"\n