sqli-labs闯关笔记-mysql基础知识
1.0 基础语句
1.1 基础
show databases;
use security;
show tables;
select * from users;
1.2 查库、查表、查字段、查字段的值
1.2.1 查库
select schema_name from information_schema.schemata;
1.2.2 查表
select table_name from information_schema.tables where table_schema='security';
1.2.3 查字段
select column_name from information_schema.columns where table_name='users';
1.2.4 查字段的值
select id,username,password from security.users;
1.3 重要语句
1.3.1 limit语句
用法：【select * from tableName limit i,n 】
参数：
- tableName : 为数据表；
- i : 为查询结果的索引值（默认从0开始）；
- n : 为查询结果返回的数量
1.3.2 order by语句
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 1 表示 所select 的字段按第一个字段排序
ORDER BY ASC应该没有这样写法,ORDER BY 后面不是字段就是数字,
可以ORDER BY 1 ASC 或者ORDER BY COL1 ASC
1.3.3 union select用来合并两个或多个 SELECT 语句的结果集。
2.0 示例
2.1 基础
查看mysql数据库所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| challenges |
| mysql |
| performance_schema |
| security |
+--------------------+
5 rows in set (0.00 sec)
使用security数据库
mysql> use security;
Database changed
查看security数据库中的所有表
mysql> show tables;
+--------------------+
| Tables_in_security |
+--------------------+
| emails |
| referers |
| uagents |
| users |
+--------------------+
4 rows in set (0.00 sec)
查看users所有内容
mysql> select * from users;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 1 | Dumb | Dumb |
| 2 | Angelina | I-kill-you |
| 3 | Dummy | p@ssword |
| 4 | secure | crappy |
| 5 | stupid | stupidity |
| 6 | superman | genious |
| 7 | batman | mob!le |
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 12 | dhakkan | dumbo |
| 14 | admin4 | admin4 |
+----+----------+------------+
13 rows in set (0.00 sec)
2.2 基础用法
2.2.1查库
mysql> select schema_name from information_schema.schemata;
+--------------------+
| schema_name |
+--------------------+
| information_schema |
| challenges |
| mysql |
| performance_schema |
| security |
| sys |
+--------------------+
6 rows in set (0.00 sec)
2.2.2查表
mysql> select table_name from information_schema.tables where table_schema='security';
+------------+
| table_name |
+------------+
| emails |
| referers |
| uagents |
| users |
+------------+
4 rows in set (0.00 sec)
2.2.3查字段
mysql> select column_name from information_schema.columns where table_name='users';
+---------------------+
| column_name |
+---------------------+
| USER |
| CURRENT_CONNECTIONS |
| TOTAL_CONNECTIONS |
| id |
| username |
| password |
+---------------------+
6 rows in set (0.00 sec)
mysql>
2.2.4查字段的值
mysql> select id,username,password from security.users;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 1 | Dumb | Dumb |
| 2 | Angelina | I-kill-you |
| 3 | Dummy | p@ssword |
| 4 | secure | crappy |
| 5 | stupid | stupidity |
| 6 | superman | genious |
| 7 | batman | mob!le |
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 12 | dhakkan | dumbo |
| 14 | admin4 | admin4 |
+----+----------+------------+
13 rows in set (0.00 sec)
3 重要语句
3.1 limit
mysql> select * from users limit 0,1;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | Dumb | Dumb |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> select * from users limit 1,1;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 2 | Angelina | I-kill-you |
+----+----------+------------+
1 row in set (0.00 sec)
3.2 order by语句，可以猜测表有多少字段。超过会报错。
mysql> select * from users order by 99;
ERROR 1054 (42S22): Unknown column '99' in 'order clause'
mysql> select * from users order by 50;
ERROR 1054 (42S22): Unknown column '50' in 'order clause'
mysql> select * from users order by 25;
ERROR 1054 (42S22): Unknown column '25' in 'order clause'
mysql> select * from users order by 13;
ERROR 1054 (42S22): Unknown column '13' in 'order clause'
mysql> select * from users order by 6;
ERROR 1054 (42S22): Unknown column '6' in 'order clause'
mysql> select * from users order by 3;
+----+----------+------------+
| id | username | password |
+----+----------+------------+
| 8 | admin | admin |
| 9 | admin1 | admin1 |
| 10 | admin2 | admin2 |
| 11 | admin3 | admin3 |
| 14 | admin4 | admin4 |
| 4 | secure | crappy |
| 1 | Dumb | Dumb |
| 12 | dhakkan | dumbo |
| 6 | superman | genious |
| 2 | Angelina | I-kill-you |
| 7 | batman | mob!le |
| 3 | Dummy | p@ssword |
| 5 | stupid | stupidity |
+----+----------+------------+
13 rows in set (0.00 sec)
mysql> select * from users order by 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'
mysql>
3.3 union，查看哪些数据回显。
mysql> select * from users where id=1 union select 1,2,3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | Dumb | Dumb |
| 1 | 2 | 3 |
+----+----------+----------+
2 rows in set (0.00 sec)
当id为不存在的值时前面语句不显示。
mysql> select * from users where id=-1 union select 1,2,3;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | 2 | 3 |
+----+----------+----------+
1 row in set (0.00 sec)
参考：
https://www.bilibili.com/video/BV1e441127Rd?p=3
