Due to the security assurance of ORM, all input parameters are executed in a prepared mode at the underlying level to prevent common SQL injection risks. In certain scenarios, we expect to embed custom SQL statements in the generated execution SQL statements, then we can use the RawSQL feature of ORM through the gdb.Raw type. Let’s look at a few examples.

Using RawSQL in Insert

gdb.Raw is a string type, and the parameter of this type will be directly embedded as an SQL fragment into the SQL statement submitted to the underlying level. It will not be automatically converted into a string parameter type, nor will it be treated as a prepared parameter. For example:

  1. // INSERT INTO `user`(`id`,`passport`,`password`,`nickname`,`create_time`) VALUES('id+2','john','123456','now()')
  2. g.Model("user").Data(g.Map{
  3.     "id":          "id+2",
  4.     "passport":    "john",
  5.     "password":    "123456",
  6.     "nickname":    "JohnGuo",
  7.     "create_time": "now()",
  8. }).Insert()
  9. // Execution error: Error Code: 1136. Column count doesn't match value count at row 1

After refactoring with gdb.Raw:

  1. // INSERT INTO `user`(`id`,`passport`,`password`,`nickname`,`create_time`) VALUES(id+2,'john','123456',now())
  2. g.Model("user").Data(g.Map{
  3.     "id":          gdb.Raw("id+2"),
  4.     "passport":    "john",
  5.     "password":    "123456",
  6.     "nickname":    "JohnGuo",
  7.     "create_time": gdb.Raw("now()"),
  8. }).Insert()

Using RawSQL in Update

  1. // UPDATE `user` SET login_count='login_count+1',update_time='now()' WHERE id=1
  2. g.Model("user").Data(g.Map{
  3.     "login_count": "login_count+1",
  4.     "update_time": "now()",
  5. }).Where("id", 1).Update()
  6. // Execution error: Error Code: 1136. Column count doesn't match value count at row 1

After refactoring with gdb.Raw:

  1. // UPDATE `user` SET login_count=login_count+1,update_time=now() WHERE id=1
  2. g.Model("user").Data(g.Map{
  3.     "login_count": gdb.Raw("login_count+1"),
  4.     "update_time": gdb.Raw("now()"),
  5. }).Where("id", 1).Update()

Using RawSQL in Select

The time function now() will be converted into a string to be executed as an SQL parameter:

  1. // SELECT * FROM `user` WHERE `created_at`<'now()'
  2. g.Model("user").WhereLT("created_at", "now()").All()

After refactoring with gdb.Raw:

  1. // SELECT * FROM `user` WHERE `created_at`<now()
  2. g.Model("user").WhereLT("created_at", gdb.Raw("now()")).All()