Search code examples
sqlpostgresqlgopgx

PlaceHolderFormat doesn't replace the dollar sign for the parameter value during SQL using pgx driver for postgres

I am new to Go and am trying to check a password against a username in a postgresql database.

I can't get dollar substitution to occur and would rather not resort to concatenating strings.

I am currently using squirrel but also tried it without and didn't have much luck.

I have the following code:

    package datalayer

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "net/http"

    sq "github.com/Masterminds/squirrel"
    _ "github.com/jackc/pgx/v4/stdlib"
    "golang.org/x/crypto/bcrypt"

    "github.com/gin-gonic/gin"
)

var (
    // for the database
    db *sql.DB
)

func InitDB(sqlDriver string, dataSource string) error {
    var err error

    // Connect to the postgres db  (sqlDriver is literal string "pgx")
    db, err = sql.Open(sqlDriver, dataSource)

    if err != nil {
        panic(err)
    }
    return db.Ping()
}

// Create a struct that models the structure of a user, both in the request body, and in the DB
type Credentials struct {
    Password string `json:"password", db:"password"`
    Username string `json:"username", db:"username"`
}

func Signin(c *gin.Context) {
    // Parse and decode the request body into a new `Credentials` instance
    creds := &Credentials{}
    err := json.NewDecoder(c.Request.Body).Decode(creds)


    if err != nil {
        // If there is something wrong with the request body, return a 400 status
        c.Writer.WriteHeader(http.StatusBadRequest)
        return
    }
    query := sq.
        Select("password").
        From("users").
        Where("username = $1", creds.Username).
        PlaceholderFormat(sq.Dollar)

        // The line below doesn't substitute the $ sign, it shows this:  SELECT password FROM users WHERE username = $1 [rgfdgfd] <nil>
    fmt.Println(sq.
        Select("password").
        From("users").
        Where("username = $1", creds.Username).
        PlaceholderFormat(sq.Dollar).ToSql())

    rows, sqlerr := query.RunWith(db).Query()
    if sqlerr != nil {
        panic(fmt.Sprintf("QueryRow failed: %v", sqlerr))
    }

    if err != nil {
        // If there is an issue with the database, return a 500 error
        c.Writer.WriteHeader(http.StatusInternalServerError)
        return
    }
    // We create another instance of `Credentials` to store the credentials we get from the database
    storedCreds := &Credentials{}
    // Store the obtained password in `storedCreds`
    err = rows.Scan(&storedCreds.Password)
    if err != nil {
        // If an entry with the username does not exist, send an "Unauthorized"(401) status
        if err == sql.ErrNoRows {
            c.Writer.WriteHeader(http.StatusUnauthorized)
            return
        }
        // If the error is of any other type, send a 500 status
        c.Writer.WriteHeader(http.StatusInternalServerError)
        return
    }

    // Compare the stored hashed password, with the hashed version of the password that was received
    if err = bcrypt.CompareHashAndPassword([]byte(storedCreds.Password), []byte(creds.Password)); err != nil {
        // If the two passwords don't match, return a 401 status
        c.Writer.WriteHeader(http.StatusUnauthorized)
    }
    fmt.Printf("We made it !")
    // If we reach this point, that means the users password was correct, and that they are authorized
    // The default 200 status is sent
}

I see the following when I check pgAdmin, which shows the dollar sign not being substituted:

enter image description here

Solution

  • The substitution of the placeholders is done by the postgres server, it SHOULD NOT be the job of the Go code, or squirrel, to do the substitution.

    When you are executing a query that takes parameters, a rough outline of what the database driver has to do is something like the following:

    1. Using the query string, with placeholders untouched, a parse request is sent to the postgres server to create a prepared statement.
    2. Using the parameter values and the identifier of the newly-created statement, a bind request is sent to make the statement ready for execution by creating a portal. A portal (similar to, but not the same as, a cursor) represents a ready-to-execute or already-partially-executed statement, with any missing parameter values filled in.
    3. Using the portal's identifier an execute request is sent to the server which then executes the portal's query.

    Note that the above steps are just a rough outline, in reality there are more request-response cycles involved between the db client and server.

    And as far as pgAdmin is concerned I believe what it is displaying to you is the prepared statement as created by the parse request, although I can't tell for sure as I am not familiar with it.

    In theory, a helper library like squirrel, or a driver library like pgx, could implement the substitution of parameters themselves and then send a simple query to the server. In general, however, given the possibility of SQL injections, it is better to leave it to the authority of the postgres server, in my opinion.

    The PlaceholderFormat's job is to simply translate the placeholder to the specified format. For example you could write your SQL using the MySQL format (?,?,...) and then invoke the PlaceholderFormat(sql.Dollar) method to translate that into the PostgreSQL format ($1,$2,...).