DEV Community

Cover image for Preventing SQL Injection with Golang
Wiliam V. Joaquim
Wiliam V. Joaquim

Posted on

Preventing SQL Injection with Golang

#go #sql #security #tutorial

What will we cover?

SQL injection is one of the most used techniques to carry out attacks on your system, where we can execute malicious SQL on vulnerable endpoints, making it possible to manipulate your database, although there are already many ways to mitigate this attack, it is still possible to leave this gap if the developer don't pay attention.

Most ORM's already inhibit this type of attack, but in Go it is very common not to use ORM, the chances of this vulnerability occurring are greater if it is not handled correctly.

How SQL Injection works

SQL injection generally happens in routes that provided filters that allow parameters to be passed and these parameters are not treated correctly, for example:

GET http://localhost:8080/users?id=1 HTTP/1.1
Enter fullscreen mode Exit fullscreen mode

This endpoint searches for a user by id, but without correct treatment, an SQL injection can be performed.

GET http://localhost:8080/users?id='1'OR'1'='1' HTTP/1.1
Enter fullscreen mode Exit fullscreen mode

Adding the sql '1'OR'1'='1' which ignores any condition, '1'='1 returns a true boolean, this way the query always returns all users in our database, this vulnerability is serious, we can even delete the entire database.

Creating a seed

To help with testing, we will create a seed (populate records in the table), to facilitate our tests, inside the database folder we will create a seed.go file:

  func SeedUsers() error {
    // drop table users
    _, err := DBConnection.Exec(`DROP TABLE IF EXISTS users`)
    if err != nil {
      log.Fatal(err)
    }

    // create table users
    createTableQuery := `
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(256) NOT NULL,
        email VARCHAR(256) NOT NULL UNIQUE,
        password VARCHAR(256) NOT NULL
      )
    `
    _, err = DBConnection.Exec(createTableQuery)
    if err != nil {
      log.Fatal(err)
    }
    log.Println("Tabela de usuários criada com sucesso.")

    insertUserQuery := `
      INSERT INTO users (name, email, password) VALUES
      ('John Doe', 'john.doe@example.com', 123456),
      ('Bob', 'bob@example.com', 123456),
      ('Charlie', 'charlie@example.com', 123456),
      ('Slash', 'slash@example.com', 098765),
      ('Gilmour', 'gilmour@example.com', 1255657),
      ('Steve Vai', 'steve_vai@example.com', 1255657)
    `

    _, err = DBConnection.Exec(insertUserQuery)
    if err != nil {
      log.Fatal(err)
    }
    log.Println("Usuários inseridos com sucesso.")
    return nil
  }
Enter fullscreen mode Exit fullscreen mode

SeedUsers deletes the table, if it exists, then creates the table again and finally adds the users to our table. You can add more user if you need.

Project structure

To illustrate, let's create some endpoints with this vulnerability and fix these vulnerabilities, but first let's structure our project, I won't go too deep into the structure, I'll leave the repository link here.

Project structure

This will be the structure of our project, we will use PostgreSQL as the database, go chi to create our endpoints, go dot env to import our environment variables.

We separate main.go to start our server, connection to the bank and our endpoints:

  func main() {
    err := database.NewDBConnection()
    if err != nil {
      panic(err)
    }

    service.SeedUsers("test")
    r := chi.NewRouter()
    r.Get("/users", handler.GetUsersInjectHandler)
    r.Get("/users/correct", handler.GetUsersCorrectHandler)
    r.Delete("/users", handler.DeleteUserInjectHandler)
    r.Delete("/users/correct", handler.DeleteUserCorrectHandler)

    server := &http.Server{
      Addr:    ":8080",
      Handler: r,
    }
    server.ListenAndServe()
  }
Enter fullscreen mode Exit fullscreen mode

connection.go will create our connection with PostgreSQL and make the connection available globally in our application:

  var DBConnection *sql.DB

  func NewDBConnection() error {
    err := godotenv.Load(".env")
    if err != nil {
      return errors.New("error loading .env file")
    }

    databaseURL := os.Getenv("DATABASE_URL")
    db, err := sql.Open("postgres", databaseURL)
    if err != nil {
      return err
    }
    DBConnection = db

    return nil
  }
