GORM uses the database/sql‘s argument placeholders to construct the SQL statement, which will automatically escape arguments to avoid SQL injection

NOTE The SQL from Logger is not fully escaped like the one executed, be careful when copying and executing it in SQL console

Query Condition

User’s input should be only used as an argument, for example:

  1. userInput := "jinzhu;drop table users;"
  3. // safe, will be escaped
  4. db.Where("name = ?", userInput).First(&user)
  6. // SQL injection
  7. db.Where(fmt.Sprintf("name = %v", userInput)).First(&user)

Inline Condition

  1. // will be escaped
  2. db.First(&user, "name = ?", userInput)
  4. // SQL injection
  5. db.First(&user, fmt.Sprintf("name = %v", userInput))

When retrieving objects with number primary key by user’s input, you should check the type of variable.

  1. userInputID := "1=1;drop table users;"
  2. // safe, return error
  3. id,err := strconv.Atoi(userInputID)
  4. if err != nil {
  5.     return error
  6. }
  7. db.First(&user, id)
  9. // SQL injection
  10. db.First(&user, userInputID)
  11. // SELECT * FROM users WHERE 1=1;drop table users;

SQL injection Methods

To support some features, some inputs are not escaped, be careful when using user’s input with those methods

  1. db.Select("name; drop table users;").First(&user)
  2. db.Distinct("name; drop table users;").First(&user)
  4. db.Model(&user).Pluck("name; drop table users;", &names)
  6. db.Group("name; drop table users;").First(&user)
  8. db.Group("name").Having("1 = 1;drop table users;").First(&user)
  10. db.Raw("select name from users; drop table users;").First(&user)
  12. db.Exec("select name from users; drop table users;")
  14. db.Order("name; drop table users;").First(&user)

The general rule to avoid SQL injection is don’t trust user-submitted data, you can perform whitelist validation to test user input against an existing set of known, approved, and defined input, and when using user’s input, only use them as an argument.