When performing a penetration test or red team engagement, finding SQL injection is always a welcome sight. Many tests are being done “under the radar” in attempts to not get caught or gradually increase efforts and noise to test incident response teams, MSSPs, and other monitoring and detection controls in place. With that being said, a handful of engagements have ended with the testers being caught due to rash decisions on what SQL queries to run via SQL injection. An often overlooked and very important aspect of running a SQL query is whether or not it is blocking. When reading or writing data, are other queries waiting to run while yours is executing? Most of the SQL injection tutorials found on the internet fail to address this important aspect. In the case of a simple “UNION SELECT” injection, one might have an injection payload similar to this:

‘ UNION SELECT null,Username,Password,null,null FROM Users--

Assuming that the data is being display on a web page, we are selecting all of the Usernames and Passwords from the Users table. How many users are in the Users table? Unless you specifically check beforehand, the above query is dangerous to run. What if there are 10 million users? Your query will likely take a very long time, and consume a lot of server-side resources. While your query is processing, depending upon the Database Management Systems (DBMS), you may be preventing other queries from running. With our MS SQL example above, we can rewrite it to be non-blocking like this:

‘ UNION SELECT null,Username,Password,null,null FROM Users WITH (NOLOCK)--

Now our query will allow others to preempt it. Locking is a good thing as it prevents data corruption. For example, you wouldn’t want to use NOLOCK on a query that is writing data. More information on transaction isolation levels can be found at https://msdn.microsoft.com/en-us/library/ms709374(v=vs.85).aspx Other DBMS’ have similar features. In MySQL (http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html), you can use the following:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

In Oracle, readers of data won’t be blocked by writers, and writers won’t be blocked by readers. More on how this is done and isolation levels in Oracle can be found at http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#insertedID3PostgreSQL, uses MVCC (http://www.postgresql.org/docs/8.4/interactive/mvcc-intro.html) and readers do not conflict with writers or other readers. In DB2, you can use what is called an uncommitted read:

SELECT * FROM Users WITH UR

Avoiding noisy automated tools like SqlMap and utilizing queries much like a sniper utilizes a single or a few shots will help in not getting caught by system administrators and NOCs or SOCs with performance degradation alerts. A quick example of such a query might be to target specific tables instead of dumping them all and then going through them and all of their columns. In MS SQL server, looking for a table that contains a column name that contains a specific keyword such as “password”, “ssn”, “session”, etc. can be done using a simple query like this:

SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE LOWER(COLUMN_NAME) LIKE ‘%password%’

Notice that we use LOWER() for the column name since they are case sensitive in MS SQL server. Of course, to get data back in an injection, the above query would likely need to be in a sub query in a “union-select” or error-based SQL injection scenario. The next time you identify a SQL injection vulnerability and want to quickly dump data, perhaps you should think twice about the performance impact you may have on systems. Denial of service conditions and getting caught on your engagement because you failed to use a non-blocking query probably isn’t how you envisioned you’d be detected. This blog post was written by Scott White of TrustedSec.