ExplainSQL using consecutive pairs of escaper in SQL string represents an escaper

Explain your user case and expected results


func TestGORM(t *testing.T) {
    user := User{Name: `"jin'zhu"`} // string values contain single or double quotes

    DB.Create(&user)

    var result User
    if err := DB.First(&result, user.ID).Error; err != nil {
        t.Errorf("Failed, got error: %v", err)
    }
}

func TestExplain(t *testing.T) {
    type args struct {
        prepareSql string
        values     []interface{}
    }
    tests := []struct {
        name    string
        args    args
        wantSQL string
    }{
        {"mysql", args{"SELECT ? AS QUOTES_STR", []interface{}{"'"}}, `SELECT '''' AS QUOTES_STR`},
        {"postgres", args{"SELECT $1 AS QUOTES_STR", []interface{}{"'"}}, `SELECT '''' AS QUOTES_STR`},
        {"sqlserver", args{"SELECT @p1 AS QUOTES_STR", []interface{}{"'"}}, `SELECT '''' AS QUOTES_STR`},
        {"sqlite", args{"SELECT ? AS QUOTES_STR", []interface{}{`"`}}, `SELECT """" AS QUOTES_STR`},
    }
    for _, tt := range tests {
        t.Run(tt.name, func(t *testing.T) {
            if name := DB.Dialector.Name(); name != tt.name {
                t.Logf("%s skip %s...", name, tt.name)
                return
            }
            gotSQL := DB.Dialector.Explain(tt.args.prepareSql, tt.args.values...)
            if reflect.DeepEqual(gotSQL, tt.wantSQL) {
                var result string
                if err := DB.Raw(gotSQL).Row().Scan(&result); err == nil {
                    t.Logf("exec `%s` result = `%s`", gotSQL, result)
                } else {
                    t.Errorf("exec `%s` got error: %v", gotSQL, err)
                }
            } else {
                t.Errorf("Explain gotSQL = %v, want %v", gotSQL, tt.wantSQL)
            }
        })
    }
}

Results

SQLite

```shell testing sqlite... 2023/12/28 01:29:27 testing sqlite3... === RUN TestGORM 2023/12/28 01:29:27 /home/runner/work/playground/playground/main_test.go:21 [5.7[64](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19993993951?pr=677#step:5:65)ms] [rows:1] INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`,`company_id`,`manager_id`,`active`) VALUES ("2023-12-28 01:29:27.08","2023-12-28 01:29:27.08",NULL,"""jin'zhu""",0,NULL,NULL,NULL,false) RETURNING `id` 2023/12/28 01:29:27 /home/runner/work/playground/playground/main_test.go:24 [1.341ms] [rows:1] SELECT * FROM `users` WHERE `users`.`id` = 1 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1 --- PASS: TestGORM (0.01s) === RUN TestExplain === RUN TestExplain/sqlite 2023/12/28 01:29:27 /home/runner/work/playground/playground/main_test.go:53 [0.218ms] [rows:-] SELECT """" AS QUOTES_STR main_test.go:54: exec `SELECT """" AS QUOTES_STR` result = `"` --- PASS: TestExplain (0.00s) ```

MySQL

```shell testing mysql... [20](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015928?pr=677#step:6:21)23/12/28 01:31:17 testing mysql... === RUN TestGORM 2023/12/28 01:31:18 /home/runner/work/playground/playground/main_test.go:[21](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015928?pr=677#step:6:22) [4.632ms] [rows:1] INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`,`name`,`age`,`birthday`,`company_id`,`manager_id`,`active`) VALUES ('20[23](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015928?pr=677#step:6:24)-12-28 01:31:18.095','2023-12-28 01:31:18.095',NULL,'"jin''zhu"',0,NULL,NULL,NULL,false) 2023/12/28 01:31:18 /home/runner/work/playground/playground/main_test.go:[24](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015928?pr=677#step:6:25) [0.882ms] [rows:1] SELECT * FROM `users` WHERE `users`.`id` = 1 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1 --- PASS: TestGORM (0.01s) === RUN TestExplain === RUN TestExplain/mysql 2023/12/28 01:31:18 /home/runner/work/playground/playground/main_test.go:53 [0.[29](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015928?pr=677#step:6:30)0ms] [rows:-] SELECT '''' AS QUOTES_STR main_test.go:54: exec `SELECT '''' AS QUOTES_STR` result = `'` --- PASS: TestExplain (0.00s) ```

PostgreSQL

```shell testing postgres... [20](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015831?pr=677#step:6:21)23/12/28 01:31:19 testing postgres... === RUN TestGORM 2023/12/28 01:31:19 /home/runner/work/playground/playground/main_test.go:[21](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015831?pr=677#step:6:22) [1.664ms] [rows:1] INSERT INTO "users" ("created_at","updated_at","deleted_at","name","age","birthday","company_id","manager_id","active") VALUES ('20[23](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015831?pr=677#step:6:24)-12-28 01:31:19.49','2023-12-28 01:31:19.49',NULL,'"jin''zhu"',0,NULL,NULL,NULL,false) RETURNING "id" 2023/12/28 01:31:19 /home/runner/work/playground/playground/main_test.go:[24](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015831?pr=677#step:6:25) [1.010ms] [rows:1] SELECT * FROM "users" WHERE "users"."id" = 1 AND "users"."deleted_at" IS NULL ORDER BY "users"."id" LIMIT 1 --- PASS: TestGORM (0.00s) === RUN TestExplain === RUN TestExplain/postgres 2023/12/28 01:31:19 /home/runner/work/playground/playground/main_test.go:53 [0.442ms] [rows:-] SELECT '''' AS QUOTES_STR main_test.go:54: exec `SELECT '''' AS QUOTES_STR` result = `'` --- PASS: TestExplain (0.00s) ```

SQL Server

```shell testing sqlserver... [20](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015738?pr=677#step:6:21)23/12/28 01:31:25 testing sqlserver... === RUN TestGORM 2023/12/28 01:31:25 /home/runner/work/playground/playground/main_test.go:[21](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015738?pr=677#step:6:22) [3.183ms] [rows:1] INSERT INTO "users" ("created_at","updated_at","deleted_at","name","age","birthday","company_id","manager_id","active") OUTPUT INSERTED."id" VALUES ('20[23](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015738?pr=677#step:6:24)-12-28 01:31:25.38','2023-12-28 01:31:25.38',NULL,'"jin''zhu"',0,NULL,NULL,NULL,0); 2023/12/28 01:31:25 /home/runner/work/playground/playground/main_test.go:[24](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015738?pr=677#step:6:25) [1.175ms] [rows:1] SELECT * FROM "users" WHERE "users"."id" = 1 AND "users"."deleted_at" IS NULL ORDER BY "users"."id" OFFSET 0 ROW FETCH NEXT 1 ROWS ONLY --- PASS: TestGORM (0.00s) === RUN TestExplain === RUN TestExplain/sqlserver 2023/12/28 01:31:[25](https://github.com/go-gorm/playground/actions/runs/7343430750/job/19994015738?pr=677#step:6:26) /home/runner/work/playground/playground/main_test.go:53 [0.492ms] [rows:-] SELECT '''' AS QUOTES_STR main_test.go:54: exec `SELECT '''' AS QUOTES_STR` result = `'` --- PASS: TestExplain (0.00s) ```

go-gorm / playground

ExplainSQL using consecutive pairs of escaper in SQL string represents an escaper #677

Explain your user case and expected results

Results