Skip to content

SQL information_schema Database

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.

TABLESThis table contains all the information pertaining to all the Tables in all Databases
COLUMNSthis table contains all the information pertaining to all the Columns in all Tables in all Database.
Tables of Interest


The TABLES Table has a number of rows of interest for us.

TABLE_SCHEMAThis contains the name of the Databases that the Tables belong to.
TABLE_NAMEThis contains all the Table names in all the Databases.
Interesting Columns


This also has a couple of columns of interest.

TABLE_NAMEThe names of all the Tables in all the Databases.
COLUMN_NAMEThe names of all the Columns in all the Tables in all the Databases.
Interesting Columns

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;
All the databases in Informaiton_schema

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';
All the tables in a Database

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';
Look at all those column names

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.


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.

Published inCTFGetting Started With CTF ChallengesSQL

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *