反恐精英之動態SQL和SQL隱碼攻擊-SQL隱碼攻擊-防衛SQL隱碼攻擊-驗證檢查

-- 11g12_07_13.prc

CREATE OR REPLACE PROCEDURE raise_emp_salary (column_value  NUMBER, emp_column VARCHAR2, amount NUMBER)

IS

    v_column  VARCHAR2(30);

    sql_stmt  VARCHAR2(200);

BEGIN

    -- 檢測輸入的列名有效性

    SELECT column_name INTO v_column FROM USER_TAB_COLS

    WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column;

    sql_stmt := 'UPDATE employees SET salary = salary + :b1 WHERE '

    || DBMS_ASSERT.ENQUOTE_NAME(v_column, FALSE) || ' = :b2';

    EXECUTE IMMEDIATE sql_stmt USING amount, column_value;

    -- 如果列名有效

    IF SQL%ROWCOUNT > 0 THEN

        DBMS_OUTPUT.PUT_LINE('更新列: ' || emp_column || ' = ' || column_value || '的員工薪水');

    END IF;

    -- 如果列名無效

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            DBMS_OUTPUT.PUT_LINE ('無效列: ' || emp_column);

END raise_emp_salary;

--11g12_07_13.tst

--測試前先檢視相關的資料

SELECT employee_id, department_id, salary FROM employees

WHERE employee_id = 112 OR department_id = 110;

DECLARE

    plsql_block  VARCHAR2(500);

BEGIN

  -- 從動態SQL塊呼叫

    plsql_block :=

        'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;';

    EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10;

  -- 從動態SQL語句呼叫

  --EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'

  --USING 112, 'EMPLOYEE_ID', 10;

END;

1次執行:

2次執行