Executing different MySQL query using Golang

Question

I'm using Go 1.9 and see that nowhere does it prescribe me to use the following syntax

func main() {
dsn := DB_USER + ":" + DB_PASS + "@" + DB_HOST + "/" + DB_NAME + "?charset=utf8"
db, err := sql.Open("mysql", dsn)
if err != nil {
    log.Fatal(err)
}
defer db.Close()

q := "call regWorker('Thuto', 'Deere4454de', '[email protected]', '8725554675364', '94874256443', @outarg)"

_, err = db.Exec(q)
if err != nil {
    log.Fatal(err)
}'

What I found odd is that it actually registers the worker? My problem is that I need the @outarg value and I'm not 100% sure how to retrieve it. I'd like to rewrite this code to be in line with the go-sql as explained here https://golang.org/pkg/database/sql/#Out

I tried searching and I humbly apologise if this has been explained before.

edit: I'm very new to go

peterm · Accepted Answer · 2018-10-24 03:26:44Z

1

Unfortunately MySQL driver, at least go-sql-driver/mysql, doesn't support Out parameters. But you can still use MySQL session variable to retrieve the value of the out parameter.

You'll need to enable multi-statement support for this to work though

dsn := fmt.Sprintf(
    "%s:%s@%s/%s?charset=utf8&multiStatements=true", // <-- add this
    DB_USER, DB_PASS, DB_HOST, DB_NAME,
)

and then

// initialize @out to ensure we're not getting a previous value
q := "SET @out=NULL"
// execute our SP
q += fmt.Sprintf(
    ";CALL regWorker('%s', '%s', '%s', '%s', '%s', @out)",
    "Thuto", "Deere4454de", "[email protected]", "8725554675364", "94874256443",
)
// return the resultset containing the value of the out parameter
q += ";SELECT COALESCE(@out, 0)"

var out int
db.QueryRow(q).Scan(&out)

In this example I assumed that the out parameter is of type int. Adjust appropriately to your requirements.

Note: that unfortunately you're not going to be able to use a prepared statement in this case; therefore you'll need to take care of proper sanitizing and escaping of parameter values to prevent sql injections.

answered Oct 24, 2018 at 3:26

peterm

93k16 gold badges157 silver badges162 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

grav80 grav80 Over a year ago

Thank you so much! I have the time to play around, is there any other driver I can mess around with to practice the out variable function?

grav80 grav80 Over a year ago

I actually thought about this some more. Instead of relying on the out variable, I've broken the stored procedures down into smaller chunks. Micro procedures with no out variables. I'll let Go check for errors. Would that be a better way of protecting against injection?

Chad Grant Over a year ago

if using placeholders (?,?,?) you'll also need interpolateParams=true

Collectives™ on Stack Overflow

Executing different MySQL query using Golang

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related