The best cure of course is prevention!

Protecting against SQL Injection

On the surface, protection against SQL injection appears to be easy to implement but, in fact, it is not as easy as it looks. The solutions fall into two distinct areas:

• Do not allow dynamic SQL that uses concatenation, or at least filter the input values and check for special characters such as quote symbols.
• Use the principle of least privilege and ensure that the users created for the applications have the privileges needed and all extra privileges (such as PUBLIC ones) are not available.

This section cannot go into great detail; such a discussion would constitute an entire article in itself. However, certain basic measures can be taken. These actions fall into two sections:

• Review the application source code. The code can be written in many different languages, such as PHP, JSP, java, PL/SQL VB, etc., so the solutions vary. However, they all follow a similar pattern. Review the source code for dynamic SQL where concatenation is used. Find the call that parses the SQL or executes it. Check back to where values are entered. Ensure that input values are validated and that quotes are matched and metacharacters are checked. Reviewing source code is a task that is specific to the language used.
• Secure the database and ensure that all excess privileges are revoked.

Some other simple tips to follow include:

• If possible, do not use dynamic PL/SQL. The potential for damage is much greater than for dynamic SQL, as then there is scope to execute any SQL, DDL, PL/SQL etc.
• If dynamic PL/SQL is necessary then use bind variables.
• If PL/SQL is used use AUTHID CURRENT_USER so that the PL/SQL runs as the logged in user and not the creator of the procedure, function or package.
• If concatenation is necessary then use numeric values for the concatenation part. This way strings cannot be passed in to add SQL.
• If concatenation is necessary then check the input for malicious code, i.e. check for union in the string passed in or metacharacters such as quotes.
• For dynamic SQL if it is necessary use bind variables. An example is shown below:

We first need to alter our simple procedure to allow the dynamic part passed in to use a bind variable. This is shown here:

create or replace procedure get_cust_bind (lv_surname in varchar2)
is
        type cv_typ is ref cursor;
        cv cv_typ;
        lv_phone        customers.customer_phone%type;
        lv_stmt         varchar2(32767):=''''select customer_phone ''''||
                                ''''from customers ''''||
                                ''''where customer_surname=:surname'''';
begin
        dbms_output.put_line(''''debug:''''||lv_stmt);
        open cv for lv_stmt using lv_surname;
        loop
                fetch cv into lv_phone;
                exit when cv%notfound;
                dbms_output.put_line(''''::''''||lv_phone);
        end loop;
        close cv;
exception
        when others then
                dbms_output.put_line(sqlcode||sqlerrm);
end get_cust_bind;
/       

First we execute with a genuine value, in this case “Clark”, to show that the correct records are returned. We then we try to SQL inject this procedure and find it doesn’t work:

SQL> exec get_cust_bind(''''Clark'''');
debug:select customer_phone from customers where customer_surname=:surname
::999444888
::999777888

PL/SQL procedure successfully completed.

SQL> exec get_cust_bind(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''
x'''');
debug:select customer_phone from customers where customer_surname=:surname

Some more pointers:

• Encrypt sensitive data so that it cannot be viewed.
• Revoke all PUBLIC privileges where possible from the database
• Do not allow access to UTL_FILE, DBMS_LOB, DBMS_PIPE, DBMS_OUTPUT, UTL_HTTP,UTL_SMTP or any other standard or application packages that allow access to the O/S.
• Change database default passwords.
• Run the listener as a non-privileged user.
• Ensure that minimum privileges are granted to application users.
• Restrict PL/SQL packages that can be accessed from apache.
• Remove all example scripts and programs from the Oracle install.

Final thoughts

I hope that this article has given an overview of some of the possibilities of SQL injecting Oracle and done so with simple examples that most readers can try. Again, SQL injection is a relatively simple technique and on the surface protecting against it should be fairly simple; however, auditing all of the source code and protecting dynamic input is not trivial, neither is reducing the permissions of all applications users in the database itself. Be vigilant, grant what is needed, and try and reduce dynamic SQL to the minimum.

Pete Finnigan is a freelance consultant specialising in Oracle and security of Oracle. Pete is currently working in the UK financial sector and has recently completed the new Oracle security step-by-step guide for the SANS institute. Pete has many years of development and administration experience in many languages. Pete is regarded as one of the worlds leading experts on Oracle security.

Watch for the forthcoming book The SANS Institute Oracle Security Step-by-step – A survival guide for Oracle security written by Pete Finnigan with consensus achieved by experts from over 53 organizations with over 230 years of Oracle and security experience. Due to be published in the next few weeks by the SANS Institute.

• If PL/SQL is used use AUTHID CURRENT_USER so that the PL/SQL runs as the logged in user and not the creator of the procedure, function or package.
• If concatenation is necessary then use numeric values for the concatenation part. This way strings cannot be passed in to add SQL.
• If concatenation is necessary then check the input for malicious code, i.e. check for union in the string passed in or metacharacters such as quotes.
• For dynamic SQL if it is necessary use bind variables. An example is shown below:

We first need to alter our simple procedure to allow the dynamic part passed in to use a bind variable. This is shown here:

create or replace procedure get_cust_bind (lv_surname in varchar2)
is
        type cv_typ is ref cursor;
        cv cv_typ;
        lv_phone        customers.customer_phone%type;
        lv_stmt         varchar2(32767):=''''select customer_phone ''''||
                                ''''from customers ''''||
                                ''''where customer_surname=:surname'''';
begin
        dbms_output.put_line(''''debug:''''||lv_stmt);
        open cv for lv_stmt using lv_surname;
        loop
                fetch cv into lv_phone;
                exit when cv%notfound;
                dbms_output.put_line(''''::''''||lv_phone);
        end loop;
        close cv;
exception
        when others then
                dbms_output.put_line(sqlcode||sqlerrm);
end get_cust_bind;
/       

First we execute with a genuine value, in this case “Clark”, to show that the correct records are returned. We then we try to SQL inject this procedure and find it doesn’t work:

SQL> exec get_cust_bind(''''Clark'''');
debug:select customer_phone from customers where customer_surname=:surname
::999444888
::999777888

PL/SQL procedure successfully completed.

SQL> exec get_cust_bind(''''x'''''''' union select username from all_users where ''''''''x''''''''=''''''''
x'''');
debug:select customer_phone from customers where customer_surname=:surname

Some more pointers:

• Encrypt sensitive data so that it cannot be viewed.
• Revoke all PUBLIC privileges where possible from the database
• Do not allow access to UTL_FILE, DBMS_LOB, DBMS_PIPE, DBMS_OUTPUT, UTL_HTTP,UTL_SMTP or any other standard or application packages that allow access to the O/S.
• Change database default passwords.
• Run the listener as a non-privileged user.
• Ensure that minimum privileges are granted to application users.
• Restrict PL/SQL packages that can be accessed from apache.
• Remove all example scripts and programs from the Oracle install.

Final thoughts

I hope that this article has given an overview of some of the possibilities of SQL injecting Oracle and done so with simple examples that most readers can try. Again, SQL injection is a relatively simple technique and on the surface protecting against it should be fairly simple; however, audit

上一页  [1] [2] [3]  下一页