To wrap up my mini series for Beginner SQL for CTF challenges I will cover the information_schema database and how its of use to the budding CTF challenger.
I installed a mariadb database in a previous post. If you have followed this post and installed the same database then great. If not then no matter, you just need any running mariadb server that you can connect to and follow along.
What is the information_schema database?
What is in the information_schema database is vital for the functionality of the MariaDB database. Its the database that contains the description of all other databases on the server. Also it contains all the tables in those databases and all the rows in those tables. It also has all the information about those columns, data type. Amount of rows, size, datatype, etc.
The tables of the information_schema database
Listing out the tables of the information_schema database produces quit a list. It has over 75 tables. Fortunately we are only interested in two tables.
| TABLES | This table contains all the information pertaining to all the Tables in all Databases |
| COLUMNS | this table contains all the information pertaining to all the Columns in all Tables in all Database. |
TABLES Table
The TABLES Table has a number of rows of interest for us.
| TABLE_SCHEMA | This contains the name of the Databases that the Tables belong to. |
| TABLE_NAME | This contains all the Table names in all the Databases. |
COLUMNS Table
This also has a couple of columns of interest.
| TABLE_NAME | The names of all the Tables in all the Databases. |
| COLUMN_NAME | The names of all the Columns in all the Tables in all the Databases. |
Still with me so far? So what can we do this?
Listing all the databases on the server.
Seeing as the table “TABLE” has the names of all the Databases on the server, a simple command can be run to get all the Database names.
SELECT distinct TABLE_SCHEMA FROM information_schema.TABLES;
Now we have a list of Databases we can get the list of Tables in those Databases. In this case we can focus on the previously setup Database called “employees”. Although you can repeat this with any mariaDB Databases.
SELECT distinct TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'employees';
Now that we have the names of the Database and the Tables from that Database we can grab the Column names from the COLUMNS tables. This time we will focus on just the ’employees’ Table, but the process works the same on the rest of them.
SELECT distinct COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'employees' and TABLE_NAME = 'employees';
How is this any good for CTF challenges?
This whole process is called SQL Enumeration. You get a small foothold with SQL Injection or even blind SQL and you can dig to get every piece of information that you need to enumerate a database. If you take a look over my Blind SQLi pages you can see how all this now makes sense and eventually gives us access to the usernames and passwords in a Database.
Wrap Up
This is the concluding post for my mini Beginners SQL series and I hope you enjoyed it. Any Questions or Comments, then please post them below.
Support
I really enjoy making this content and if you would like to support the cost of keeping this site up and running, please make a purchase through one of my affiliate links.
Be First to Comment