Warning: Toxic SQL!
w h i t e s p a c e
SQL Injection still a go-to technique
Still A Go-To Technique as of 2014
The answer is complicated. There are agreed upon practices for Oracle, published in whitepapers, which will effectively make SQLI not possible. But these standards have themselves been a moving target over the years as hacker ingenuity and Oracle features evolved. Also, they assume a specific application design. Further, vulnerabilities within PHP and loosely secured but developer-convenient techniques such as client-side lookup databases can move the sheriff responsibilities from where DBAs would like them to be, straight into the arms of more carefree web developers. There are even friendly how-to sites available out there for amateur injecters and their posse.
Oracle is far from the most popular backend choice for web-driven apps among the open-source faithful. Free databases such as MySQL, MariaDB, and others with differing capabilities and string-handling escape rules are popular practice beds for injection pupils. Open-source applications can expose a higher degree of vulnerability because potential intruders can sometimes preview the relevant SQL requests before attempting to manipulate them, whereas normally an intruder is in the position of having to systematically guess about the SQL code when it is secured within a DB-resident stored procedure. According to the Integrigy consultancy group, Oracle is immune to certain SQL weaknesses which some other DB vendors are exposed to because of idiosyncracies of their SQL languages. For example, MySQL permits the convenient INTO OUTFILE syntax within SELECT statements, SQLServer and PostgreSQL both support multiple statement executions (semi-colon delineated), and SQLServer also offers an explicit EXECUTE statement. All of these have been frequently parlayed into techniques used for SQLI attacks. Still, there are many well-known instances of successful injection attacks against Oracle databases.
It should also be grasped that SQLI is often not a standalone intruder technique, but is employed as one component tool during an extended hunting scenario filled with illicit tactics. But it is still the go-to technique in 2014. SQLI lives! Here’s a look at some notable past injection break-ins, along with a great comprehensive data-viz of known breeches of all sorts over the past decade, searchable by technique.
How Injection Works
SQLI tries to exploit requests for user input within many-tiered software applications, essentially by substituting cleverly disguised ‘rogue’ SQL for expected literals enroute to the database.
SQL Injection comic via XKCD.COM
w h i t e s p a c e
| ORIGINAL APPLICATION SQL | EXPECTED | INJECTED SQL | DATA BREACH |
|---|---|---|---|
| SELECT ssn, acct# FROM user_reg WHERE userid = ’36dieterN$’ AND passkey = ???? |
password | ‘pswd1234’ OR ‘X’=’X’; - - | User’s SSN and ACCT# |
| SELECT prod_id, inventory, cost, fee FROM prods_asia WHERE prod_name LIKE ‘% ???? |
first few letters of product name |
Tires’ UNION SELECT * FROM dba_users WHERE username LIKE ‘%’; - - |
Access info for user and priveleged accounts |
| SELECT * FROM all_users WHERE created = ' ' ' ||p_date||' ' ' '; |
date variable | ALTER SESSION SET nls_date_format = ‘dd-mon-yyyy”‘ ’ or ‘ ’a’ ’ = ‘ ’a” ’; |
Same DB privileges as procedure caller/owner |
| SELECT TRANSLATE (‘????‘, ’0123456789ABCDEFGHIJ … VWXYZ’, ’0123456789′) FROM dual; |
zip code | SELECT TRANSLATE (‘ ’ || UTL_HTTP.REQUEST ( ‘http://192.168.1.1/’ ) || ‘‘, ’0123456789ABCDEFGHIJ … VWXYZ’, ’0123456789′) FROM dual; |
Web Server pages stolen |
Discussion of Techniques
A few facts should emerge from inspecting these injection examples. First, the hacker has to guess what the underlying SQL query is behind the request for input. It is normally not exposed; so a trial-and-error poking sequence is typical. Second, the more time and energy a hacker devotes to mastering a particular database vendor’s documentation and syntax specifics, the more creative their attacks can become. This is especially evident in the 3rd example. The first two cases rely upon well known, even standardized, appends to SQL commands. OR ‘X’ = ‘X’ (or equivalently OR 1=1) simply evaluates as always TRUE, which in effect bypasses the WHERE clause. The UNION SELECT… technique allows the hacker to join a free query of his choosing to the original SQL.
The 3rd example is more clever though, relying upon a less than obvious implicit datatype conversion in the SQL for Oracle DATE variables, and also upon the fact that the string conversions and display formats for these date variables are normally customizable at the user session level. The hacker imposes an unorthodox but perfectly legit string ‘format’ for the date input, which is then interpreted at run time to alter the SQL request. This method, which would escape the notice of many seasoned Oracle developers, was put forth in a comment by a reader of Tom Kyte’s blog. As pointed out there, it is easy to append many kinds of SQL requests in this NLS date format backdoor, such as code to help map out the database schema (table and column names). This exact example is admittedly a stretch for illustration purposes, since using a creation date (ALL_USERS.CREATED) is a clumsy imprecise way of getting to a specific database user account — especially when time of day is truncated. But it reveals the string-to-date implicit conversion vulnerability. If you’re intrigued by all this, you can delve into further injection lore minutae involving numeric format conversion, instead of date, here.
The 4th example depicts what is known as Function Call Injection. Oracle is shipped with about 200 supplied packages of PL/SQL utilities, consisting of well over 1000 callable functions for use in custom code. What’s different about functions as a SQLI weapon is that they rely much less upon awareness about the application — meaning less guesswork before hackers achieve results. All sorts of other mischief is possible via these functions. Some packages can issue operating system calls; others can manipulate database activity or data. The UTL_HTTP call shown will retrieve a page from the web server. Note that the original statement is not subject to the more usual types of SQL injection; only the function call injection method can expose it’s vulnerability.
Oracle-Specific Countermeasures
In Oracle, the #1 SQLI countermeasure is religously employing bind variables! We can almost say that this one measure is sufficient to defeat all SQLI possibilities. The sensible way to architect this is to place all user-facing application SQL (using mandatory bindvar coding standards) within stored procedures. This design offers several other advantages, but it also seems to have led to the confused and errant ‘received wisdom’ that: using stored procedures alone will defeat injection attacks. Not true. The advantages of a strict stored procedure design include ease of code maintenance and inspection, the chance to strictly control the access grants and privileges for the procedure owner, whose identity can be safely concealed within the database, ease of standardizing, and centrally located usage auditing. There are also opportunities for code encryption and wrappers for compiled versions. But — it is still very possible to inject SQL into Oracle stored procedures whenever bind variables are not used to accept external input. It may be that the uncertainty about the effectiveness of bind variables within stored procedures arises because of the way these things are implemented within SQL Server and MySQL (which together comprise a much larger proportion of all web-facing database installations than does Oracle).
In the PL/SQL example below, the stored procedure on the left is exposed to the NLS_DATE_FORMAT manipulation technique described before, because the passed-in string is not validated but merely concatenated. The evaluation of the constructed SQL statement occurs only later at execution time, when it is assumed valid. With the bind variable version, no SQL injection is possible. The date string cannot be manipulated into excess SQL commands. For Java developers there is a similar bind variable technique, and it is also injection-proof:
w h i t e s p a c e
| LANGUAGE | WITHOUT BIND VARIABLES (AT RISK) | WITH BIND VARIABLES (SECURE) |
|---|---|---|
| PL/SQL PROC | CREATE OR REPLACE PROCEDURE without_bindvar (p_date IN DATE) AS c1 SYSREFCURSOR; l_sql LONG; BEGIN l_sql = ‘ SELECT * FROM ALL_USERS WHERE created = ' ' ' ||p_date||' ' ' ' ; DBMS_OUTPUT.PUT_LINE(l_sql); OPEN c1 FOR l_sql; . . . |
CREATE OR REPLACE PROCEDURE with_bindvar (p_date IN DATE) AS CURSOR c1 IS SELECT * FROM all_users WHERE created = p_date ; BEGIN OPEN c1; . . . |
| JAVA / JDBC | String email = request.getParameter(“email”); PreparedStatement pstmt = conn.prepareStatement( ”INSERT INTO cust_acct(email_1) VALUES (‘ “+ email + ” ’)”); pstmt.setString(1, email); pstmt.execute(); pstmt.close(); |
String email = request.getParameter(“email”); PreparedStatement pstmt = conn.prepareStatement( ”INSERT INTO cust_acct(email_1) VALUES (?)”); pstmt.setString(1, email); pstmt.execute(); pstmt.close(); |
The only reason we have to say 99.9999% effective instead of 100% regarding the bind variable technique in Oracle is because sometimes it is not possible to use bind variables. I myself have never seen this in practice, and would want to be seriously convinced in every case where this is claimed. But in any case, it is claimed. And in such cases, developers generally must rely upon Dynamic SQL, which involves building the eventually executed SQL string via concatenating parts of it together. Part of this concatenated SQL string is solicited from the web user, and this is the crack in the castle wall for the injection hacker.
Accepting a string of live user input into a database call which is not mediated by a bind variable forces the application developer to perform data validation. At the risk of sounding old school, you might think this is simply de rigeur, but the news headlines tell otherwise. Validation code can sometimes get tricky and convoluted. This is where the Oracle package DBMS_ASSERT comes in handy. The various calls within this package work as filters. They return an error message if the argument doesn’t pass muster, otherwise they simply return the argument itself — sometimes improved in various ways, for example, quote-delimited. ASSERT functions not only will validate input strings, but also will check whether an intended database schema name or object name is legitimate, which can help detect phishing:
w h i t e s p a c e
AS ‘SANITIZED’ FROM dual;
SANITIZED
============
’Stephenson’
rob@XE_11g2> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(‘O”Shaughnessey’)
AS ‘SANITIZED’ FROM dual;
SANITIZED
===============
’O’Shaugnessey’
rob@XE_11g2> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(‘Stephenson” OR ”1”=”1’)
AS ‘SANITIZED’ FROM dual;
*
ORA-06502: PL/SQL: numeric or value error
rob@XE_11g2> SELECT DBMS_ASSERT.SIMPLE_SQL_NAME(‘Bad_Guess_Table_Name’)
AS ‘VALIDATED’ FROM dual;
*
ORA-44003: invalid SQL name
rob@XE_11g2> SELECT DBMS_ASSERT.SCHEMA_NAME(‘Bad_Guess_Owner_Name’)
AS ‘VALIDATED’ FROM dual;
*
ORA-44001: invalid SQL name
w h i t e s p a c e
An authoritative Oracle whitepaper which lays out the right defense for SQLI distinguishes three types of literals which need be considered in the case where dynamic SQL is soliciting user input without bind variables. They are: text literals, date literals (datetime), and numeric literals. Each of these three types are handled differently when validating input. Here is a distilled summary of the recommendations assuming a PL/SQL development environment (the basic principles can be generalized):
Text Literals
• Replace each singleton occurrence of the singlequote character with two consecutive singlequote characters.
• Concatenate a singlequote character before and after the resulting value.
• Assert safety of result with DBMS_ASSERT.ENQUOTE_LITERAL()
Datetime Literals
• Use TO_CHAR(string, ‘format’) to compose a SQL datetime string t from input. Value for format must adhere to specific application spec.
• Concatenate a singlequote character before and after the resulting value.
• Assert safety of result with DBMS_ASSERT.ENQUOTE_LITERAL()
• Compose the date predicate in the SQL statement using TO_DATE(t, ‘format’) ensuring that the format mask is identical to what was previously used to build t.
Numeric Literals
• Use TO_CHAR(string, ‘TM’, ‘.,’) to convert the input string. This explicitly 3-parameter version specifies: number, format, and NLS. The final two parameters are the safest defaults when only ‘number’ is provided.
Final Say
Part of the response to the SQLI threat lies with the database and part with the front end, and things go best when hybrid design teams and code review squads are used. Even when bind variables inside stored procedures are the accepted developer practices for soliciting web input, it’s a very good idea for web application developers to be thinking defensively about validating all input before passing it to the database. Still, DBAs will want to assume the worst about the verification capabilities of external application tiers. To maximize their defensive posture, DBAs can REVOKE PUBLIC access for all Oracle supplied packages. It’s especially good to read up on those supplied packages frequently implicated in Oracle SQLI attacks. Historically, these have included: DBMS_JAVA_TEST, DBMS_LOCK, DBMS_PIPE, DBMS_RANDOM, UTL_FILE, UTL_HTTP, UTL_SMTP, and UTL_TCP. DBAs should also be aware which custom PL/SQL packages are granted PUBLIC and consider ways to restrict them. They can employ fine-grained auditing to help defeat and be quickly alerted to phishing expeditions.
Integrigy study of admin accounts in 120 Oracle databases
MySQL Considerations
This post has focused upon injection hacks from the point of view of Oracle databases. But certain Oracle features make it immune to some kinds of injection techniques compared with other database systems. Also, the sheer popularity of MySQL in the web arena, plus it’s intimate connection with PHP have lead numerous developers in the direction of this platform. Considering too, that MySQL is nowadays an Oracle product, I will devote a future post to taking a look at MySQL-specific SQLI.
~RS
Hearing the Oracle 
Very interesting information.I am going to reblog this for you.
Reblogged this on Truth Troubles.
thanks… you should be aware this slightly aged info at this point, the basics will still hold but there could easily be new countermeasure developments.