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).