Agenda

• What is SQL

• Build Environment

• Basic Syntax

• What is SQLi

Structured

Query

Language

Database

Tables

Column

• Create

• ​Read

• Update

• Delete

How to use

SQL Engine

Agenda

• What is SQL

• Build Environment

• Basic Syntax

• What is SQLi

bit.ly/sqli_vm

需安裝 Extension Pack

• VM:

  • nisra:nisra2020
• SQL
  • root:root

ssh nisra@localhost

docker ps

mysql -uroot -p -h127.0.0.1

http://localhost

Agenda

• What is SQL

• Build Environment

• Basic Syntax

• What is SQLi

• SHOW DATABASES ;
  • ​列出所有 DB
• CREATE DATABASE <db_name> ;
  • ​新增一個 DB
• USE <db_name> ;
  • ​選擇使用該 DB
• ​DROP DATABASE <db_name> ;
  • ​刪除一個 DB

Database

Lab01

新增一個叫做 "test" 的 DB

並且使用它

• SHOW TABLES;
  • ​列出所有 Table
• ​DROP TABLE <tb_name> ;
  • ​​刪除一個 Table

Table

CREATE TABLE <tb_name> (

        <col_name1> type,

        <col_name2> type,

        <col_name3> type,

        ...

)

Table

• INT

• TINYINT

• SMALLINT

• BIGINT

• FLOAT

• DOUBLE

Type

• CHAR(n)

• VARCHAR(n)

• DATE

• TIME

• DATETIME

• TIMESTAMP

Type

Type

• DATE

  • '1970-01-01'

• TIME

  • '00:00:00'

• DATETIME

  • '1970-01-01 00:00:00'

• TIMESTAMP

  • 1597065553

Lab02

新增一張 table 叫做 people
且有下列 column

Column

• SHOW COLUMNS FROM <tb_name>;
  • ​列出該表中的欄位​​​

Select

切換到 DB

employees

• 指定欄位​
  SELECT <col>[, <col> ...]
  FROM <tb>
  
  

• 全選

  ​SELECT * FROM <tb>

• 庫名、表名、欄位名可加重音符 (`)

• 字串會加上單引號 ( ' )

Lab03

將 employees 這個 table 印出來看看

where

查詢的條件 ( filter 的概念 )

SELECT *

FROM <tb>

WHERE <condition>

where

Ex. 在 1960 後出生的人

SELECT *

FROM employees

WHERE birth_date >= date('1960-01-01')

Lab04

將男性員工列出來

Lab04

將男性員工列出來

SELECT * FROM employees

WHERE gender='M'

Lab05

在職 30 年以上的員工

Lab05

在職 30 年以上的員工

SELECT * FROM employees

WHERE hire_date <= DATE('1990-01-01')

where

多個條件

SELECT *

FROM <tb>

WHERE <condition> OR/AND
<condition>

Lab06

1960 後出生的男員工

Lab06

1960 後出生的男員工

SELECT * FROM employees

WHERE birth_date >= DATE('1960-01-01')

AND gender = 'M'

ORDER BY

依照 XX 排序

SELECT *

FROM <tb>

ORDER BY <col>

ORDER BY

依照生日排序

SELECT *

FROM <tb>

ORDER BY birth_date

ORDER BY

• 正序 (default)

  • ASC

• 反序

  • DESC

ORDER BY

依照生日排序

SELECT *

FROM <tb>

ORDER BY birth_date

DESC

LIMIT

限制輸出筆數

SELECT *

FROM <tb>

LIMIT from, n

LIMIT

從第 6 筆開始輸出 3 個

SELECT *

FROM employees

LIMIT 5, 3

LIMIT

從第 6 筆開始輸出 3 個

UNION

將多個 select 連在一起

SELECT * FROM <tb>

UNION

SELECT * FROM <tb>

UNION

SELECT 1, 2

SELECT 3, 4

UNION

UNION

SELECT 1, 2

SELECT 3, 4

UNION

Insert

• 指定欄位​

  INSERT INTO <tb>(<col>)
  VALUES (<val>)
   

• 全部欄位都要給值

  INSERT INTO <tb>
  VALUES (<val>, ...)

test

people

Lab07

把自己的資料寫進去 id 是 1

Lab07

把自己的資料寫進去 id 是 1

INSERT INTO people

VALUES (1, 'chess', DATE('1970-01-01'))

Lab07-2

再新增幾筆資料進去 id 不要重複

Delete

DELETE FROM <tb>

WHERE <condition>

不加 where 會將整個 table 內容清空 !!!!!!!!!!!!!!!!!!!!!!!!

Lab08

把 id 1 刪掉吧 只有 ID 1 喔

Lab08

把 id 1 刪掉吧

DELETE FROM people

WHERE id=1

Update

UPDATE <tb>

SET <col>=<val>, ...

WHERE <condition>

不加 where 會將 table 中該欄位直接毀滅 !!!!!!!!!!!!!!!!!!

Lab09

把 某個特定 id 的名字改成 QQ,

生日改成 2020/01/01

Lab09

把 某個特定 id 的名字改成 QQ,

生日改成 2020/01/01

UPDATE people

SET name='QQ', birth=DATE('2020-01-01')

WHERE id=2

Agenda

• What is SQL

• Build Environment

• Basic Syntax

• What is SQLi

SQLi in PHP

INSERT INTO `Students`(name)

VALUES('XXX')

Robert'); DROP TABLE Students;-- 

INSERT INTO `Students`(name)

VALUES('Robert'); DROP TABLE Students;-- ')

INSERT INTO `Students`(name)

VALUES('Robert'); DROP TABLE Students;-- ')

• SQLi

  • 有回顯

• Blind SQLi

  • 沒有回顯

• 通用

  • Stacked Queries

• SQLi

  • Union Based

  • Error Based

• Blind SQLi

  • Boolean Based

  • Time Based

' or 1=1#--

user=?

pass=?

SELECT *
FROM users 
WHERE user='$user' AND pass='$pass'

user=xxx

pass=bbb

SELECT *
FROM users 
WHERE user='xxx' AND pass='bbb'

user=xxx' or 1=1#--

pass=

SELECT *
FROM users 
WHERE user='xxx' or 1=1#--' AND pass=''

SQLi 01

http://lab.nisra.net:8101

SQLi 02

hint : 多行註解

http://lab.nisra.net:8102

Union Based

information_schema

• SCHEMATA

• TABLES

• COLUMNS

• SCHEMATA

儲存所有 DB 名稱的 table

• TABLES

儲存所有 table 的資訊

• COLUMNS

儲存所有 column 的資訊

• SCHEMATA

  • schema_name

• TABLES

  • table_schema

  • table_name

• COLUMNS

  • table_schema

  • table_name

  • column_name

UNION

SELECT 1, 2

SELECT 3, 4

UNION

SELECT * FROM employees WHERE <condition>

UNION

SELECT 1, 2, 3, 4, ...

SELECT * FROM employees WHERE <condition = false>

UNION

SELECT 1, 2, 3, 4, ...

SQLi 03

hint : GROUP_CONCAT()

http://lab.nisra.net:8103