Attack | Tutorials because the current SQL injection is very popular and the technology threshold is low attack means, and very practical, light can get some of the site's accounts, such as to get a movie site of the gold member of the account number, heavy use of its website building more intrusion into the entire server and so on.
This is intended as a topic to explain SQL and its injection. Where the SQL is not clear where you want to look at their own data. This post will be updated for a long time ...
One, SQL overview
The SQL (structured query Language) language is a structured query language. There are 9 keywords in the SQL language that complete the core functions: SELECT (data query), creat, DROP, ALTER (data definition), INSERT, Updata, DELETE (data manipulation), GRANT, REVOKE (Data Control).
1, Data definition section
(1) Create a basic table
creat table Employee (
Eno Char (6) NOT null unique,
Ename char (unique)
Esex Char (2)
eage int
Edept Char (10)
Espe Char (20)
)
The statement creates a data table named employee. There are six columns, character type (length 6, non-empty, unique) employee number ENO, character type (length 20, unique) employee number name ename, character type (length 2) Employee sex, integral type of employee age, character type ( The employee Department of length 10, character type (length 20) employee specialty.
(2) Delete the basic table
DROP TABLE Employee
(3) Change the basic table
ALTER TABLE Employee ADD esalary CHAR (5)
Add a column to the employee table with a character type (length of 5) for the employee's salary.
ALTER TABLE wmployee DROP UNIQUE (ename);
Removes the unique attribute of the employee's name from the employee table.
ALTER TABLE Employee MODIFY esex CHAR (1);
Change the gender column in the employee table to a character type.
2, the data query part
This is the most flexible and powerful part of the SQL statement.
(1) Basic query statement
SELECT Eno,ename,esex from Employee
Query the Eno,ename,esex three columns in the Employee table
SELECT * from Employee
Queries all columns in the Employee table.
SELECT DISTINCT Eno from Employee;
Queries the ENO column in the Employee table and removes duplicate rows.
(2) condition (WHERE) query statement
The Sussu conditions are concatenated as follows: Not,=,>,<,>=,<=,!=,<>,!>,!<,between And,not BETWEEN and (scoping), Like,not like ( Character matching), is null,is NOT null (null value), And,or (multiple conditional connection).
Ⅰ comparison
SELECT Eno from Employee WHERE eage <=25
List employee numbers older than 25 in the table
Ⅱ defined Range
SELECT Eno,ename from Employee
WHERE eage [NOT] BETWEEN and 30
List the employee number and name of age (not) 20 to 30 in the table
Ⅲ determines the collection
SELECT Eno,ename from Employee
WHERE edept [NOT] in (' SD ', ' HD ')
List (not) is the employee number and name of the hardware and software development department
Ⅳ character Matching
The use of like is as follows
[NOT] Like ' < matching mode > ' [ESCAPE ' < code-changing character > ']
The wildcard symbol has both% and _.
%: Matches a string of any length (length can be 0). A%b can be matched with AB,ADFB.
_: Matches a single arbitrary character. A_b can be matched with a#b,a@b.
If there is escape, then follow the symbol after the replacement of% or _ is no longer a wildcard symbol, only normal% or _.
For example:
SELECT * from Employee WHERE ename like ' Liu% '
Find information about employee surnamed Liu in the table
SELECT * from Employee Where ename like ' Liu _ _ '
Find information for employees whose name is Liu (two words) in the table.
SELECT * from Employee WHERE espe like ' db\%t_ ' ESCAPE ' \ '
Find the information for the employee in the table that has a db_ start and the second last character is T.
Ⅴ Null Value SELECT * FROM Employee wheree espe are [not] NULL
Find employee information in the table for which the long term (not) is empty.
Ⅵ Multi-condition connection
SELECT ename from Employee WHERE edept = ' SD ' and Eage <=30;
Lists the names of employees under 30 and 30 years of age in the Software development department in the table.
(3) Order of results
Sort query Results by ORDERBY,ASC (default) ascending, desc in descending order
SELECT * from Employee ORDER by Edept,eage DESC
(4) grouped results
Grouping of query results is typically used in SQL set functions, so set functions are introduced first.
The set functions of the SQL language are mainly count (statistics total), sum (sum), AVG (mean value), Max (max), min (min).
SELECT MAX (eage) from Employee WHERE edept= ' SD '
Lists the names of the oldest employees in the Software development department.
SELECT edept from Employee GROUP by Edept has Count (*) >10
Count the number of employees in each department, showing only those departments where the number of employees is greater than 10.
SELECT Edept,count (Eno) from the Employee GROUP by edept
To count the number of employees in each department, and to list the number of employees by department.
(5) Connection query
A connection query is a situation in which a query involves multiple data tables, from which you join multiple tables.
If we want to count the employee number and name of each project participant, the table Eproject structure is as follows:
Eproject (Eno char (6), Pno char (6), Timebgntime,
Timeend Time,remark CHAR (50))
The corresponding query statement is:
SELECT Eproject.pno,employee.eno,ename
From Employee,eproject
WHERE Employee.eno=eproject.eno
Order BY Eproject.pno;
Lists employee numbers and names for each purpose, sorted by item number in ascending order.
(6) Collection query
A collection query refers to a collection operation between multiple select query results. There are mainly union (and operation), INTERSECT (AC operation), minus (differential operation), in which the standard SQL does not provide the intersection operation and the difference operation, but they may use the union query implementation.
If we want to query hardware Development department employees older than 25 years old, can be combined with the query to achieve the following:
SELECT * FROM employee where edept= ' HD ' UNION SELECT * from employee where eage<=25
3, Data Update section
The data update statements in SQL have insert,update and delete three types, which are used as follows:
(1) Inserting data
INSERT into Employee
ValueS (' 13253 ', ' King II ', ' Male ', ' db_project ', ' SD ', ' the ') '
Insert a complete piece of data into the employee table
INSERT into Employee (eno,ename)
ValueS (' 13253 ', ' King II ');
Inserts a data into the table, containing only the employee number and name, and the other columns as null values.
Note: In the above case, a column with a Non-empty property must not be a null value.
(2) Modify the data
UPDATE Employee SET eage=24 WHERE eno= ' 13253 '
Change the age of employee number 13,253th to 24 years
(3) Delete data
DELETE from Employee WHERE eno= ' 13253 '
4, Data Control section
(1) User authorization
The SQL user authorizes the GRANT keyword, which is used as follows:
GRANT SELECT on TABLE Employee to USR1;
Allow users to USR1 query table employee
GRANT all privileges on TABLE Employee to USR2;
Allow the user to USR2 any action on the table employee
(2) Withdrawal of authority
Reclaim user rights in SQL use revoke keywords
REVOKE UPDATE (Eno) on the TABLE Employee from USR3;]
Reclaim the rights of ENO columns in USR3 Update table employee
REVOKE on TABLE Employee from public
Does not allow all users to add data to table employee.
