Prepare protocol is broken on non-utf8 charset since #28925

[xhebox]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

	connStr := fmt.Sprintf("%s:%s@tcp(%s:4000)/test?charset=gbk", user, pwd, dsn)
	db, err := sql.Open("mysql", connStr)
	if err != nil {
		log.Fatalf("SQLConnect failed: %v", err)
	}
	defer db.Close()
	gbkStr := []byte{0xB2, 0xE2, 0xCA, 0xD4} // GBK encoded "测试"
	execWithPrepare(db, fmt.Sprintf("INSERT INTO tsecurity (security_id, security_code, mkt_id, security_name) VALUES (1, '1', 1 ,'%s')", gbkStr))
}

2. What did you expect to see? (Required)

succeed

3. What did you see instead (Required)

Incorrect string value...

4. What is your TiDB version? (Required)

[0xPoe]

Full code:

package main

import (
	"bytes"
	"database/sql"
	"fmt"
	"io/ioutil"
	"log"
	"os"

	_ "github.com/go-sql-driver/mysql"
	"golang.org/x/text/encoding/simplifiedchinese"
	"golang.org/x/text/transform"
)

func execWithPrepare(db *sql.DB, query string) {
	// Prepare statement
	stmt, err := db.Prepare(query)
	if err != nil {
		log.Fatalf("SQLPrepare failed: %v", err)
	}
	defer stmt.Close()

	// Execute statement
	_, err = stmt.Exec()
	if err != nil {
		log.Fatalf("SQLExecute failed: %v", err)
	}

	// Commit transaction
	_, err = db.Exec("COMMIT")
	if err != nil {
		log.Fatalf("SQLTransact failed: %v", err)
	}

	fmt.Println("SQLExecute succeeded")
}

func execWithoutPrepare(db *sql.DB, query string) {
	// Execute statement
	_, err := db.Exec(query)
	if err != nil {
		log.Fatalf("SQLExecute failed: %v", err)
	}

	// Commit transaction
	_, err = db.Exec("COMMIT")
	if err != nil {
		log.Fatalf("SQLTransact failed: %v", err)
	}

	fmt.Println("SQLExecute succeeded")
}

func execWithPrepareAndParam(db *sql.DB, query string) {
	// Prepare statement
	stmt, err := db.Prepare(query)
	if err != nil {
		log.Fatalf("SQLPrepare failed: %v", err)
	}
	defer stmt.Close()

	// Execute statement
	gbkStr := []byte{0xB2, 0xE2, 0xCA, 0xD4} // GBK encoded "测试"
	_, err = stmt.Exec(1, "1", 1, fmt.Sprintf("%s", gbkStr))
	if err != nil {
		log.Fatalf("SQLExecute failed: %v", err)
	}

	// Commit transaction
	_, err = db.Exec("COMMIT")
	if err != nil {
		log.Fatalf("SQLTransact failed: %v", err)
	}

	fmt.Println("SQLExecute succeeded")
}

func GbkToUtf8(gbkData []byte) string {
	reader := transform.NewReader(bytes.NewReader(gbkData), simplifiedchinese.GBK.NewDecoder())
	utf8Data, err := ioutil.ReadAll(reader)
	if err != nil {
		panic(err)
	}
	return string(utf8Data)
}

func main() {
	if len(os.Args) < 4 {
		log.Fatalf("Usage: %s <DSN> <User> <Password>", os.Args[0])
	}

	dsn := os.Args[1]
	user := os.Args[2]
	pwd := os.Args[3]

	// Connect to the database
	connStr := fmt.Sprintf("%s:%s@tcp(%s:4001)/test?charset=gbk", user, pwd, dsn)
	db, err := sql.Open("mysql", connStr)
	if err != nil {
		log.Fatalf("SQLConnect failed: %v", err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Fatalf("SQLConnect failed: %v", err)
	}

	fmt.Println("connect success")
	gbkStr := []byte{0xB2, 0xE2, 0xCA, 0xD4} // GBK encoded "测试"
	fmt.Println("GBK encoded string:", GbkToUtf8(gbkStr))
	fmt.Printf("GBK encoded string: %s\n", gbkStr)

	// Execute SQL statement
	// execWithoutPrepare(db, fmt.Sprintf("INSERT INTO tsecurity (security_id, security_code, mkt_id, security_name) VALUES (1, '1', 1 ,'%s')", gbkStr))
	// execWithPrepare(db, fmt.Sprintf("INSERT INTO tsecurity (security_id, security_code, mkt_id, security_name) VALUES (1, '1', 1 ,'%s')", gbkStr))
	// execWithPrepareAndParam(db, "INSERT INTO tsecurity (security_id, security_code, mkt_id, security_name) VALUES (?, ?, ?, ?)")
}

execWithoutPrepare and execWithPrepareAndParam will succeed and execWithPrepare will fail.

[0xPoe]

For execWithPrepare we set the charset here:

tidb/pkg/executor/prepared.go

Line 90 in 2e31170

charset, collation := vars.GetCharsetInfo()

and check the gbk data with utf8 encoding here:

tidb/pkg/executor/insert_common.go

Line 403 in 2e31170

val1, err := table.CastValue(sctx, val, e.insertColumns[i].ToInfo(), false, false)