Insert NULL into DB?

Question

go

There are such constructions:

DB.User.Create(database.User{Username: "юзернейм1", Password: "пароль"})

func (obj *UserObject) Create(user User) error {
  if (User{}) == user {
    return structEmptyErr()
  }
  var role = MayBeNullStr(user.Role)
  fmt.Println(role)
  var regIP = MayBeNullStr(user.RegIP)
  var regAgent = MayBeNullStr(user.RegAgent)
  var qu = fmt.Sprintf("insert into users (role, username, password, reg_ip, reg_agent) values ('%v', '%v', '%v', '%v', '%v') RETURNING id", role, user.Username, user.Password, regIP, regAgent)
  result, err := obj.db.Exec(context.Background(), qu)
  checkErr(err)
  println(result)
  return nil
}

func MayBeNullStr(str string) interface{} {
  if len(str) < 1 {
    return sql.NullString{}
  }
  return str
}

As I imagine: MayBeNullStr is called, and then if the string is empty, then some thing is returned, which is null, which will be inserted into the database, and everything will be fine.
As a matter of fact: { false} is returned.
Purpose: if for example roles is an empty string, then insert null for roles into the database.
How to do it?

Answer 1

This is not how you can insert records into the database, there will be SQL injection.

var qu = fmt.Sprintf("insert into users (role, username, password, reg_ip, reg_agent) values ('%v', '%v', '%v', '%v', '%v') RETURNING id", role, user.Username, user.Password, regIP, regAgent)
  result, err := obj.db.Exec(context.Background(), qu)

Should be inserted like this

var qu = fmt.Sprintf("insert into users (role, username, password, reg_ip, reg_agent) values (?,  ?,  ?,  ?, ?) RETURNING id", )
  result, err := obj.db.Exec(context.Background(), qu, role, user.Username, user.Password, regIP, regAgent)

And with this approach, you can already wrap the values ​​in your MayBeNullStr function

result, err := obj.db.Exec(context.Background(), qu, MayBeNullStr(role), MayBeNullStr(user.Username) и т.д.)

But with this approach, it will be necessary to provide for the correct extraction of data, otherwise it will panic.
Something like thisSELECT COALESCE(Username, '') AS Username