Enter fullscreen mode Exit fullscreen mode

user_handler.go will be responsible for handling our request and calling the user_service.go service.

Creating the endpoints

Let's create endpoints with vulnerabilities and one without the vulnerability.

Searching for users

Let's create an endpoint that searches for the user by id, let's call it GetUsersInjectHandler, this endpoint will have our vulnerability.

user_handler.go:

  func GetUsersInjectHandler(w http.ResponseWriter, r *http.Request) {
    if r.Method != http.MethodGet {
      http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
      return
    }
    id := r.URL.Query().Get("id")
    if id == "" {
      http.Error(w, "id not provided", http.StatusBadRequest)
      return
    }

    users, err := service.GetUserInject(id)
    if err != nil {
      fmt.Println(err)
      http.Error(w, "Error when searching for users", http.StatusInternalServerError)
      return
    }

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusOK)
    if err := json.NewEncoder(w).Encode(users); err != nil {
      http.Error(w, "Error encoding users to JSON", http.StatusInternalServerError)
      return
    }
  }
Enter fullscreen mode Exit fullscreen mode

user_service.go:

  func GetUserInject(id string) ([]User, error) {
    query := fmt.Sprintf("SELECT id, name, email FROM users WHERE id = %s", id)
    rows, err := database.DBConnection.Query(query)
    if err != nil {
      return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
      var user User
      if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
        return nil, err
      }
      users = append(users, user)
    }

    return users, nil
  }
Enter fullscreen mode Exit fullscreen mode

In the service we execute our query, passing the id directly "SELECT id, name, email FROM users WHERE id = %s", id, here is the vulnerability we are passing the id directly to the query, without treating it first.

Let's make a request using the vscode HTTP Client extension:

GET http://localhost:8080/users?id=1 HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

We received the expected return:

[
  {
    "id": 1,
    "name": "John Doe",
    "email": "john.doe@example.com"
  }
]
Enter fullscreen mode Exit fullscreen mode

Now, let's inject the sql:

GET http://localhost:8080/users?id='1'OR'1'='1' HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

Received all users:

[
  {
    "id": 1,
    "name": "John Doe",
    "email": "john.doe@example.com"
  },
  {
    "id": 2,
    "name": "Bob",
    "email": "bob@example.com"
  },
  {
    "id": 3,
    "name": "Charlie",
    "email": "charlie@example.com"
  },
  {
    "id": 4,
    "name": "Slash",
    "email": "slash@example.com"
  },
  {
    "id": 5,
    "name": "Gilmour",
    "email": "gilmour@example.com"
  },
  {
    "id": 6,
    "name": "Steve Vai",
    "email": "steve_vai@example.com"
  }
]
Enter fullscreen mode Exit fullscreen mode

As the injected sql '1'='1' will always be true, the query will always return all records.

Deleting users

The above sql is a serious flaw, but it could get worse, if the same vulnerability is in a query that deletes records, let's see an example:

user_handler.go:

  func DeleteUserInjectHandler(w http.ResponseWriter, r *http.Request) {
    if r.Method != http.MethodDelete {
      http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
      return
    }
    id := r.URL.Query().Get("id")
    if id == "" {
      http.Error(w, "id not provided", http.StatusBadRequest)
      return
    }

    err := service.DeleteUserInject(id)
    if err != nil {
      fmt.Println(err)
      http.Error(w, "Error when searching for users", http.StatusInternalServerError)
      return
    }

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusOK)
    if err := json.NewEncoder(w).Encode("Usuário deletado com sucesso"); err != nil {
      http.Error(w, "Error encoding users to JSON", http.StatusInternalServerError)
      return
    }
  }
Enter fullscreen mode Exit fullscreen mode

user_service.go:

  func DeleteUserInject(id string) error {
    query := fmt.Sprintf("DELETE FROM users WHERE id = %s", id)
    _, err := database.DBConnection.Exec(query)
    if err != nil {
      return err
    }

    return nil
  }
Enter fullscreen mode Exit fullscreen mode

We use the same logic to delete user by id:

DELETE http://localhost:8080/users?id=1 HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

When calling this endpoint, the user with id = 1 is deleted, now let's inject the sql:

