Solution: Blind String SQL Injection

Lesson Plan Title: Blind String SQL Injection

Concept / Topic To Teach:
SQL injection attacks represent a serious threat to any database-driven site. The methods behind an attack are easy to learn and the damage caused can range from considerable to complete system compromise. Despite these risks, an incredible number of systems on the internet are susceptible to this form of attack.

Not only is it a threat easily instigated, it is also a threat that, with a little common-sense and forethought, can easily be prevented.

It is always good practice to sanitize all input data, especially data that will used in OS command, scripts, and database queries, even if the threat of SQL injection has been prevented in some other manner.

General Goal(s):
The form below allows a user to enter an account number and determine if it is valid or not. Use this form to develop a true / false test check other entries in the database.
The goal is to find the value of the field name in table pins for the row with the cc_number of 4321432143214321. The field is of type varchar, which is a string.
Put the discovered name in the form to pass the lesson. Only the discovered name should be put into the form field, paying close attention to the spelling and capitalization.

Solution:

This lesson is conceptually very similar to the previous lesson. The big difference is we are searching for a string, not a number.
We will attempt to figure out the name the same way, by injecting a boolean expression into the pre-scripted SQL query. It looks similar to the one from the previous lesson:
101 AND (SUBSTRING((SELECT name FROM pins WHERE cc_number='4321432143214321'), 1, 1) < 'H' );

We can compare characters the same way we can compare numbers. For example, N > M. However, without the SUBSTRING method, we are attempting to compare the entire string to one letter, which doesn't help us. The substring method has the following syntax:
SUBSTRING(STRING,START,LENGTH)

The expression above compares the first letter to H. It will return false and show invalid account number. Changing the boolean expression to < 'L' returns true, so we know the letter is between H and L. With a few more queries, we can determine the first letter is J. Note that capitalization matters, and it's right to assume the first letter is capitalized.

To determine the second letter, we have to change the SUBSTRING parameters to compare against the second letter. We can use this command:
101 AND (SUBSTRING((SELECT name FROM pins WHERE cc_number='4321432143214321'), 2, 1) < 'h' );

Using several more queries, we can determine the second letter is i. Note that we are comparing the second character to a lowercase h. Continue this process until you have the rest of the letters. The name is Jill. Enter this name to complete the lesson. Capitalization matters.