main.go

package main

import (
	"fmt"
	"time"

	"github.com/k0kubun/pp"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

type User struct {
	ID   int
	Name string
}

func main() {
	db := connection()
	migration(db)
	insertData(db)

	var users1, users2, users3, users4 []User
	var userInputID string

	// OK: SELECT * FROM `users` WHERE `users`.`id` = '1'
	userInputID = "1"
	if err := db.Debug().Find(&users1, userInputID).Error; err != nil {
		panic(err)
	}
	pp.Println(users1)

	// NG: SELECT * FROM `users` WHERE 1=1
	userInputID = "1=1"
	if err := db.Debug().Find(&users2, userInputID).Error; err != nil {
		panic(err)
	}
	pp.Println(users2)

	// NG: SELECT * FROM `users` WHERE 1=1 ORDER BY `users`.`id` LIMIT 1
	userInputID = "1=1"
	if err := db.Debug().First(&users3, userInputID).Error; err != nil {
		panic(err)
	}
	pp.Println(users3)

	// NG: DELETE FROM `users` WHERE 1=1
	userInputID = "1=1"
	if err := db.Debug().Delete(&users4, userInputID).Error; err != nil {
		panic(err)
	}
	pp.Println(users4)
}

func connection() *gorm.DB {
	connectTemplate := "%s:%s@%s/%s?parseTime=true"
	user := "docker"
	password := "docker"
	protocol := "tcp(127.0.0.1:13306)"
	dbName := "test"
	connectUrl := fmt.Sprintf(connectTemplate, user, password, protocol, dbName)

	db, err := gorm.Open(mysql.Open(connectUrl))
	if err != nil {
		panic(err)
	}
	d, err := db.DB()
	if err != nil {
		panic(err)
	}
	d.SetMaxOpenConns(10)
	d.SetMaxIdleConns(10)
	d.SetConnMaxLifetime(time.Duration(10) * time.Second)
	return db
}

func migration(db *gorm.DB) {
	if err := db.AutoMigrate(User{}); err != nil {
		panic(err)
	}
}

func insertData(db *gorm.DB) {
	tx := db.Begin()
	TestData := []*User{
		{Name: "Tiara"},
		{Name: "Rosetta"},
		{Name: "Lavie"},
		{Name: "Ashley"},
		{Name: "Lynette"},
	}

	for _, data := range TestData {
		if err := tx.Create(data).Error; err != nil {
			panic(err)
		}
	}
	if err := tx.Commit().Error; err != nil {
		panic(err)
	}
}