`InterpolateForDialect("SHOW CREATE TABLE ?;"...)` produces invalid MySQL statement

[PavelSafronov]

Repro code:

package main

import (
	"fmt"
	"github.com/gocraft/dbr/v2"
	"github.com/gocraft/dbr/v2/dialect"
)

func main() {
	q, _ := dbr.InterpolateForDialect("SHOW CREATE TABLE ?;", []interface{}{"foo"}, dialect.MySQL)
	fmt.Println(q)
}

Sandbox link showing this execution: https://go.dev/play/p/QGDpepYMCfM

The above code produces the following MySQL statement: SHOW CREATE TABLE 'foo';.
This MySQL statement is not valid, because MySQL is not expecting ' (quotes) around the table name. MySQL docs in question.

Is there a way to invoke InterpolateForDialect in such a way that we can get the injection protection, but avoid quoting a particular parameter? Should we be using a different method entirely?

[taylorchu]

you should use dbr.I (identifier) instead of string

[PavelSafronov]

Can you give an example of that?

I tried this, but in the generated query, foo was still quoted.

dbr.InterpolateForDialect("SHOW CREATE TABLE ?;", []interface{}{dbr.I("foo")}, dialect.MySQL)

[taylorchu]

Is there a way to invoke InterpolateForDialect in such a way that we can get the injection protection, but avoid quoting a particular parameter? Should we be using a different method entirely?

package main

import (
	"fmt"

	"github.com/gocraft/dbr/dialect"
	"github.com/gocraft/dbr/v2"
)

func main() {
	fmt.Println(dbr.InterpolateForDialect("SHOW CREATE TABLE ?;", []interface{}{dbr.I("foo")}, dialect.MySQL))
}

SHOW CREATE TABLE foo

dbr.I will now quote with tick.

[PavelSafronov]

Ah, thank you so much, I didn't notice that using dbr.I produces ticks instead of single quotes. The returned query is now perfectly valid.

Can this approach be documented somewhere? This information is necessary for using the function, but isn't very discoverable.

[taylorchu]

yes.

func ExampleI() {
        // I, identifier, can be used to quote.
        I("suggestions.id").As("id") // `suggestions`.`id`
}

it is there. this issue is helpful for future :)

dbr.I is just a proxy to quoteident, which is used in almost every places.

[PavelSafronov]

Thanks for that pointer.

I'm looking in the examples, but not finding this other case: is there a way to invoke InterpolateForDialect so the values aren't quoted/ticked at all? (I can also make a new issue to ask this question, if you'd prefer that.)

[taylorchu]

dbr.Expr generates non-quoted texts