CODEWITHSUNDEEP
sqliterestgo
Meto
Meto

Reputation: 658

GO API with SQLITE3 can't DELETE tuple from db

Hello fellow developers. I am trying to learn GO while constructing a simple web API using sqlite3. I got stuck at somepoint where i am unable to delete rows from my table by sending a DELETE request from postman. I am trying to use the code below to delete a row. I have already verified that I have access to db and I can also delete rows by using command tool of sqlite3. I do not understand what is wrong!

func deleteArticle(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Content-Type", "application/json")
    params := mux.Vars(r) // get any params
    db := connectToDB(dbName)
    defer db.Close()

    _, err := db.Query("DELETE FROM article WHERE id=" + params["id"])
    if err != nil {
        fmt.Fprintf(w, "article couldn't be found in db")
    }
}

Here is the navigation part:

myRouter.HandleFunc("/articles/{id}", deleteArticle).Methods("DELETE")

No mather what I do I cannot delete an article from db using postman. Thanks bunches.

postman delete_method

Upvotes: 0

Views: 457

Answers (1)

Meto
Meto

Reputation: 658

Thanks to @mkopriva 's comments I have learned that

1.

It is very important that you do not use Query nor QueryRow for SQL queries that do not return any rows, for these cases use the Exec method. When you use Query you always have to assign the result to a non-blank identifier, i.e. anything but _, and then invoke the Close method on that once you're done with the result. If you do not do that then your application will leak db connections and very soon will start crashing.

2.

when you want to pass user input (including record ids) to your queries you have to utilize, at all times, the parameter-reference syntax supported by the sql dialect and/or dirver you are using, and then pass the input separately. That means that you should never do

Exec("DELETE FROM article WHERE id=" + params["id"]),

instead you should always do

Exec("DELETE FROM article WHERE id= ?",params["id"])

If you do not do it the proper way and instead continue using plain string concatenation your app will be vulnerable to SQL injection attacks.

Regarding this information I have changed my code into:

func deleteArticle(w http.ResponseWriter, r *http.Request) {
    w.Header().Set("Content-Type", "application/json")
    params := mux.Vars(r) // get any params
    db := connectToDB(dbName)
    defer db.Close()
    fmt.Printf("%q\n", params["id"])
    statement, err := db.Prepare("DELETE FROM article WHERE id= ?")
    if err != nil {
        fmt.Fprintf(w, "article couldn't be found in db")
    }
    statement.Exec(params["id"])
}

Which has solved my problem. So thank you @mkopriva

Upvotes: 1

Related Questions