DELETE http://localhost:8080/users?id='1'OR'1'='1' HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

When calling the endpoint with the injected sql, notice that all records in the user table were deleted, can you imagine the damage this can do to a production database?

This can be done in any query, we could have an endpoint that updates the user's password by id and with sql injection update the password of all users, the possibilities are enormous!

Fixing the vulnerability

There are already countless ways to mitigate this attack, we could deal with it in the handler and check if there is sql in the id value for example, but the most effective and correct way is to use the database driver's existing resources, in this case the package "database/sql" already has this feature and with few modifications we can avoid the sql injection attack.

Searching for users

Let's have the same logic:

user_handler.go

  func GetUsersCorrectHandler(w http.ResponseWriter, r *http.Request) {
    if r.Method != http.MethodGet {
      http.Error(w, "method not allowed", http.StatusMethodNotAllowed)
      return
    }
    id := r.URL.Query().Get("id")
    if id == "" {
      http.Error(w, "id not provided", http.StatusBadRequest)
      return
    }

    users, err := service.GetUserCorrect(id)
    if err != nil {
      fmt.Println(err)
      http.Error(w, "Error when searching for users", http.StatusInternalServerError)
      return
    }

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusOK)
    if err := json.NewEncoder(w).Encode(users); err != nil {
      http.Error(w, "Error encoding users to JSON", http.StatusInternalServerError)
      return
    }
  }
Enter fullscreen mode Exit fullscreen mode

user_service.go:

  func GetUserCorrect(id string) ([]User, error) {
    query := "SELECT id, name, email FROM users WHERE id = $1"
    rows, err := database.DBConnection.Query(query, id)
    if err != nil {
      return nil, err
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
      var user User
      if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
        return nil, err
      }
      users = append(users, user)
    }

    return users, nil
  }
Enter fullscreen mode Exit fullscreen mode

However, now we don't pass the id directly, we pass the marker $1 which indicates that we will have a parameter in that position and we pass it to the query Query(query, id), this way the driver already does what we call " prepared statement" or "sanitization", "hydration", call it whatever you think is best, this approach helps separate query logic from data input, improving the security and integrity of database operations.

Let's call the endpoint and try to inject the sql:

GET http://localhost:8080/users/correct?id='1'OR'1'='1' HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

We get an error and avoid sql injection!

HTTP/1.1 500 Internal Server Error
Content-Type: text/plain; charset=utf-8
X-Content-Type-Options: nosniff
Date: Wed, 27 Dec 2023 15:53:27 GMT
Content-Length: 28
Connection: close

Error when searching for users
Enter fullscreen mode Exit fullscreen mode

Searching correctly:

GET http://localhost:8080/users/correct?id=2 HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

We now receive the user data correctly.

[
  {
    "id": 2,
    "name": "Bob",
    "email": "bob@example.com"
  }
]
Enter fullscreen mode Exit fullscreen mode

Deleting users

Let's use the same solution used to search for the user.

user_handler.go

  func DeleteUserCorrectHandler(w http.ResponseWriter, r *http.Request) {
    if r.Method != http.MethodDelete {
      http.Error(w, "meyhod not allowed", http.StatusMethodNotAllowed)
      return
    }
    id := r.URL.Query().Get("id")
    if id == "" {
      http.Error(w, "id not provided", http.StatusBadRequest)
      return
    }

    err := service.DeleteUserCorrect(id)
    if err != nil {
      fmt.Println(err)
      http.Error(w, "Error when searching for users", http.StatusInternalServerError)
      return
    }

    w.Header().Set("Content-Type", "application/json")
    w.WriteHeader(http.StatusOK)
    if err := json.NewEncoder(w).Encode("User deleted successfully"); err != nil {
      http.Error(w, "Error encoding users to JSON", http.StatusInternalServerError)
      return
    }
  }
Enter fullscreen mode Exit fullscreen mode

user_service.go:

func DeleteUserCorrect(id string) error {
  query := "DELETE FROM users WHERE id = $1"
  _, err := database.DBConnection.Exec(query, id)
  if err != nil {
    return err
  }

  return nil
}
Enter fullscreen mode Exit fullscreen mode

