Without proper safeguards, applications are vulnerable to various forms of security attack. One particularly pervasive method of attack is called SQL injection.
Best example of Sql Injection .
I think he was trying to inject SQL through the traffic camera and drop the table from the database which host the application. So BEWARE , SQL INJECTION CAN HAPPEN FROM UNKNOWN WAYS. Hackers can try numerous unknown ways to bring down your database.
One of the best exhaustive article that i have read recently which was published in Oracle Magazine provides the detailed explanation on SQL Injection and different ways to handle it and make the application secure.
How to write SQL injection proof PL/SQL
Types of SQL Injection Attacks
The impact of SQL injection attacks may vary from gathering of sensitive data to manipulating database information, and from executing system-level commands to denial of service of the application. The impact also depends on the database on the target machine and the roles and privileges the SQL statement runs with.
Researchers generally divide injection attacks into three categories:
First Order Attack : The attacker can simply enter a malicious string and cause the modified code to be executed immediately.
Second Order Attack : The attacker injects into persistent storage (such as a table row) which is deemed as a trusted source. An attack is subsequently executed by another activity.
Lateral Injection : The attacker can manipulate the implicit function To_Char() by changing the values of the environment variables, NLS_Date_Format or NLS_Numeric_Characters.
How do we avoid SQL Injections
Several avoidance strategies can be employed to safeguard against, or mitigate the impact of, SQL injection attacks. Here are high-level descriptions of each of the strategies
Reduce the attack surface
Ensure that all excess database privileges are revoked and that only those routines that are intended for end-user access are exposed. Though this does not entirely eliminate SQL injection vulnerabilities, it mitigates the impact of the attacks.
Avoid dynamic SQL with concatenated input
Dynamic SQL built with concatenated input values presents the easiest entry point for SQL injections. Avoid constructing dynamic SQL this way.
Use bind arguments
Parameterize queries by using bind arguments. Bind arguments eliminate the possibility of SQL injections and enhance performance.
Filter and sanitize input.
The Oracle-supplied DBMS_ASSERT package contains a number of functions that can be used to sanitize user input and help in guarding against SQL injection in applications that use dynamic SQL built with concatenated input values.
In case your filtering requirements cannot be satisfied by the DBMS_ASSERT package, you may need to create your own filter.
Example of SQL Injection
-- Creator: Jenny Tsai-Smith
-- Description: Code sample with dynamic SQL and string concatenation.
-- Used to demonstrate 1st order attack via UNION.
SQL> conn hr
Connected.
-- This is the procedure with dynamic SQL constructed
-- via concatenation of input value.
-- Vulnerable to SQL injection!!
SQL> CREATE OR REPLACE
2 PROCEDURE GET_PHONE (p_email VARCHAR2 DEFAULT NULL)
3 AS
4 TYPE cv_emptyp IS REF CURSOR;
5 cv cv_emptyp;
6 v_phone employees.phone_number%TYPE;
7 v_stmt VARCHAR2(400);
8 BEGIN
9 v_stmt := 'SELECT phone_number FROM employees WHERE email = '''
10 || p_email || '''';
11
12 DBMS_OUTPUT.PUT_LINE('SQL statement: ' || v_stmt);
13 OPEN cv FOR v_stmt;
14 LOOP
15 FETCH cv INTO v_phone;
16 EXIT WHEN cv%NOTFOUND;
17 DBMS_OUTPUT.PUT_LINE('Phone: '||v_phone);
18 END LOOP;
19 CLOSE cv;
20
21 EXCEPTION WHEN OTHERS THEN
22 dbms_output.PUT_LINE(sqlerrm);
23 dbms_output.PUT_LINE('SQL statement: ' || v_stmt);
24 END;
25 /
Procedure created.
SQL> set serveroutput on
SQL> exec get_phone('PFAY');
SQL statement: SELECT phone_number FROM employees WHERE email = 'PFAY'
Phone: 603.123.6666
PL/SQL procedure successfully completed.
--SQL Injection done successfully
SQL> exec get_phone('x'' union select username from all_users where ''x''=''x')
SQL statement: SELECT phone_number FROM employees WHERE email = 'x' union select username from all_users where 'x'='x'
Phone: ADMIN
Phone: ANONYMOUS
Phone: APEX_PUBLIC_USER
Phone: CTXSYS
Phone: DBSNMP
Phone: DIP
Phone: EXFSYS
Phone: FLOWS_030000
Phone: FLOWS_FILES
Phone: HR
Phone: MDDATA
Phone: MDSYS
Phone: MGMT_VIEW
Phone: OLAPSYS
Phone: ORACLE_OCM
Phone: ORDPLUGINS
Phone: ORDSYS
Phone: OUTLN
Phone: OWBSYS
Phone: SCOTT
Phone: SI_INFORMTN_SCHEMA
PL/SQL procedure successfully completed.
-- This is the procedure with static SQL
-- Safe from SQL injection!!
SQL> CREATE OR REPLACE
2 PROCEDURE GET_PHONE (p_email VARCHAR2 DEFAULT NULL)
3 AS
4
5 BEGIN
6
7 FOR i IN
8 (SELECT phone_number
9 FROM employees
10 WHERE email = p_email)
11 LOOP
12 DBMS_OUTPUT.PUT_LINE('Phone: '||i.phone_number);
13 END LOOP;
14
15 END;
16 /
Procedure created.
SQL> exec get_phone('PFAY');
Phone: 603.123.6666
PL/SQL procedure successfully completed.
SQL> exec get_phone('x'' union select username from all_users where ''x''=''x');
PL/SQL procedure successfully completed.
SQL>
In Order to avoid SQL Injection use bind variables Or use DBMS_ASSERT Package to sanitize the input data.
For further reading go through the code samples provided at
http://st-curriculum.oracle.com/tutorial/SQLInjection/index.htm
Comments
Post a Comment