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:
- Spaces bypass:
select/**/foobar/**/FROM/**/table/**/WHERE/**/user='c3'; - Single quotes:
select foobar FROM table WHERE user=0x6333;
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...