SQL Injection

Structured Query Language was originally an invention of IBM, and is now an ANSI standard. With SQL,
someone who is not a programmer can make requests to a database server and perhaps even get back the information requested. Of course, SQL is really not that simple, and it is possible for a naive SQL user to make very resource intensive SQL queries, or, more likely, get nothing back but incomprehensible error messages.

For our purposes, all you really need to know about is a very basic database query request, SELECT. The basic syntax for a SELECT request is "SELECT _select-list_ From _table-name_ Where _condition_". This roughly translates to: "Select the data matching the select-list from table-name that matches this condition." In an ASP script, the SELECT string might be built first, so it can be passed later to a Microsoft SQL Server using a line like:

sql_query = "SELECT * FROM PRODUCTS
WHERE ID = " & Request.QueryString("CAT")

The call Request.QueryString("CAT") extracts the value of the Web form variable "CAT" so it can be appended as the SELECT condition, for example "CAT = blenders". Now, SQL server can return all rows matching the category "blenders".

But, there is a problem with this script fragment. The value of the "CAT" variable is blindly accepted. What if the wiley attacker had sent "blenders+OR+1=1"? Now, the condition will always be true, and all rows will be returned. If the database table named PRODUCTS contains nothing that must remain confidential, that really isn't much of a problem. But, suppose the request was for customer account information, or something else that should be kept private?

SQL Server treats the non-printing character one, represented in URL encoding as %01, as a line delimiter. When the Web server script does not check the value provided by the remote "user", the value could be "blenders%01DROP+TABLE+PRODUCTS". Dropping a table means to delete it from the database, and is probably not something you want.

The most interesting attack comes through the use of stored procedures. Stored procedures are SQL subroutines, some of which come bundled with your SQL server. For Microsoft's SQL Server, the extended stored procedure xp_cmdshell executes programs in the context of the SQL Server, which you (hopefully) have configured as some special account with few privileges (like Guest group, and Login as a Service privilege). Software Development Kits (SDK), like the ones that come with .NET, use the default account, sa, a member of the Administrators group, and while this is convenient for a programmer, it is a disaster for security.

Again, using the same SELECT example, the attacker sets the value of "CAT" to be "blenders%01EXEC+master..xp_cmdshell+ net+user+crack+secret+/ADD". If SQL Server is running as the sa user, you now have a new user account named "crack" with a password of "secret". A second SELECT statement could be used to add the "crack" user to the Administrators group.

SQL Server has other, interesting extended stored procedures, for registry access and database administration (adding users, changing passwords, etc.). If you are running MS-SQL Server, it is also possible to access MS-SQL Server, and these procedures, using pipes connecting to port 139/TCP.

By now, I am hoping that you have gotten the point. Your front end software, whether it be PL/SQL or ASP, cannot blindly accept input from remote users. You must filter the input so that it will resemble what you expect. If it is a number, check to see that the value contains only digits and dots. If it is a name, check to see that the value contains letters (and dots and dashes) that can occur in a person's name. And so on.

If this seems painfully obvious, a company running the Microsoft Developers Store Web site, under contract to Microsoft, had this very problem in January 2002 (see Resources). And they are not the only site, by any means.

One organization, the Open Web Application Security Project, is working on developing a set of filters that will block evil characters (like %01), and make it easy to configure filters that will only permit the answers you expect in a value. Of course, you can do a lot of this today, just by carefully checking the value of any variable you will be reading from the remote Web browser.

Recommended Articles

O'Reilly Network Beware SQL Injection in Web Applications [January 06, 2003]

When you're developing a web database application, you may need to build your SQL statements on the fly and send them to the database. Consider this Perl example, which queries a database using the zipcode parameter:

my $zipcode = param("zipcode");
my $sql = "SELECT * FROM PEOPLE WHERE zipcode = '$zipcode'";
my $sth = $dbh->prepare($sql); 
$sth->execute();

So, you could search for all the people in the 02881 zipcode with this URL: http://webserver/cgi-bin/search?zipcode=02881. It looks simple and safe enough, but it's easy for a remote user to subvert your intentions. Suppose a remote user sends this URL to your web server: http://webserver/cgi-bin/search?zipcode=02881'+or+'0'='0. Now, $sql looks like this:

SELECT * FROM PEOPLE
WHERE zipcode='02881' or '0' = '0'

This would return all rows from the PEOPLE table, which could be a Bad Thing. Consider this:

• If the PEOPLE table is very large, this could be a potential denial-of-service attack.
• If you have a database, such as a company roster, on your website, you'd want to let users query portions of it, but you'd prefer not to let someone download the entire thing.

