Information in SQL injection_ The role of schema

Information in SQL injection_ The role of schema

This experiment explains through information_schema is a database to obtain information such as database name, table name and field name in the database.

Introduction to experiment

Experiment series: WEB Security Application

Subjects: WEB Security enthusiasts

Related courses and majors: Penetration Testing Technology

Experimental hours: 1 hour

Experiment category: practical experiment

Preparatory knowledge

MySQL syntax:

MySQL query data:

Experimental purpose

Through this experiment, master the process of SQL injection through information_schema is the principle of database explosion database name, table name and field name.

Experimental environment

windows 7. Install wamp environment.

Experiment contents and steps

In MySQL database injection, if you have carefully read the SQL injection statement, you may find that when obtaining the database name, table name and field, the information in the injection statement_ Schema database appears frequently, so have you ever thought about why you need to use this database? What is this database? What's in it?

​ information_ The schema database comes with MySQL. There is no such database below MySQL 5. It provides a way to access database metadata. What is metadata? Metadata is data about data, such as database name or table name, column data type, or access rights. That is, information_ The schema holds information about all other databases maintained by the MySQL server. Such as database name, database table, data type and access right of table column, etc. In information_ In schema, there are several read-only tables.

In phpmyadmin, click information on the left_ Schema database.

After expansion, as shown in the figure below, all tables in the database are displayed. Because there are too many tables, only a part is cut. You can pull the scroll bar on the right to view all tables.

You can also execute the following SQL statement to view all tables in the Library:

​ show tables;

It should be noted that in information_schema executes the SQL statement in this database. How to enter information_ To execute SQL statements in the schema database, please refer to the previous steps to execute SQL statements in the sqli database.

The tables shown above are actually views rather than basic tables, so you won't see the entity files of the database in the data storage directory of the database. The data of the database is saved in the C:\wamp\bin\mysql\mysql5.6.17\data directory. There are four directories in this directory:

To view the data saving directory of the database, execute select @@datadir, as shown in the following figure:

Each directory corresponds to a database in the database. Execute show databases in the database; You can see that there are five databases, which is the lack of information_schema this database.

In SQL injection, we focus on the following tables, because they are mainly used to obtain data:

SCHEMATA: provides information about all databases in the current mysql database, including schema_ The name field holds all database names. The results of show databases are taken from this table.

TABLES: provides information about TABLES in the database. It describes in detail the schema, table type, table engine, creation time and other information of a table, including table_ The name field holds all column name information, and the results of show TABLES from schema are taken from this table.

COLUMNS: provides column information in the table. Describes in detail all COLUMNS of a table and the information of each column, where column_name saved all the field information. The result of show COLUMNS from schema.tablename is taken from this table.

To better illustrate the role of these tables, we enter the mysql terminal.

Click the wampserver icon in the lower right corner. If there is no such icon, you can double-click the wampserver on the desktop to run.

Then click MySQL in the pop-up list and select MySQL console.

A command line window will pop up, which is the mysql client. At this time, you are required to enter the password. Because the root password is empty, just press enter.

Enter information_schema database. The command is: use information_schema; . Be sure to remember to add a semicolon later. A semicolon indicates the end of a statement. If you do not detect that you enter a semicolon, it will think that you have not finished a statement until you encounter a semicolon.

First, execute show databases; View all databases before executing the select schema_name from schemata;.

You can see that their functions are the same. They list all databases, just like what we said earlier. Schema_ The name field holds all database names.

Therefore, in injection, we can inject select schema_name from schemata to query all database names in the current database. If you check some injection statements that explode database names, you will find that they contain the following sentence: select schema_name from information_schema.schemata limit 0,1 is based on querying information_ Schema in schema.schemata_ Name, where limit 0 and 1 are used to obtain the first record. By incrementing the first parameter, you can obtain one record at a time, that is, one database name at a time until an error occurs, indicating that there are no more errors.

Usually, after obtaining the database name, you will select the database of interest, and then obtain the data. First, you need to obtain all the table names in the database of interest and query information_ The TABLES table in the schema library can get the table name.

In the TABLES table, it saves all table names in all databases and the library to which the table belongs, which means that no matter which database you are in, there will be a record corresponding here. If you create a table in a database, there will also be a record corresponding to the table you create in this table.

Desc can be used to see the table structure. Look at the table structure of tables and execute desc tables;, The results are as follows:

Note the two records marked in the figure above. In each record, they record a table name and a database name to which the table belongs. Where TABLE_NAME saves the table name, while TABLE_SCHEMA saves the database where the table name is located. We can query a record to see how many records there are before querying. To avoid inconvenient viewing too many records, execute select count(*) from tables; The results are as follows:

