TheLazy.Dev
Portfolio GitHub

Blog

go |sql |security |tutorial

Preventing SQL Injection with Golang

wiliamvj

Wiliam V. Joaquim

Posted on March 26, 2024

Preventing SQL Injection with Golang

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

💖 💪 🙅 🚩
wiliamvj
Wiliam V. Joaquim

Posted on March 26, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Preventing SQL Injection with Golang
go Preventing SQL Injection with Golang

March 26, 2024