PI-2

Solved in an unusual way - or how to spent a day on this challenge with a funny but time consuming attack.

Intro

We are given a google spreadsheet with basically 0 rights do do anything. But it imports a table from a different spreadsheet and displays the two columns A and B using the formula =QUERY(IMPORTRANGE("1MD4O3pFoQY59_YoW_ZzxRUg-rBgHFlAaYxnNABmqc3A","A:Z"),"SELECT Col1, Col2") After trying a bit of stuff (and not seeing the flag in the network traffic), I noticed probably the only thing we could do in the sheet: Add a temporary filter (data -> filters, then click on the filter icon in cell A1 and filter by condition)

Basic Filter Approach

There are 10 rows with digits 0 to 9 and we can apply filters like Show only rows, where the value equals XXX. For XXX, we can put any formula. Lets say =1+3. Only row 6 with value 4 is shown, because the others do not match the value 4. Without knowing the result of 1+3, the filter shows us the result (4). Right, we could add the two values by ourself, but we can plug in differnt formulas with unknown results and the filter shows us exactly the correct answer (assuming the result is a single digit, but we will improve this later). (If no digits matches, nothing is shown, there it helps to use greater / less filters to debug.

Exploit the Filters

Longer numbers: Lets say, we want to know the value of X(), but it is a number with 2 digits, we need 2 queries:

1. =MOD(X(), 10) MOD ist modulo, so the filter displays the one row, that matches the reminder when dividing X() by 10. So, if X() is 34, we get 4.
2. =DIV(X() - 4, 10) Now, we subtract 4 (or whatever we got from the first query) and divide by 10 to get the second digit ((34 - 4) / 10 = 3). We need the "-4", otherwise 34 / 10 = 3.4 does not match any row.
3. If the number is even longer, e.g. 134, the DIV-query shows 0 results (because the answer is 13), in which case, I used DIV(X() - 104, 10, so (134 - 104) / 10 = 3. And we will never deal with numbers larger than 127 actually. (in this challenge)

Strings : If we have strings, we take the ascii values of the characters and do the above number extracting using =UNICODE(MID (string, charPos, 1)) and do this for every charPos.

Final Exploit strategy

1. We find where stuff is located in the other sheet: =COUNTA(QUERY(IMPORTRANGE("1MD4O3pFoQY59_YoW_ZzxRUg-rBgHFlAaYxnNABmqc3A","A:Z"), "SELECT Col1")) This counts every filled cell in the first column of the hidden sheet. The answer is 10 for Col1 and Col2 (so, filter shows no rows), i think it was 2 for Col3 and 1 for Col15. So, flag is probably in Col15. We found it. But need to extract it.
2. Our basic query is QUERY(IMPORTRANGE("1MD4O3pFoQY59_YoW_ZzxRUg-rBgHFlAaYxnNABmqc3A","A:Z"), "SELECT Col15 WHERE Col15 LIKE '_%' ") . The WHERE selects only those rows with actual content. So only the flag cell.
3. The result to the previous query is a string and we apply our string extracting, and long number extracting to successivly extract the single characters. Takes a long time to do, but gets a flag in the end. On of this queries looks like this: =MOD(UNICODE(MID(QUERY(IMPORTRANGE("1MD4O3pFoQY59_YoW_ZzxRUg-rBgHFlAaYxnNABmqc3A","A:Z"), "SELECT Col15 WHERE Col15 LIKE '_%' "),3,1)),10) Which is part of the extracting of the third character of the flag cell.