Description the number of tables in all databases is 142. Query any record to view. I select the last record here. The SQL statement is: select * from tables limit 141,1\G. since the default query result is unfriendly in the client, you can change the semicolon after the statement to \ g. it will make one record display one line, which doesn't look so messy\ G is only supported in the client. In other software connecting to the database, using \ g will report an error.

As you can see, the table of the last record_ Name is user, TABLE_SCHEMA is sqli. View the tables in the sqli database. The SQL statement is: show tables from sqli; You can see that the user table does exist.

Since information_ Table in TABLES table of schema_ The schemta field is the name of the saved database, and TABLE_NAME saves the table name, so we can use table_ The schemta field is used as the query criteria to query TABLE_NAME, you can know all table names in all specified databases. For example, we want to use information_schema database to query all TABLES in sqli database, you can use the following SQL statements:

select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'sqli';

If the current library is information_schema, you can omit it. Otherwise, you need to bring the database name when querying across databases. The results are as follows:

By modifying TABLE_SCHEMA limits that you can query all table names in any database. This is the principle of exploding table names through injection on the Internet.

Knowing the table name, how to get the fields in the table? You should know that if we do not have a table name, we will query all the data. If the injection does not echo and the union query cannot be performed, it is undoubtedly very inefficient to obtain our target data.

Fortunately, in information_ There is also a table in the schema database, which saves all the column names in the whole data. This table is COLUMNS. Also, check the table structure first.

There are three fields related to injection, namely TABLE_SCHEMA,TABLE_NAME and COLUMN_NAME, it's not hard to guess. If you query a record in this table, TABLE_SCHEMA saves the database name of the field saved by this record, while TABLE_NAME saves the table name to which the field belongs, COLUMN_NAME is a column name record. Query a record to verify it. First determine how many records there are in the table, and execute select count(*) from columns;, It is known that there are 1662 records in total, and the results are as follows:

We get the last record and execute select * from columns limit 1661,1\G

Including COLUMNS_NAME is ip, TABLE_NAME is user, TABLE_SCHEMA is sqli, which indicates that there is an ip column in the user table in sqli data, that is, the ip field we often talk about.

Check whether the field exists in the user table of sqli, and execute the SQL statement: show columns from sqli.user;

You can see that the field does exist.

Since it is in columns, TABLE_NAME saves the table name to which the field belongs, TABLE_SCHEMA saves the database name to which the field belongs. Just like obtaining the table name through the TABLES table, we can query the TABLE_NAME and TABLE_SCHEMA is used as the query criteria to query the eligible columns_ Name, that is, query the fields in a table in the specified database.

For example, we need to pass information_ The columns table of the schema database queries all the fields in the user table of the sqli database. You can execute the following SQL statements:

select column_name from information_schema.columns where TABLE_SCHEMA='sqli' and TABLE_NAME='user';

Query results and show columns from sqli.user; agreement.

After knowing the database name, table name and field, if there is echo and joint query is supported, you can directly obtain the data by injecting a joint query statement behind the injection point. If there is no echo, you may need to obtain the data through blind annotation. You can refer to the MySQL blind annotation experiment.


Analysis and thinking

Why does the database name use the hexadecimal value of characters in online SQL injection statements?
It is often used to intercept a character of the string during blind annotation to judge what it is. For example, substr(database(),1,1) is used to obtain the first character of the database for judgment, combined with the length function, and finally spliced into the whole database name.


#Or -- spaces are single line comments

/**/Is an inline comment

SQL sentence
 Query statement
SELECT〈Target rank〉
     FROM〈data source〉
     [WHERE〈Tuple selection condition〉]
     [GROUP BY〈Disaggregated group〉[HAVING 〈Group selection criteria〉]]
     [ORDER BY〈Sort column 1〉〈Sort requirement 1〉 [,...n]];

 be used for union Injection attack

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];


expression1, expression2, ... expression_n: expression, sql Injection is usually database()Functions and other information we need.
tables: The data table to retrieve.
WHERE conditions: Optional, search criteria.
DISTINCT: Optional, delete duplicate data in the result set. By default UNION The operator has deleted duplicate data, so DISTINCT Modifiers have no effect on the results.
ALL: Optional, return all result sets, including duplicate data.

limit m,n

from m Position start, take n Records

order by You can add column name or number after it. The number should be less than or equal to the number of columns in the query result, so it can be increased slowly. When an error occurs, the query result is the number when the error occurs-1. 

mysql 5.0 Then there is the database, which contains a lot of information about the database and common tables SCHEMATA,TABLES,COLUMNS. 

