Bind Variables, Array/Bulk Operations, Type Mapping, LOB Streaming, and Injection Defense

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh27_bind_lab PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh27_bind_lab (  item_id NUMBER PRIMARY KEY,  tenant_code VARCHAR2(20) NOT NULL,  item_name VARCHAR2(100) NOT NULL,  amount NUMBER(12,2) NOT NULL,  created_at TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,  payload_json JSON,  attachment CLOB,  embedding VECTOR(4,FLOAT32));INSERT INTO sh27_bind_lab(  item_id,tenant_code,item_name,amount,payload_json,embedding) VALUES(  1,'TENANT_A','Compressor guide',25.50,  JSON('{"status":"ACTIVE"}'),  VECTOR('[0.9,0.8,0.1,0.0]',4,FLOAT32));INSERT INTO sh27_bind_lab(  item_id,tenant_code,item_name,amount,payload_json,embedding) VALUES(  2,'TENANT_B','Private electrical guide',75.00,  JSON('{"status":"ACTIVE"}'),  VECTOR('[0.1,0.1,0.9,0.2]',4,FLOAT32));COMMIT;

attacker_input = "TENANT_A' OR '1'='1"sql = (    "select item_id, tenant_code, item_name "    "from sh27_bind_lab "    "where tenant_code = '" + attacker_input + "'")# Resulting SQL changes the predicate and can expose all rows.

tenant_code = "TENANT_A' OR '1'='1"cur.execute(    """    select item_id, tenant_code, item_name    from sh27_bind_lab    where tenant_code = :tenant_code    """,    tenant_code=tenant_code)print(cur.fetchall())# Expected: no row for this literal tenant value.

SELECT  sql_id, executions, parse_calls, version_count,  substr(sql_text,1,140) AS sql_textFROM v$sqlWHERE sql_text LIKE  'select item_id, tenant_code, item_name%sh27_bind_lab%'ORDER BY last_active_time DESC;

rows = [    (10, "TENANT_A", "Pump guide", 10.00),    (11, "TENANT_A", "Valve guide", 20.00),    (10, "TENANT_A", "Duplicate PK", 30.00)]cur.executemany(    """    insert into sh27_bind_lab(      item_id,tenant_code,item_name,amount    ) values(:1,:2,:3,:4)    """,    rows,    batcherrors=True)for err in cur.getbatcherrors():    print(err.offset, err.message)conn.rollback()  # this lab chooses all-or-nothing

const sql = `  insert into sh27_bind_lab(    item_id, tenant_code, item_name, amount  ) values (:id, :tenant, :name, :amount)`;const binds = [  {id: 20, tenant: "TENANT_A", name: "A", amount: 1.25},  {id: 21, tenant: "TENANT_A", name: "B", amount: 2.50}];const result = await connection.executeMany(sql, binds, {  autoCommit: false,  bindDefs: {    id:     {type: oracledb.NUMBER},    tenant: {type: oracledb.STRING, maxSize: 20},    name:   {type: oracledb.STRING, maxSize: 100},    amount: {type: oracledb.NUMBER}  },  batchErrors: true,  dmlRowCounts: true});console.log(result.dmlRowCounts, result.batchErrors);await connection.rollback();

const result = await connection.execute(  `select attachment     from sh27_bind_lab    where item_id = :id`,  { id: 1 });const lob = result.rows[0][0];lob.setEncoding("utf8");lob.on("data", chunk => {  // Process/write each chunk.});lob.on("end", () => console.log("done"));lob.on("error", console.error);// Do not release the connection before the LOB stream completes.

SELECT *FROM :table_name;-- Typical result:-- ORA-00903: invalid table name

requested = user_inputif requested not in {"SH27_BIND_LAB"}:    raise ValueError("object is not allowed")if not oracledb.is_simple_sql_name(requested):    raise ValueError("invalid identifier")safe_name = oracledb.enquote_name(requested)sql = f"select count(*) from {safe_name}"

const allowed = new Set(["SH27_BIND_LAB"]);if (!allowed.has(userInput) || !oracledb.isSimpleSqlName(userInput)) {  throw new Error("object is not allowed");}const safeName = oracledb.enquoteName(userInput);const sql = `select count(*) from ${safeName}`;

DECLARE  l_table VARCHAR2(128) := :requested_table;BEGIN  IF l_table NOT IN ('SH27_BIND_LAB') THEN    RAISE_APPLICATION_ERROR(-20001,'table not allowed');  END IF;  EXECUTE IMMEDIATE    'SELECT COUNT(*) FROM ' ||    DBMS_ASSERT.SIMPLE_SQL_NAME(l_table)  INTO :row_count;END;/

DROP TABLE sh27_bind_lab PURGE;