Capture the Flag is native to the area of cybersecurity. The thrill of writing the correct linux command to retrieve the password in CTF is the most vanilla form.
However, us data engineers have to play it in our everyday vocations.
You know, when one of your colleagues comes and says to you, I know that I have seen the value ‘x’ in the database but I can’t remember where it is.
Do you know? They ask.
It’s a stretch to remember table names and column names let alone a cell of a record.
But the next time this happens to you, you will be prepared.
There is a string in the Sales schema of the Adventure Works Database that is equal to this but we don’t know which table or column '8E01F170-64D3-4B74-82E1-D1691AE9F37C'.
We are going to write a little query that writes a select statement possessing the where clause from every column in every table:
Looks a little something like this:
SELECT CONCAT('SELECT * ,''',TABLE_NAME,' ',COLUMN_NAME,'''as TABLE_NAME_COLUMN_NAME',' FROM Sales.', TABLE_NAME, '' ,' WHERE CAST (',COLUMN_NAME,' AS VARCHAR (1000)',') = ''8E01F170-64D3-4B74-82E1-D1691AE9F37C''')
FROM (
SELECT TABLE_NAME as TABLE_NAME, COLUMN_NAME as COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Sales'
) as X
When executed we get a series of SELECT statements.
Running all of these in series we get:
A lot of empty data sets.
But fear not, scroll down and we see:
The needle in the haystick:
It is in the customer table, it is a row guid.
That saved us a lot of time.
We didn’t use excel.
We didn’t use CTRL F.
Just another example of how useful the internal SQL Server databases are.
With that spare time, you can have a cup of tea and an iced vovo.
But…
Don’t forget to pay the lady.
Related Posts
