oracle sql inject - ghdrako/doc_snipets GitHub Wiki
Bind arguments
Dynamic SQL using concatenated inputs can substitute the concatenated parameter with a placeholder in the dynamic SQL or dynamic PL/SQL. At runtime, the placeholder can be replaced with an actual argument through the USING clause in the same positional order. Bind variables can successfully substitute the placeholders for the value operands in the WHERE clause.
CREATE OR REPLACE PROCEDURE P_SHOW_DEPT
(P_ENAME VARCHAR2)
IS
CUR SYS_REFCURSOR;
l_ename VARCHAR2(100);
l_deptno NUMBER;
BEGIN
/*Open ref cursor for a dynamic query using a bind variable*/
OPEN CUR FOR 'SELECT ename, deptno
FROM employees
WHERE ename = :bind' USING P_ENAME;
LOOP
*Fetch and display the results*/
FETCH CUR INTO l_ename, l_deptno;
EXIT WHEN cur%notfound;
DBMS_OUTPUT.PUT_LINE(l_ename ||'--'|| l_deptno);
END LOOP;
END;
/
DBMS_ASSERT(10g) package to sanitize the user inputs.
The DBMS_ASSERT package is owned by SYS and contains seven subprograms.
| Subprograms | Description |
|---|---|
| ENQUOTE_LITERAL function | Encloses a string literal within single quotes |
| ENQUOTE_NAME function | Encloses the input string in double quotes |
| NOOP functions | Overloaded function returns the value without any checking; does no operation |
| QUALIFIED_SQL_NAME function | Verifies that the input string is a qualified SQL name |
| SCHEMA_NAME function | Verifies that the input string is an existing schema name |
| SIMPLE_SQL_NAME function | Verifies that the input string is a simple SQL name |
| SQL_OBJECT_NAME function | Verifies that the input parameter string is a qualiied SQL identiier of an existing SQL object |
Example
SELECT DBMS_ASSERT.ENQUOTE_LITERAL('KING') FROM DUAL;
'KING'
SELECT DBMS_ASSERT.ENQUOTE_NAME('KING') FROM DUAL;
"KING"
SELECT DBMS_ASSERT.SCHEMA_NAME('PLSQL') from dual;
ORA-44001: invalid schema ...
Make use of DBMS_ASSERT speciic exceptions to identify the actual exception raised by the bad inputs. The exceptions ORA44001 to ORA44004 are the DBMS_ASSERT exceptions:
- ORA44001 stands for sys.dbms_assert.INVALID_SCHEMA_NAME
- ORA44003 stands for sys.dbms_assert.INVALID_SQL_NAME
- ORA44002 stands for sys.dbms_assert.INVALID_OBJECT_NAME
- ORA44004 stands for sys.dbms_assert.QUALIFIED_SQL_NAME