Hearing the Oracle

Home » Featured » What is SQL-Injection?

What is SQL-Injection?

Enter email address to receive notifications of new posts by email.


Warning: Toxic SQL

Warning: Toxic SQL!

I wasn’t especially surprised to read not long ago that Russian hackers had succeeded in stealing an estimated 1.2 billion login/password credentials for online websites. It seems to have become accepted that internet security is hopelessly more porous than originally envisioned, and that more large-scale breaches involving well known companies exist than are publicized. What piqued my curiosity, however, was a press report that the chief technique used for this record-shattering theft was SQL Injection: a technique which I had supposed to be long ago defeated. It turns out that although basic preventive measures had been worked out towards the end of last century, in practice SQL Injection (SQLI) is still a very popular and fruitful technique. Is this apparent contradiction due strictly to developer laxity, or are other factors involved?
w h i t e s p a c e
SQL Injection still a go-to technique

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

SQL Injection comic via XKCD.COM

User input fields might be either poorly filtered or simply treated as unchecked literal replacement strings. The frontend code (often) merely passes whatever literal string is supplied through to the backend. In other cases it may construct SQL queries on the fly inserting the supplied user input, unvarnished (JDBC). Or the database code might simply accept the passed string naively as an item to be concatenated within a stored SQL statement stub (PL/SQL). In Oracle, this is known as dynamic SQL. You can gain a very quick hands-on understanding of how SQLI works by reading and working through an injection hack walkthrough such as this excellent one given by the Unixwiz blog. Although it’s hack scenario is going up against a real-world SQLServer database, the code used is generic enough to illustrate a similar attempt against Oracle. It depicts successively more intrusive breaches, progressing from stealing column and table names, then on to usernames and passwords, and finally culminating in creating a hidden privileged account — all from the innocent starting point of a commonplace ‘forgot my login’ dialog! Below are collected a summary of common SQLI sample techniques:

w h i t e s p a c e

 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
    ’0123456789ABCDEFGHIJ … VWXYZ’,
 FROM dual;
 zip code  SELECT TRANSLATE (‘
    ( ‘’ ) || ‘
    ’0123456789ABCDEFGHIJ … VWXYZ’,
 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

 without_bindvar (p_date IN DATE)
    l_sql LONG;
    l_sql = ‘
       WHERE created = ' ' ' ||p_date||' ' ' ' ;
    OPEN c1 FOR l_sql;
    . . .
 with_bindvar (p_date IN DATE) 
    CURSOR c1 IS
    SELECT * FROM all_users WHERE created = p_date ;
    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);
 String email = request.getParameter(“email”);
 PreparedStatement pstmt = conn.prepareStatement(
    ”INSERT INTO cust_acct(email_1)
    VALUES (?)”);
 pstmt.setString(1, email);

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



   rob@XE_11g2> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(‘O”Shaughnessey’)


   rob@XE_11g2> SELECT DBMS_ASSERT.ENQUOTE_LITERAL(‘Stephenson” OR ”1”=”1’)

   ORA-06502: PL/SQL: numeric or value error

   rob@XE_11g2> SELECT DBMS_ASSERT.SIMPLE_SQL_NAME(‘Bad_Guess_Table_Name’)

   ORA-44003: invalid SQL name

   rob@XE_11g2> SELECT DBMS_ASSERT.SCHEMA_NAME(‘Bad_Guess_Owner_Name’)

   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

Integrigy study of admin accounts in 120 Oracle databases

Also, a companion intrusion technique for injection hackers makes use of well documented administrative accounts shipped with most Oracle databases. While installation sites are pretty good at resetting the default passwords for the more common of these accounts, some of the obscure, less frequently used ones often retain their default logins — as can be seen in the graphic at right from an Integrigy study of 120 Oracle installations. DBAs should know about all default credentials and reset them to something else.

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.



  1. oldpoet56 says:

    Very interesting information.I am going to reblog this for you.

    • stolzyblog says:

      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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: