deep inside: security and tools

Dissection of a SQL injection challenge

As part of the SQL injection challenges that I developed (focusing on MySQL), one of the classic challenges (we have the same types for XSS), is asimple, yet disturbing for juniors, black-list and few controls such as partialoutput encoding.

In the case of SQLi, I decided to blacklist the following keywords (as seen during an assessment): select, union, drop, delete, insert, and, or, where,update, if, not

On top of this, I use the mysqli function that properly escapes strings mysqli_real_escape_string, and I remove all white-spaces.

The SQL commands is using a multiple queries aware driver (i.e., you can stack queries), and the injection context is fairly simple and we have something like this:

SELECT username FROM users WHERE userid=<<HERE>>

Since this is an exploitation challenge, the goal is to extract the password of a given user from this database.

Now, every time that I write a challenge, I first come up with the application and I need to break it after to make sure that there is a solution (unless the challenge is derived from what I found already in some of my previous assessments).

Anyway, here my main personal challenge was to come up with a query that would retrieve the proper data without using one of the black-listed keywords. Spaces and quotes are easy not to care about simply by using /**/ as a word separator, and we can use the hexadecimal representation of strings so that wemake sure not to use single-quotes & co. Here is a quick summary with 2similar queries:

The way I found to solve this challenge is to use MySQL prepared statements.However, I was fairly disturbed at first since I cannot use the followingsyntax in MySQL:

PREPARE st FROM 0x73656c656374202a2066726f6d207573657273;
EXECUTE st;
DEALLOCATE PREPARE st;

where 0x73656c656374202a2066726f6d207573657273 contains the query to geteverything from the users table (i.e., select * from users). The syntax of the PREPARE keyword is not flexible like any other string manipulation in MySQL, and does not allow strings with their hexadecimal representation.

The gotcha here (I wouldn't call this a trick) is to use a temporary variable assignment, and use this variable in the PREPARE construct. The finalconstruct I used is the following:

SET @v=0x73656c656374202a2066726f6d207573657273;
PREPARE st FROM @v;
EXECUTE st;
DEALLOCATE PREPARE st;

Now, putting the pieces together, and adding this into the our originalquery, we get a payload similar like this:

9999||username=0xdeadbeef;SET/**/@s=0x73656c656374202a2066726f6d207573657273;PREPARE/**/ss/**/FROM/**/@s;EXECUTE/**/ss;DEALLOCATE/**/PREPARE/**/ss;#

This construct is very similar to the solution of the challenge, but not exactly the same since we need to use the application to display the data. Therefore, in that case we need to make sure that the prepared statement willreturn only one column, etc.

Anyway, I wanted to share this since I haven't come across many references that talked about using prepared statements as SQL injection payloads...

All entries

  1. February 2013 — RSA 2013 speaking session
  2. February 2013 — HTML5 tokenization visualization
  3. September 2011 — PHP, Variable variables, Oh my!
  4. July 2011 — Dissection of a SQL injection challenge
  5. January 2010 — Yes, we need a standard to evaluate SAST, but it ain't easy...
  6. November 2009 — Data driven factory: I give you data, you give me an object...
  7. June 2009 — NIST Static Analysis Tool Exposition special publication released
  8. December 2008 — Every-day's CSRF: Sorry, I turned off your christmas tree lights
  9. August 2008 — Why the "line of code" is indeed a good metric
  10. May 2008 — Accelerate the convergence to the bug: Running the test in 16-bit
  11. February 2008 — Code review tools: the missing link (so far)
  12. January 2008 — Talk: Problems and solutions for testing web application security scanners
  13. October 2007 — IE6 And IE7 don't have compatible CSS tricks
  14. September 2007 — Source Code Obfuscation
  15. February 2007 — The return of the SVG XSS
  16. February 2007 — How you should design a test suite for Web Apps Scanners
  17. January 2007 — Test Suites for Web Application Scanners
  18. December 2006 — SVG Files: XSS attacks