Let's call the endpoint and try to inject the sql:

DELETE http://localhost:8080/users/correct?id='1'OR'1'='1' HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

We get an error and avoid sql injection!

HTTP/1.1 500 Internal Server Error
Content-Type: text/plain; charset=utf-8
X-Content-Type-Options: nosniff
Date: Wed, 27 Dec 2023 15:58:52 GMT
Content-Length: 28
Connection: close

Error when deleting users
Enter fullscreen mode Exit fullscreen mode

Deleting correctly:

GET http://localhost:8080/users/correct?id=1 HTTP/1.1
content-type: application/json
Enter fullscreen mode Exit fullscreen mode

We only delete the user with the id we want.

"User deleted successfully"
Enter fullscreen mode Exit fullscreen mode

Final considerations

In this post we saw how to simulate sql injection and how to avoid sql injection, there are several ways to mitigate this attack, but using the resources provided by the database driver is generally the simplest and safest, however validating this before calling your driver causes that this vulnerability is practically nil. The use of ORM's also greatly reduces the chances of sql injection happening, this problem is already handled natively in the ORM, but it can still happen.

Repository link

repository of the project

see the post on my blog here

Gopher credits

Top comments (7)

Collapse
 
aminmansuri profile image
hidden_dude • • Edited

I don't think this is a good solution to the injection problem. Why?

Because the key to avoiding injection is to: check your inputs

For example, if your id is a number, you can cast it to a number to ensure it is of the right type. If its a UUID, you can check that it has the correct format and only uses acceptable characters.

What you did was to delegate the critical security check to the Query() method. Now I'm not a Go expert, but there are two ways a Query() method would help with injection:

  1. Parametrization: it does the type checking and/or escaping and concatenates it to the SQL

  2. Prepared Statements: it depends on a SQL driver to precompile the statement, and then send over the parameters to the compiled statement.

However, that doesn't change the fact that you sent a tainted string to the next level of software, it also shows that you made a lot of assumptions about how the driver works and delegated critical security to the Query() method or to the driver. Note that drivers don't necessarily all do what you want for all datbases.

While this is a good, and probably essential way to have a second line of defense, the PRIMARY line of defense is to CHECK YOUR INPUTS.

For example do this (not Go syntax):

int id_as_number = int(id_str)

Or make it match a regex of the form NNN-NNN-NNN in the case of a UUID.

ALWAYS CHECK INPUTS THAT COME FROM THE USER. Do it early do it hard.

Note: if you don't check early, it can be used to do other forms of injection like Javascript injection, script injection, trigger overflows, and other bad behaviors long before it gets to your Query.

I think your solution is maybe better than nothing, but it violates a canonical principle of software security.

Collapse
 
madeindra profile image
Made Indra • • Edited

The original post is a great start because it's a common mistake for beginners to append a query with value instead of using a parameterized query.

For further work, I agree with this comment, input should be sanitized and validated before it's passed to the database.

Collapse
 
aminmansuri profile image
hidden_dude • • Edited

I know I'm being a bit strict here, and that your code may possibly work (for now). But I don't think this delegation is a good practice. So I think its better we discuss this here to reflect one what our defensive attitude should be with regards to software security.

Collapse
 
priteshusadadiya profile image
Pritesh Usadadiya •

[[..Pingback..]]
This article was curated as a part of #126th Issue of Software Testing Notes Newsletter.

Web: softwaretestingnotes.com

Collapse
 
kehoecj profile image
Clayton Kehoe •

Great guide - very detailed. Thanks for sharing!

Collapse
 
pixi3mama profile image
Abdiel Jayne •

I am impressed with the eyes and curiosity of a child. Thank you for sharing this guide.

Collapse
 
jhonnyfall profile image
Jhonny Fall •

Great!!

Read next

ajmal_hasan profile image

Securing Apps In React Native

Ajmal Hasan -

iamkumar profile image

Flutter App Multiple Audio/Video Playback Issue

Manish Kumar -

pragativerma18 profile image

The Complete Guide to Logging in Django

Pragati Verma -

aceld profile image

(Part 2) Golang Framework Hands-on - KisFlow Streaming Computing Framework - Project Construction / Basic Modules

Aceld -