Go SQL Statement Quote problem

When using go for MySQL development, you will encounter quotation marks (in fact, language-independent, as long as the use of SQL will encounter these similar problems).

This article provides an example of how to resolve these issues.

Example1

The first example shows whether the format character is to be quoted.

The code is as follows:

detailMsg := "abc"sql := fmt.Sprintf(   "insert into tbl_log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, %s)",    "1.1.0.1",     "test.py",     detailMsg,)fmt.Println("sql: ", sql)

Output

2018/04/05 17:40:00 Sql:insert into Tbl_log (IP, name, detail_msg) VALUES (' 1.1.0.1 ', ' test.py ', ABC)
2018/04/05 17:40:00 exec failed:error 1054:unknown column ' abc ' in ' Field List ', Sql:insert into Tbl_log (IP, name, det AIL_MSG) VALUES (' 1.1.0.1 ', ' test.py ', ABC)

If the field type is a string, the formatted character needs to be prefixed with two single quotation marks, which means that the format character%s is changed to '%s '.

Correct example

detailMsg := "abc"sql := fmt.Sprintf(         "insert into tbl _log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, ‘%s‘)",          "1.1.0.1",          "test.py",          detailMsg,        )fmt.Println("sql: ", sql)

Output

Sql:insert into Tbl_log (IP, name, detail_msg) VALUES (' 1.1.0.1 ', ' test.py ', ' abc ')

Example2

If the inserted content contains single quotes, the example is as follows.

That is, if there is a single quotation mark in Detailmsg.

detailMsg := "ab‘c"fmt.Println("detailMsg: ", detailMsg)sql := fmt.Sprintf(         "insert into tbl_log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, ‘%s‘)",          "1.1.0.1",          "test.py",          detailMsg,        )fmt.Println("sql: ", sql)

Output

2018/04/05 17:45:03 Detailmsg:ab ' C
2018/04/05 17:45:03 Sql:insert into tbl _log (IP, name, detail_msg) VALUES (' 1.1.0.1 ', ' test.py ', ' ab ' C ')
2018/04/05 17:45:03 exec failed:error 1064:you has an Error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use near ' C ') ' at line 1, sql:ins ert into Tbl_log (IP, name, detail_msg) VALUES (' 1.1.0.1 ', ' test.py ', ' ab ' C ')

Solutions

Escapes the single quotation mark in the content.

detailMsg:= "ab‘c"detailMsg = strings.Replace(detailMsg, "‘", "\\‘", -1)log.Println("detailMsg:", detailMsg)sql := fmt.Sprintf(  "insert into tbl_log(ip, name, detail_msg) values (‘%s‘, ‘%s‘, ‘%s‘)",   "1.1.0.1",   "test.py",   detailMsg,)log.Println("sql:", sql)

Output

2018/04/05 17:48:11 Detailmsg:ab ' C
2018/04/05 17:48:11 Sql:insert into Tbl_log (IP, name, detail_msg) VALUES (' 1.1.0.1 ', ' test.py ', ' ab ' C ')

Go SQL Statement Quote problem