SCHEMATA The table stores the database name, and the field is SCHEMA_NAME

SCHEMATA surface
sql sentence

SELECT schema_name FROM information_schema.schemata

Query results
TABLES The table stores the table name and the database to which the table belongs. The fields are TABLE_NAME,TABLE_SCHEMA

TABLES surface
 It is generally used to query the current database or specify one from the database obtained above.

Query current database sql sentence

SELECT table_name FROM information_schema.`TABLES` WHERE table_schema=database()

Table of the current database
 Specify database sql Statement to pikachu take as an example

SELECT table_name FROM information_schema.`TABLES` WHERE table_schema='pikachu'

COLUMNS Table storage column name, database name and table name. The fields are COLUMN_NAME,TABLE_SCHEMA,TABLE_NAME

COLUMNS surface
 Query the columns of the current table, sql sentence

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name=table_name

 Query the columns of the specified table. We already know from the above pikachu Database has tables users,Take this as an example.

sql sentence

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema='pikachu' AND table_name='users'

 field value

By using this database, we already know all the databases, tables and columns, and then we can get the desired data. To database pikachu of users Tabular username and password For example.

sql sentence

SELECT username,password FROM pikachu.users

 Of course, practical sql Injection is not so simple. This article lists all the knowledge points. The next article explains several injection technologies with a local target, and then analyzes them dvwa The filtering code and bypass can almost solve the medium and low-level problems after learning sql Inject problems, and then you can go to some CTF Online range to further improve.

Common functions
 On top Sql In the statement, we mentioned databse(),This is mysql Next, let's look at the function in sql Functions commonly used in injection.

Basic function
1.Returns the user currently using the database
2. Returns the version of the current database
3. Returns the currently used database
4. Returns the location of the current database
5. Returns the current operating system version
6.Concatenate multiple strings into one string

7.Concatenate multiple lines of strings into one line
group_concat()The syntax is as follows:


[DISTINCT] Fields to connect

[Order BY sort field ASC/DESC]

[Separator 'Separator']


It is mainly used to process one to many query results, which are usually combined GROUP BY Use together. 

Blind annotation (string handler)
length(s) : Returns the length of the target string
 It is often used to judge the database and other information during blind injection, sql sentence

select length(database());


substr(),substring(),mid():The usage is basically the same. Just intercept a part of the string and one

SUBSTR(s, start, length)

SUBSTRING(s, start, length)

MID(s, start, length)

From string s of start(Include and>=1)Position start interception length Characters.

It is often used to intercept a character of a string during blind annotation to judge what it is, such as using substr(database(),1,1)Obtain the first character of the database for judgment, and then combine length Function, and finally spliced into the whole database name.

ascii(s)/ord(s): Return string s Corresponding to the first character of ASCII Just remember one code

Commonly used for blind injection, combined with substr function

sql sentence

SELECT ascii(substr(database(),1,1))


Re query ascii The code table knows that 105 corresponds to characters i. 

char(num): return ASCII Character corresponding to code

Inverse function of the above function
sql sentence

SELECT char(97)
97 The corresponding character is a

hex(): Replace the target string with data in hexadecimal format
select hex("dvwa")
Return result: 70696 B61636875
unhex(): Replace the data in hexadecimal format with the original string
 sentence: unhex(64767761)
Explanation: return result: dvwa

left()/right() : From the left of the specified string/Start intercepting the string on the right
LEFT(s,n) ,RIGHT(s,n)
From string s Left/Start intercepting on the right n Bit string

Error injection
UPDATEXML function

UPDATEXML (XML_document, XPath_string, new_value); 
First parameter: XML_document yes String Format, as XML The name of the document object is usually written as 1
 Second parameter: XPath_string (Xpath Formatted string) ,You don't have to understand Xpath Grammar, we usually write sql Statement query table name, etc. are displayed through error information.
Third parameter: new_value,String Format, replace the found qualified data, and generally write 1
 Function: change the value of qualified nodes in the document

also floor,extractvalue Other functions can also be used for error injection

Time blind injection

function N Seconds.


If expression expr Set up, return v1,Otherwise return v2. And sleep Function to determine the result by how long the web page will return.

sql sentence

SELECT ascii(substr(database(),1,1)) and if(length(database())>17,sleep(2),1)
 After 2 seconds, return 0

2 Return in seconds
SELECT ascii(substr(database(),1,1)) and if(length(database())>18,sleep(2),1)

Return now
 Database length>17 But no>18,That is, the database length is 18.

Tags: Database SQL Web Security

Posted by TEENFRONT on Sat, 23 Oct 2021 13:32:57 +0530