Whether or not either of these cases apply to your application, the overriding concern should be that you want to stay in control of what code is executed on your server! Other databases, such as Microsoft SQL Server, have powerful stored procedures like xp_cmdshell, which runs command-line programs with the same credentials that the server runs under.

For a good overview of this problem, see the Open Web Application Security Project's page on this topic.

Direct SQL Command Injection

[PDF] SQL Injection

SecurityFocus HOME Infocus- SQL Injection and Oracle, Part Two

SecuriTeam.com ™ (SQL Injection Walkthrough)

SecuriTeam.com ™ (More Advanced SQL Injection Paper Released)

4GuysFromRolla.com - Protecting Yourself from SQL Injection ...

SQL Injection Attacks - Are You Safe-

SQL Injection Walkthrough

[PPT]SQL Injection

SQL Injection and Oracle

Is Your Web Site at Risk of SQL Injection?

SecurityFocus/HOME Infocus SQL Injection and Oracle, Part Two by Pete Finnigan last updated November 28, 2002

This is the second part of a two-part article that will examine SQL injection attacks against Oracle databases. The first installment offered an overview of SQL injection and looked at how Oracle database applications are vulnerable to this attack, and looked at some examples. This segment will look at enumerating the privileges, detecting SQL injection attacks, and protecting against SQL injection.

Enumerating the Privileges

Access to SQL inject an Oracle database is great, but what would an attacker look for to gain an advantage or a potential step up. He would, of course, need to enumerate the user he had access to and see what that user can see and do. I will show a few examples here to give the reader an idea of what is possible.

In this example, we are logged in as the user dbsnmp and the get_cust procedure has been modified to select three columns from our sample table. If we use a union to extend an existing select statement then the new SQL in the union must select the same number of columns and data types as the existing hijacked select otherwise an error occurs, see the following:

