Passing Variable to GoLang Query

Question

first off let me say I'm a beginner (started a few days ago) with golang and am in the process of learning how to practically apply the language. My goal is to build a web Rest API that queries a database and provides data back to the user.

I've been able to successfully create a simple API using martini (https://github.com/go-martini/martini) and connect to a MySQL database using https://github.com/go-sql-driver/mysql. My problem at the current moment is how to pass a variable param from the API request into my query. Here is my current code:

package main

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

func main() {

  db, err := sql.Open("mysql",
    "root:root@tcp(localhost:8889)/test")

  m := martini.Classic()

  var str string

  m.Get("/hello/:user", func(params martini.Params) string {

  var userId = params["user"] 

  err = db.QueryRow(
      "select name from users where id = userId").Scan(&str)

  if err != nil && err != sql.ErrNoRows {
    fmt.Println(err)
  }

  return "Hello " + str

  })

  m.Run()


  defer db.Close()  

}

As you can see my goal is to take the input variable (user) and insert that into a variable called userId. Then I'd like to query the database against that variable to fetch the name. Once that all works I'd like to respond back with the name of the user.

Can anyone help? It would be much appreciated as I continue my journey to learn Go!

You might find the following resource to be useful in your SQL + go adventures: go-database-sql.org — Zhube
– Zhube, Commented Mar 18, 2015 at 19:52

Drew LeSueur · Accepted Answer · 2015-03-17 03:51:38Z

20

I haven't used it, but looking at the docs, is this what you are after?

db.QueryRow("SELECT name FROM users WHERE id=?", userId)

I assume it should replace the ? with userId in a sql safe way.

http://golang.org/pkg/database/sql/#DB.Query

answered Mar 17, 2015 at 3:51

Drew LeSueur

20.4k29 gold badges94 silver badges110 bronze badges

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

3 Comments

elithrar Over a year ago

Correct. ? is the parameter format used by mySQL, and $1, $2, etc. is the PostgreSQL format. The OP should NEVER (ever!) use anything but parameterisation else they be subject to SQL injection attacks.

viablepath Over a year ago

Thanks that helped a lot. @elithrar am I doing the correct parameterization in my above example? Ex. var userId = params["user"]

elithrar Over a year ago

The parameterisation I'm talking about is in your SQL query - not your Go code. You should read blog.codinghorror.com/… to get a better understanding of what I mean and why you want to do it.

Skully · Accepted Answer · 2021-11-18 13:17:06Z

1

You can try it this way.

var y string // Variable to store result from query.
err := db.QueryRow("SELECT name from user WHERE id = $1", jobID).Scan(&y)
if err != nil && err != sql.ErrNoRows {
    fmt.Println(err)
}

Documentation reference: https://pkg.go.dev/database/sql#pkg-variables

edited Nov 18, 2021 at 13:17

Skully

3,1743 gold badges29 silver badges43 bronze badges

answered Nov 18, 2021 at 4:32

Yash Chauhan

3761 gold badge5 silver badges19 bronze badges

Collectives™ on Stack Overflow

Passing Variable to GoLang Query

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related