bug when interpolating params for multiStatements

[0xor1]

Issue description

When executing multiple statements with param interpolation mysql server complains about syntax of ? characters, it appears that when a query contains multiple statements that parameters are not injected beyond the first statement. This issue originates from this SO question

Example code

package main

import(
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "fmt"
)

const(
    sqlDriver = "mysql"
    sqlConnectionString = "usr:pwd@tcp(localhost:3306)/my_test_db?parseTime=true&loc=UTC&multiStatements=true"
)

func main(){
    db, _ := sql.Open(sqlDriver, sqlConnectionString)
    rows, err := db.Query(`SELECT "hello";`)
    if err != nil {
        fmt.Printf("single statements don't work with hard coded params, error: %v", err)
    }
    if rows != nil {
        rows.Close()
    }
    rows, err = db.Query(`SELECT "hello"; SELECT "world";`);
    if err != nil {
        fmt.Printf("multiple statements don't work with hard coded params, error: %v", err)
    }
    if rows != nil {
        rows.Close()
    }
    rows, err = db.Query(`SELECT ?;`, "hello")
    if err != nil {
        fmt.Printf("single statements don't work with interpolated params, error: %v", err)
    }
    if rows != nil {
        rows.Close()
    }
    rows, err = db.Query(`SELECT ?; SELECT ?;`, "hello", "world")
    if err != nil {
        fmt.Printf("multiple statements don't work with interpolated params, error: %v", err)
    }
    if rows != nil {
        rows.Close()
    }
}

produces output:

multiple statements don't work with interpolated params, error: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ?' at line 1

Configuration

Driver version (or git SHA): 267b128
Go version: 1.5.3
Server version: MySQL 5.7.10
Server OS: Windows 8.1

[arnehormann]

Thanks for the report @robsix, apparently we opened a can of worms with multistatements. And apparently you really have a knack for finding those worms and rubbing our faces in them 😀 .

The driver sends all parameters associated with the first query.

We can't change that without error prone query parsing: we'd have to find all ?s (and doing so, ignore any ? in a string or wherever else a ? can occur and not be a parameter). We'd also have to detect query boundaries (; outside of a string - but the delimiter could have been changed and we'd have to detect that, too).

So no, working as intended / impossible to fix. Unless you use interpolateParams.

[0xor1]

thanks, I wasn't aware of interpolateParams that should suit my needs, and sorry for being so annoying

[julienschmidt]

I wasn't aware of this problem until now. If we can't do anything about it, we should at least document this unexpected behavior properly.

[arnehormann]

@robsix not annoying at all, we need that input - thanks!
@julienschmidt yep, you're right.

[arnehormann]

Addendum for @robsix: interpolateParams is incompatible with prepared statements. My earlier comment was probably misleading.

[0xor1]

thanks, I'll leave it with my custom strings.Replace(query, "?", "%q") to db.Exec(fmt.Sprintf(query, args...)) solution, it works and I dont need to worry about sql injection the args are all fully generated on the server side. Thanks again.