Error when add a query parameter to postgres query

Question

When I write the code:

err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").
    Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)

Everything works fine. But I want it to not just get the page with id=1, but to be dynamic.

So I write:

err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=?", pageID).
    Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)

But I get an error:

GolangdProjects go run test.go  
1  
2017/04/13 11:29:57 Couldn't get page: 1  
exit status 1

The full code:

package main

import (
  "database/sql"
  "fmt"
  _ "github.com/lib/pq"
  "github.com/gorilla/mux"
  "log"
  "net/http"
)

const (
  DBHost = "localhost"
  DBPort = ":5432"
  DBUser = "nirgalon"
  DBPass = ""
  DBDbase = "cms"
  PORT = ":8080"
)

var database *sql.DB

type Page struct {
  Title string
  Content string
  Date string
}

func ServePage(w http.ResponseWriter, r *http.Request) {
  vars := mux.Vars(r)
  pageID := vars["id"]
  thisPage := Page{}
  fmt.Println(pageID)
  err := database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id=1").Scan(&thisPage.Title, &thisPage.Content, &thisPage.Date)
  if err != nil {
    log.Fatal("Couldn't get page: " + pageID)
    log.Fatal(err.Error)
  }
  html := `<html><head><title>` + thisPage.Title + `</title></head><body><h1>` + thisPage.Title + `</h1><div>` + thisPage.Content + `</div></body></html>`
  fmt.Fprintln(w, html)
}

func main() {
  db, err := sql.Open("postgres", "user=nirgalon dbname=cms sslmode=disable")
  if err != nil {
    log.Println("Couldn't connnect to db")
    log.Fatal(err)
  }
  database = db

  routes := mux.NewRouter()
  routes.HandleFunc("/page/{id:[0-9]+}", ServePage)
  http.Handle("/", routes)
  http.ListenAndServe(PORT, nil)
}

Please don't include images of error messages. Instead copy/paste the text of the error message. That makes searching much easier, not to mention reading for the visually impaired. — Jonathan Hall
– Jonathan Hall, Commented Apr 13, 2017 at 15:56

Alex Pliutau · Accepted Answer · 2017-04-13 09:37:02Z

1

psql driver uses $1, $2, etc for params:

database.QueryRow("SELECT page_title,page_content,page_date FROM pages WHERE id = $1", pageID)

answered Apr 13, 2017 at 9:37

Alex Pliutau

22k28 gold badges115 silver badges144 bronze badges

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

Comments

abhink · Accepted Answer · 2017-04-13 08:59:24Z

0

QueryRow takes var args of type interface{}. It could be that pageID is being interpolated as a string (as returned by mux.Vars), resulting in a query that's incorrectly formatted as:

"SELECT page_title,page_content,page_date FROM pages WHERE id='1'"

Try converting pageID to int:

id := vars["id"]
pageID := strconv.Atoi(id)

answered Apr 13, 2017 at 8:59

abhink

9,1961 gold badge41 silver badges52 bronze badges

1 Comment

nirgn Over a year ago

Hi, thanks for the answer. I tried this and get the same error.

Collectives™ on Stack Overflow

Error when add a query parameter to postgres query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related