Unable to use prepared statements with go-sql-driver/mysql when connecting to StarRocks

[1246245906]

Issue description

I am trying to connect to StarRocks using Go and the go-sql-driver/mysql library. According to the latest StarRocks documentation, it supports prepared statements. I have successfully used prepared statements with Java's JDBC, but when I attempt to use them with Go, I encounter an issue.

When I try to use prepared statements with the go-sql-driver/mysql library, I receive a "busy buffer" error. However, if I do not use prepared statements, everything works fine.

Example code

func main() {
	dsn := "user:psw@tcp(xxx:xxx)/xxx"

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println("Connected to the database successfully!")

	qsql := "SELECT id, name, age FROM users where id = ?"
	rows, err := db.Query(qsql, 1)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
		var id int
		var name string
		var age int
		err := rows.Scan(&id, &name, &age)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("ID: %d, Name: %s, Age: %d\n", id, name, age)
	}

	if err = rows.Err(); err != nil {
		log.Fatal(err)
	}
}

Error log

[mysql] 2024/11/06 20:23:09 connection.go:49: busy buffer

Configuration

*Driver version (or git SHA):v1.8.1

*Go version: v1.19

[methane]

According to the latest StarRocks documentation, it supports prepared statements

Link please?

I have successfully used prepared statements with Java's JDBC

I never use neither StarRocks nor JDBC. But when I googled, useServerPrepStmts=true is required to use prepared statement. Unless it is used, JDBC interpolate query parameters in client side.
If you want to use "client side prepare", go-sql-driver/mysql provides interpolateParams option.

[1246245906]

Thank you, I can use client-side prepared statements for now, but according to the documentation, client-side prepared statements still have a risk of SQL injection.

StarRocks supports server-side prepared statements, and the documentation link is as follows:
https://docs.mirrorship.cn/docs/sql-reference/sql-statements/prepared_statement

I would like to know why the busy buffer issue occurs when using server-side prepared statements.

[methane]

StarRocks supports server-side prepared statements, and the documentation link is as follows:
https://docs.mirrorship.cn/docs/sql-reference/sql-statements/prepared_statement

It is statement level prepared statement. It is different from protocol level prepared statement.

Thank you, I can use client-side prepared statements for now, but according to the documentation, client-side prepared statements still have a risk of SQL injection.

It is very low risk. And unless you are using useServerPrepStmts=true in JDBC, you are using client side prepared statement already. Do you use it?

I would like to know why the busy buffer issue occurs when using server-side prepared statements.

I don't know. busy buffer happens after some other errors. It is just a result, not a cause.

[methane]

And what version of starrocks do you use?

[1246245906]

StarRocks supports server-side prepared statements, and the documentation link is as follows:
https://docs.mirrorship.cn/docs/sql-reference/sql-statements/prepared_statement

It is statement level prepared statement. It is different from protocol level prepared statement.

Thank you, I can use client-side prepared statements for now, but according to the documentation, client-side prepared statements still have a risk of SQL injection.

It is very low risk. And unless you are using useServerPrepStmts=true in JDBC, you are using client side prepared statement already. Do you use it?

I would like to know why the busy buffer issue occurs when using server-side prepared statements.

I don't know. busy buffer happens after some other errors. It is just a result, not a cause.

ok，thank you for reply.

[1246245906]

And what version of starrocks do you use?
3.3，the latest version of StarRocks.

[methane]

For the record, I confirmed that prepared statement works with this driver and StarRocks 3.3.5. Do not assume what cause your error. Write complete step to reproduce instead.

I can not reproduce your error because your example is no reproducible. (no step to prepare users table).