常見的 Structured Query Language 指令有 Create, Drop, Insert 等，而這些指令又可以細分為幾個種類：
這個種類的指令大多用來建立、變更、刪除資料庫物件的結構或 schema，例如：
一言以蔽之，用來查詢資料庫的資料，例如 SELECT。
操作和修改資料庫中的資料，常見的有：
與權限、授權、許可相關的指令，例如：
與控制、管理事務(Transaction)相關，例如：
PicoCTF 2023 / Web Exploitation / XXE
The web project was rushed and no security assessment was done. Can you read the /etc/passwd file?
<?xml version="1.0" encoding="UTF-8"?><data><ID>1</ID></data>
Body
PicoCTF 2023 / Web Exploitation
BookShelf Pico, my premium online book-reading service.
I believe that my website is super secure. I challenge you to prove me wrong by reading the ‘Flag’ book!
Here are the credentials to get you started:
Username: “user”
Password: “user”
/base/books/pdf/5，會被告知無權限
/base/books/pdf/5，成功了！將 flag pdf 儲存下來即可
我的 mysql 版本： mysql Ver 8.0.32 for macos13 on x86_64 (MySQL Community Server - GPL) 在還記得原本密碼的情況下改密碼：
mysql -u root -p 輸入原本的密碼 ALTER USER 'root'@'localhost' IDENTIFIED BY '新密碼';
在學習如何在 Go 操作資料庫，用此篇文章記錄一下。
mkdir example
cd example
go mod init example
mysql -u root -p
create database recordings;
use recordings;
關於 use，MySQL 手冊上的介紹：The USE statement tells MySQL to use the named database as the default (current) database for subsequent statements.
create-tables.sql，貼上下面程式碼：
DROP TABLE IF EXISTS album;
CREATE TABLE album (
id INT AUTO_INCREMENT NOT NULL,
title VARCHAR(128) NOT NULL,
artist VARCHAR(255) NOT NULL,
price DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO album
(title, artist, price)
VALUES
('Blue Train', 'John Coltrane', 56.99),
('Giant Steps', 'John Coltrane', 63.99),
('Jeru', 'Gerry Mulligan', 17.99),
('Sarah Vaughan', 'Sarah Vaughan', 34.98);
source /path/to/create-tables.sql
select * from album;
應該會出現：
+----+---------------+----------------+-------+
| id | title | artist | price |
+----+---------------+----------------+-------+
| 1 | Blue Train | John Coltrane | 56.99 |
| 2 | Giant Steps | John Coltrane | 63.99 |
| 3 | Jeru | Gerry Mulligan | 17.99 |
| 4 | Sarah Vaughan | Sarah Vaughan | 34.98 |
+----+---------------+----------------+-------+
4 rows in set (0.00 sec)
github.com/go-sql-driver/mysql package main
import (
"database/sql"
"fmt"
"log"
"os"
"github.com/go-sql-driver/mysql"
)
var db *sql.DB
func main() {
// Capture connection properties.
cfg := mysql.Config{
User: os.Getenv("DBUSER"),
Passwd: os.Getenv("DBPASS"),
Net: "tcp",
Addr: "127.0.0.1:3306",
DBName: "recordings",
}
// Get a database handle.
var err error
db, err = sql.Open("mysql", cfg.FormatDSN())
if err != nil {
log.Fatal(err)
}
pingErr := db.Ping() // 確認是否真的連接上資料庫
if pingErr != nil {
log.Fatal(pingErr)
}
fmt.Println("Connected!")
}
example 資料夾，執行下列指令：
go get .
export DBUSER=你的 MySQL 用戶名
export DBPASS=你的 MySQL password
go run .
type Album struct {
ID int64
Title string
Artist string
Price float32
}
albumsByArtist 的 function，這個 function 會 query 傳入的作者所有的專輯，並透過 for loop 與 rows.Next() 進行迭代，把 query 到的內容存入 albums 這個 Album struct 陣列中。
// albumsByArtist queries for albums that have the specified artist name.
func albumsByArtist(name string) ([]Album, error) {
// An albums slice to hold data from returned rows.
var albums []Album
rows, err := db.Query("SELECT * FROM album WHERE artist = ?", name)
if err != nil {
return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
}
defer rows.Close()
// Loop through rows, using Scan to assign column data to struct fields.
for rows.Next() {
var alb Album
if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
}
albums = append(albums, alb)
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
}
return albums, nil
}
albumsByArtist function，索引 John Coltrane 的所有 album ：
albums, err := albumsByArtist("John Coltrane")
if err != nil {
log.Fatal(err)
}
fmt.Printf("Albums found: %v\n", albums)
go run .
剛剛是透過指令 Query 出多個 row，因為一個 artist 可能會創作多張專輯。那如果是只會回傳一個 row 的情況呢？可以使用另一個叫做 QueryRow 的的 method.
// albumByID queries for the album with the specified ID.
func albumByID(id int64) (Album, error) {
// An album to hold data from the returned row.
var alb Album
row := db.QueryRow("SELECT * FROM album WHERE id = ?", id)
if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
if err == sql.ErrNoRows {
return alb, fmt.Errorf("albumsById %d: no such album", id)
}
return alb, fmt.Errorf("albumsById %d: %v", id, err)
}
return alb, nil
}
albumByID，索引 ID 是 2 的專輯：
alb, err := albumByID(2)
if err != nil {
log.Fatal(err)
}
fmt.Printf("Album found: %v\n", alb)
go run .
前面使用了 Query 和 QueryRow，接下來是不會回傳資料的操作，使用 Exec
result.LastInsertId() 來 retrieve 剛剛插入的 row 的 ID：
// addAlbum adds the specified album to the database,
// returning the album ID of the new entry
func addAlbum(alb Album) (int64, error) {
result, err := db.Exec("INSERT INTO album (title, artist, price) VALUES (?, ?, ?)", alb.Title, alb.Artist, alb.Price)
if err != nil {
return 0, fmt.Errorf("addAlbum: %v", err)
}
id, err := result.LastInsertId()
if err != nil {
return 0, fmt.Errorf("addAlbum: %v", err)
}
return id, nil
}
albID, err := addAlbum(Album{
Title: "The Modern Sound of Betty Carter",
Artist: "Betty Carter",
Price: 49.99,
})
if err != nil {
log.Fatal(err)
}
fmt.Printf("ID of added album: %v\n", albID)
go run .