How to escape back ticks

Question

MySQL requires tables that shadow reserved words to be back ticked. I have a table Role which is a reserved word, but I have already put my query in back ticks so I can write it over multiple lines (this is a toy query, large ones will not fit on one line).

How do I escape the back ticks?

Here is my code:

dbmap := db.InitDb()

var roles []entities.Role
query :=
    ` << Difficult to see with SO's code editor widget, but here is a back tick
SELECT *
FROM `Role` <<< Needs escaping
`  << Difficult to see, but here is a back tick

_, err := dbmap.Select(&roles, query, nil)
if err != nil {
    panic(err)
}

fmt.Println(roles)

score 107 · Accepted Answer · answered Jan 18 '14 at 01:47

107

You cannot escape backticks inside backticks, but you can do:

dbmap := db.InitDb()

var roles []entities.Role
query := `
SELECT *
FROM ` + "`Role`"

_, err := dbmap.Select(&roles, query, nil)
if err != nil {
    panic(err)
}

fmt.Println(roles)

answered Jan 18 '14 at 01:47

Agis

32,639
3
73
81

score 6 · Answer 2 · answered Jul 20 '17 at 11:55

6

You can use . prefix:

query := `
SELECT *
FROM .Role
`

answered Jul 20 '17 at 11:55

Pawel Kowalak

367
2
7

score 5 · Answer 3 · answered Jan 18 '14 at 01:49

5

If your query is long, it might be worth putting in a text file and reading it in, that will keep your code more concise and organized, and also avoid the backtick quoting issue entirely.

answered Jan 18 '14 at 01:49

tlehman

5,125
2
33
51

I was thinking of doing exactly that when the going gets heavy, I can then use a tool like Navicat and get syntax highlighting and autocomplete etc. – Lee Jan 18 '14 at 01:51
Excellent idea, you can also name the file in a self-explanatory way. – tlehman Jan 18 '14 at 01:52
But be careful when you read the file as byte-array, it might then contain the byte-order mark. – Stefan Steiger Nov 03 '15 at 07:31
1

I'd also consider that this makes your binary less portable as you have to remember to bring the sql file along as well. – donatJ Feb 16 '18 at 16:56
This is an entirely different problem, but I embed the SQL files in the file using github.com/jteeuwen/go-bindata, so distribution is easier – gimix May 14 '19 at 10:00
You can now use the [//go:embed directive](https://pkg.go.dev/embed) – Kevin Mar 24 '22 at 22:35
@donatJ you could `embed` it – Max Coplan May 27 '22 at 17:47
Do you know the current directory? Doubt it. Do you know the full runtime path at compile time? Doubt it. If you think you know the current directory, you might be wrong. Some foolish code (that might not even exist yet) might set it in another thread. Paths are hell. – doug65536 Aug 23 '22 at 12:46

NotX · Answer 4 · 2021-03-04T13:54:27.747

5

I just used a placeholder (like the unicode ” or basically anything which won't appear anywhere else in the query) instead of the backticks and replaced them afterwards:

strings.ReplaceAll(`CREATE TABLE ”mydatabase”.”mytable” (
    ”id” binary(16),
    ”sname” varchar(45),
    PRIMARY KEY(”id”)
)`, "”", "`")

(Thanks to Andrey Tarantsov for proposing ” as a placeholder.)

edited Mar 04 '21 at 13:54

answered Jan 24 '20 at 16:06

NotX

1,516
1
14
28

A good choice, but oh my gosh, why %s?! :) You can even use some Unicode quotes like ”. – Andrey Tarantsov Mar 04 '21 at 13:31
@AndreyTarantsov Good point. I guess, I was kinda used to `%s` as placeholder. But thinking about it, it's a bit hard to read and might bring some confusion with `fmt` verbs. I'll adjust the response according to your advice! :) – NotX Mar 04 '21 at 13:51
This works but terrible code style imo... – Dion Aug 04 '22 at 16:59
@Dion That's true, and I wouldn't recommend to use it extensively. But in situations where you have a lenghty query and don't want to read it from a file (for whatever reasons), imo this solution maintains the most readibility compared to the other suggestions here. I didn't intend to advocate this style - if you can keep `.sql` files separately or you don't depend on readability, there's no reason to do it that way. – NotX Aug 05 '22 at 10:49
Unfortunately there is no decent solution for that in Go. I just went to only using the sql backticks when really necessary (that basically only is the case when a column name matches an sql keyword). With that it's possible to use the go backticks for multiline queries and in case a backtick is necessary you can use the regular string concat like ``` `CREATE TABLE mydatabase.mytable(` + "`count` int (11)," + ` ... rest of the query` ``` – Dion Aug 06 '22 at 11:23
"Unfortunately there is no decent solution for that in Go" is a proper roundup for many Go patterns anyway imo. ;( I think you're approach is fine, too, but I'ld personally rather not rely on the fact that in 99.9% of all cases schema and table names don't need to be escaped, just for the one 0.01 % biting back when you expect it the least. – NotX Aug 07 '22 at 10:21

score 4 · Answer 5 · answered May 15 '19 at 08:25

4

Use notepad++ on your plain text and replace (search and) replace

with

`+"`"+`

answered May 15 '19 at 08:25

Timothy

75
1
1

score 2 · Answer 6 · answered May 17 '17 at 07:12

2

You can try writing queries like this:

query :=fmt.Sprintf("SELECT * FROM `Role`")

You can compare the outputs:

import "fmt"

func main() {
 query :=fmt.Sprintf("SELECT * FROM `Role`")
 fmt.Println(query)
 fmt.Println( `SELECT * FROM ` + "`Role`") }

answered May 17 '17 at 07:12

nilay jha

744
8
12

1

That's not multiline – Dion Aug 06 '22 at 11:25

Mesh · Answer 7 · 2022-03-14T12:45:25.333

If you are using Go Templates you can pass the backtick in as a parameter:

package main

import (
    "fmt"
    "text/template"
    "bytes"
)

func main() {
    template,_ := template.New( "greeting").Parse(`Hello {{ .BT }}{{ .FirstName }}{{ .BT }}`)
    data := struct {
        FirstName string
        BT        string
    }{
        FirstName:"bob",
        BT:"`", // <---- Here!
    }
    var buf bytes.Buffer
    _ = template.Execute(&buf, data)
    fmt.Print(buf.String())
}

gives:

 Hello `bob`

How to escape back ticks

7 Answers7

Linked

Related