SQL> exec get_cust('x'' union select 1,''Y'' from sys.dual where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from customers
where customer_surname='x' union select 1,'Y' from sys.dual where 'x'='x'
-1789ORA-01789: query block has incorrect number of result columns

The main select has three varchar columns but we select two columns and one is a number; as a result, an error occurs. Back to enumeration, first get the objects that the user we are logged in as can see:

SQL> exec get_cust('x'' union select object_name,object_type,''x'' from user_obj
ects where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from customers
where customer_surname='x' union select object_name,object_type,'x' from
user_objects where 'x'='x'
::CUSTOMERS:TABLE:x
::DBA_DATA_FILES:SYNONYM:x
::DBA_FREE_SPACE:SYNONYM:x
::DBA_SEGMENTS:SYNONYM:x
::DBA_TABLESPACES:SYNONYM:x
::GET_CUST:PROCEDURE:x
::GET_CUST2:PROCEDURE:x
::GET_CUST_BIND:PROCEDURE:x
::PLSQ:DATABASE LINK:x   

Then get the roles that have been allocated directly to the user:

SQL> exec get_cust('x'' union select granted_role,admin_option,default_role from
 user_role_privs where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from customers
where customer_surname='x' union select granted_role,admin_option,default_role
from user_role_privs where 'x'='x'
::CONNECT:NO:YES
::RESOURCE:NO:YES
::SNMPAGENT:NO:YES

Then find out the system privileges that are granted directly to the user:

SQL> exec get_cust('x'' union select privilege,admin_option,''X'' from user_sys_
privs where ''x''=''x');
debug:select customer_phone,customer_forname,customer_surname from customers
where customer_surname='x' union select privilege,admin_option,'X' from
user_sys_privs where 'x'='x'
::CREATE PUBLIC SYNONYM:NO:X
::UNLIMITED TABLESPACE:NO:X  

Selecting from the table USER_TAB_PRIVS will give the privileges granted directly to the user on objects. There are many system views that start USER_%, these show objects and privileges that are granted to the current user as well as details about objects owned by the user. For instance, there are 168 views or tables in Oracle 8.1.7, so this gives an indication of the amount of detail that can be learned about the user you are logged in as. These USER_% views do not include all the many privileges and options available to the current user; however, besides those specifically granted, any user also can include all of the objects that have permissions granted to PUBLIC.

PUBLIC is a catch-all that is available to all users in the Oracle database. There is a good set of views, known as the ALL_% views, that is similar in construction to the USER_% views. These include every item available to the current user, including PUBLIC ones. A good place to start is the view ALL_OBJECTS, as it has a similar structure to USER_OBJECTS and will display every object and its type available to the current user. A good query to see all of the objects, their types and owner available would be:

select count(*),object_type,owner
from all_objects
group by object_type,owner    

The V$ views is also a good set of views, provided they are available to the user. These give information about the current instance, performance, parameters, and the like. V$PARAMETER, which gives all of the database instance initialization parameters, including details of the UTL_FILE directories is a good example. V$PROCESS and V$SESSION are another pair of views that will give details of current sessions and processes. These will tell the user who is logged on, where they are logged in from, and what program they are using, etc.

In conclusion to this exploration section it is worth mentioning that because I wanted to make easy examples that anyone with a copy of the Oracle RDBMS could try out, I used a PL/SQL procedure to demonstrate the techniques and obviously I had access to my source code. It made it easy for me to understand exactly the SQL I could send successfully without causing errors.

In the real world, in a Web-based environment, or in a network-based application, the source code would probably not be available. As a result, working out how to get successful SQL to send will probably require trial and error. If error messages are returned to the user either directly from the Oracle RDBMS or from the application, then it is usually possible to work out where to change the SQL. An absence of error messages makes it harder but not impossible. All of the Oracle error messages are quite well documented and are available on-line on a Unix system with the oerr command or with the HTML documentation provided with Oracle CDs on any platform. (Remember anyone can get a copy of Oracle to use to learn the product.) They are also on-line, along with the complete Oracle documentation, at http://tahiti.oracle.com/.

Having knowledge of Oracle and of the schema of the user being used is also a great advantage. Quite obviously, some of this knowledge is not hard to learn, so the lesson is that in case anyone is able to SQL inject into your database then you need to minimize what they can do, see, or access.

Detecting SQL Injection

Oracle is a large product and is applied in many diverse uses, so to say that SQL injection can be detected would be wrong; however, in some cases, it should be possible for the DBA or security admin to spot whether or not this technique is being used. If abuse is thought to be taking place then forensic investigations can be done using the redo logs. A GUI tool called Log Miner is available from Oracle to allow the redo logs to be analysed. However, this has serious restrictions: until version 9i, select statements could not be retrieved. The redo logs allow Oracle to replay all of the events that altered data in the database, this is part of the recovery functionality. It is possible to see all statements and data that has been altered. Two PL/SQL standard packages, DBMS_LOGMNR and DBMS_LOGMNR_D, are available, these packages allow the redo logs to be queried from the command line for all statements processed.

The extensive Oracle audit functionality can be utilized but, again, unless you know what you are looking for, finding evidence of SQL injection taking place could like finding a needle in a haystack. The principle of least privilege should be observed in any Oracle database so that only those privileges that are actually needed are granted to the application database users. This simplifies (minimizes) what can be legally done and, as a result, makes any actions outside the scope of these users easier to spot. For instance, if the application user should have access to seven tables and three procedures and nothing else, then using Oracle audit to record select failures on all other tables would enable an administrator to spot any attempted access to any table outside the applications realm. This can be done, for example, for one table with the following audit command:

SQL> audit select on dbsnmp.customers by access whenever not successful;

Audit succeeded.

A simple script can be built to generate the audit statements for the tables needed. There should be no real performance issues with this audit, as no other tables should be accessed by the application. As a result, it should not therefore generate audit. Of course, if someone successfully accesses a table outside the realm, it would not be captured. This is merely intended as a first step.

The same audit principles can be used to audit DDL, inserts and update failures or successes. The new SANS guide (see references) has a whole chapter on audit.

Another idea could be to watch the SQL executed and look for any dodgy SQL. A good script called peep.sq can be used to access the SQL executed from the SGA is one called from http://www.oriole.com/frameindexSA.html, search down the list of free scripts and get it. The script gives the SQL statements in the SGA with the worst performance times. It can be easily modified to remove the execution time restraints and bring back all SQL in the SGA. A script such as this can be scheduled on a regular basis and then the SQL that is returned can be used to guess if any SQL injection has been attempted. I say “guess” because it is virtually impossible to know all legal pieces of SQL an application generates; therefore, the same applies to spotting illegal ones. A good first step would be to identify all statements with “union” included or or statements with ‘x’=’x’ type lines. There could be performance issues with extracting all of the SQL from the SGA regularly!

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.
  Relevant Links

All of the code from this paper is available from the author's Web site from the scripts menu - SQL and PL/SQL option.www.petefinnigan.com

Protecting Oracle Databases Whitepaper
Aaron Newman, Application Security Inc

Hackproofing Oracle Application Servers
David Litchfield, NGSSoftware Insight Security Research

Etc

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotes :  Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce :  Bernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOS :  Programming Languages History : PL/1 : Simula 67 : C : History of GCC development :  Scripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-Month :  How to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Hater’s Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Last modified: March 12, 2019