Find Column Names for SQL Injection
Extracting column names for a given table
Once the attacker knows table names he needs to find out what the column names are in order to extract information. This article explains how this information can be found using meta data. Examples shown use a UNION attack to simplify things, however the same information could be retrieved with blind SQL injection and inference techniques. In most cases the attacker will filter data returned in order to show the columns of only one table. This is not done in this article to ensure that the queries presented are as simple as possible.
Find Column Names in MySQL
In MySQL, the table information_schema.columns provides information about columns in tables. Below are listed the most useful columns to extract.
- column_name: The name of the column.
- table_name: The name of the table.
- data_type: Specifies the data type (MySQL data type).
- column_default: Default value inserted in the column.
- is_nullable: Indicates whether the column can contain null or not.
Here is an example presenting how the information about columns could be extracted using a UNION attack.
User input.
1 AND 1=2 UNION SELECT table_name, column_name, 1 FROM information_schema.columns
Query generated.
SELECT name, description, price FROM products WHERE category=1 AND 1=2 UNION SELECT table_name, column_name, 1 FROM information_schema.columns
Find Table Names in SQL Server
SQL Server also supports INFORMATION_SCHEMA and it is the the best way to access information related to columns (information_schema.columns). For you information, it could also be done with sys.columns (or syscolumns for SQL Server 2000), but information_schema standardises this data and it should preferably be used. Below are listed the most interesting columns, as you can see those are pretty similar to MySQL’s.
- column_name: The name of the colunmn.
- table_name: The name of the table in which the column can be found.
- data_type: Indicates data type of the column (SQL Server data type).
- column_default: Default value inserted in the column.
- is_nullable: Indicates if the value can be null.
Here is what a basic extraction of column names in SQL Server could look like.
User input.
1 AND 1=2 UNION SELECT table_name, column_name, 1 FROM information_schema.columns
Query generated.
SELECT name, description, price FROM products WHERE category=1 AND 1=2 UNION SELECT table_name, column_name, 1 FROM information_schema.columns
Find Column Names in Oracle
Oracle does not support information_schema, however the same information can be retrieved in the data dictionary. The system table all_tab_columns contains what we are searching for. The most interesting columns of this table are listed below. As you can see the information is pretty similar to the previous DBMS.
- column_name: The name of the column.
- table_name: The name of the table in which the column can be found.
- data_type: Indicates data type of the column (Oracle data type).
- data_default: Default value.
- nullable: Y if the column can contain null values, N if it can't.
Here is the same example, adapted to Oracle DBMS.
User input.
1 AND 1=2 UNION SELECT table_name, column_name, 1 FROM all_tab_columns
Query generated.
SELECT name, description, price FROM products WHERE category=1 AND 1=2 UNION SELECT table_name, column_name, 1 FROM all_tab_columns
For your information, there are alternative tables that will allow you to gather information about columns in Oracle.
- all_col_tables: Returns columns from all tables the current user has access to.
- dba_col_tables: Returns columns from system tables (given you have access granted the SELECT_CATALOG_ROLE role or the SELECT ANY DICTIONARY privilege).
- user_col_tables: Returns columns from the tables that the current user owns only.
