problem xorm generate SQL stmt with WHERE when column name is SQL keyword #1324

New Issue

lunny · 2019-06-12T09:00:37Z

huanjinzi commented

2019-06-12 09:00:37 +00:00

(Migrated from github.com)

xorm生成的SQL语句：

SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE Index=? AND Platform=? LIMIT 1[1 1]

WHERE Index=? AND Platform=?，

正确 `Index`
错误 Index，没有(`)

Index为关键字，所以SQL执行时候会有语法错误

xorm生成的SQL语句： ``` SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE Index=? AND Platform=? LIMIT 1[1 1] ``` ` WHERE Index=? AND Platform=?`， * 正确 \`Index\` * 错误 Index，没有(`) >Index为关键字，所以SQL执行时候会有语法错误

lunny commented

2019-06-12 09:03:09 +00:00

Could you give your code here?

huanjinzi commented

2019-06-12 09:14:50 +00:00

(Migrated from github.com)

@lunny
when I execute code below:

engine := this.GetReadEngine()
engine.Where(query)

xorm will generate SQL statement with WHERE,in WHERE sub statement,column name don't quoted by (`).

Example

SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE Index=1 AND Platform=1

when column name is SQL keyword,such as SELECT、INDEX, SQL statement execute with syntax error!

@lunny when I execute code below: ``` engine := this.GetReadEngine() engine.Where(query) ``` `xorm` will generate SQL statement with `WHERE`,in `WHERE` sub statement,column name don't quoted by **(`)**. **Example** ``` SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE Index=1 AND Platform=1 ``` when column name is SQL keyword,such as `SELECT`、`INDEX`, SQL statement execute with syntax error!

lunny commented

2019-06-12 09:36:13 +00:00

How is the query's struct's definition?

How is the `query`'s struct's definition?

huanjinzi commented

2019-06-12 14:27:27 +00:00

(Migrated from github.com)

model struct

type Recommend struct {
	Id         int       `xorm:"not null pk autoincr comment('') INT(11)"`
	Index      int       `xorm:"not null comment('') INT(11)"`
	Platform   int       `xorm:"not null comment('') INT(11)"`
}

var r *Recommend
var query = map[string]interface{}{}
query["Platform"] = 1
query["Index"] = 1

engine := this.GetReadEngine()
engine.Where(query).Get(r)

execute above code, will see from xrom debug log:

SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE Index=? AND Platform=? [1 1]

because Index is SQL keyword, above SQL statement has syntax error.

when use:

query["`Platform`"] = 1
query["`Index`"] = 1

xorm SQL debug log is:

SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE `Index`=? AND `Platform`=? [1 1]

it works.

Is this a problem?

model struct ```go type Recommend struct { Id int `xorm:"not null pk autoincr comment('') INT(11)"` Index int `xorm:"not null comment('') INT(11)"` Platform int `xorm:"not null comment('') INT(11)"` } ``` ```go var r *Recommend var query = map[string]interface{}{} query["Platform"] = 1 query["Index"] = 1 engine := this.GetReadEngine() engine.Where(query).Get(r) ``` execute above code, will see from `xrom` debug log: ```sql SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE Index=? AND Platform=? [1 1] ``` because `Index` is SQL keyword, above SQL statement has syntax error. when use: ```go query["`Platform`"] = 1 query["`Index`"] = 1 ``` `xorm` SQL debug log is: ```sql SELECT `Id`, `Index`, `Platform` FROM `recommend` WHERE `Index`=? AND `Platform`=? [1 1] ``` it works. **Is this a problem?**

Sign in to join this conversation.

No Milestone

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#1324