mysql语句中ORDER BY列不能参数化

sortfields={
    0:"hostname",
    1:"lasttime",
    2:"vendor",
    3:"softname",
    4:"softversion",
    5:"filename",
    6:"fileversion",
    7:"sha1",
    8:"out_ip",
    9:"os",
    10:"manufacturer",
    11:"kernel",
    12:"username"          #指的用户那列，用户名不用排序
}
reverses={
    0:"ASC",
    1:"DESC"
}


sql="select a.deviceid,a.username,a.types,a.userpri,a.chpswtime,a.last_login_time,a.clients,h.hostname from `{db}`.account a left join `{db}`.hostinfo h on (a.deviceid = h.deviceid) where INSTR(a.username,%s) and INSTR(h.hostname,%s) and (a.last_login_time >%s and a.last_login_time <%s) order by %s %s limit %s,%s;".format(db=company)
results = conn.execute(sql, (username,hostname,firsttime,lastttime,sortfields[sortedby],reverses[reversed],postion, count)).fetchall()

改成下面就可以了：

sql="select a.deviceid,a.username,a.types,a.userpri,a.chpswtime,a.last_login_time,a.clients,h.hostname from `{db}`.account a left join `{db}`.hostinfo h on (a.deviceid = h.deviceid) where INSTR(a.username,%s) and INSTR(h.hostname,%s) and (a.last_login_time >%s and a.last_login_time <%s) order by {sorts} {reverses} limit %s,%s;".format(db=company,sorts=sortfields[sortedby], reverses=reverses[reversed])
results = conn.execute(sql, (username,hostname,firsttime,lastttime,postion, count)).fetchall()

使用参数化的order by后面的字段为"字段"，而我们排序需要的是字段，不带引号的