Profile cover photo
Profile photo
Romain Gaucher
Application security and data analysis geek; Master of frenglish.
Application security and data analysis geek; Master of frenglish.

Romain's posts

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 a simple, yet disturbing for juniors, black-list and few controls such as partial output 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 we make sure not to use single-quotes & co.
Here is a quick summary with 2 similar 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 following syntax in MySQL:
PREPARE st FROM 0x73656c656374202a2066726f6d207573657273;
where 0x73656c656374202a2066726f6d207573657273 contains the query to get everything 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 final construct I used is the following:
SET @v=0x73656c656374202a2066726f6d207573657273;

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

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 will return 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...

Post has shared content
How not to design a CAPTCHA

Unfortunately Sony missed the entire point of a CAPTCHA. Instead of using an obfuscated image which is difficult for computers to recognize the characters, they include the CAPTCHA's unobfuscated characters in HTML and use CSS and JavaScript to make the characters appear slightly distorted.

While taking a Digital Image and Video Processing class (EE 371R at, I picked up a summer hobby of writing CAPTCHA breakers. Most CAPTCHAs can be decoded with a combination of Gaussian edge detection and XOR difference analysis with known characters. However this one falls to a simple regex.

Having some fun developing SQLi challenges for our internal training. The main issues I have so far:
- Integration of several DB (MySQL, MSSQL, PostgreSQL, etc.)
- Making sure that the challengers cannot blow the server
- Having a "not too" harsh increase of difficulty
- Allowing several ways to exploit the vulnerability even though, each challenge should be pretty much locked down

If anyone here has ideas, I'm happy to give them a try.
Wait while more posts are being loaded