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,useame,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 | useame | 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 |
| useame |
| password |
+---------------------+
6 rows in set (0.00 sec)

mysql>

2.2.4查字段的值

mysql> select id,useame,password from security.users;
+----+----------+------------+
| id | useame | 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 | useame | password |
+----+----------+----------+
| 1 | Dumb | Dumb |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> select * from users limit 1,1;
+----+----------+------------+
| id | useame | 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 | useame | 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 | useame | 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 | useame | password |
+----+----------+----------+
| 1 | 2 | 3 |
+----+----------+----------+
1 row in set (0.00 sec)

参考：

https://www.bilibili.com/video/BV1e441127Rd?p=3