英文:
strange sqlite error in gosqlite (golang)
问题
我遇到了一个奇怪的错误，找不出问题出在哪里。
我使用这个函数来保存收到的消息：
func (mdb *MailDB) SaveMail(mail *Mail){
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("无法打开数据库：", err)
return
}
defer conn.Close()
insertsql := fmt.Sprintf(`INSERT INTO mails (sender,subject,text,time) VALUES ("%v", "%v", "%v", %v) ;`,
mail.Sender,mail.Subject,mail.Text,time.Now().Unix())
err = conn.Exec(insertsql)
if err!=nil {
log.Print("maildb插入失败@exec：",err)
log.Print(insertsql)
return
}
}
我得到了这个错误：
2012/05/09 10:10:20 maildb插入失败@exec：SQL错误或缺少数据库：无法识别的标记：""
2012/05/09 10:10:20 INSERT INTO mails (sender,subject,text,time) VALUES ("wLrOBizTcmS1MlqeXydUK9U6YJQ=", "abc", "321
", 1336551020) ;
（在321后面是一个'\n'，我不知道为什么在这里被省略了）
奇怪的是，我可以在sqlite控制台中发送复制粘贴的查询而没有问题。
有人看出问题出在哪里吗？
请帮忙！
英文:
I got a strange error and can't find out whats going wrong.
I use this function to save an incoming message:
func (mdb *MailDB) SaveMail(mail *Mail){
conn, err := sqlite.Open("maildb.db")
if err != nil {
log.Print("Unable to open the database: ", err)
return
}
defer conn.Close()
insertsql := fmt.Sprintf(`INSERT INTO mails (sender,subject,text,time) VALUES ("%v", "%v", "%v", %v) ;`,
mail.Sender,mail.Subject,mail.Text,time.Now().Unix())
err = conn.Exec(insertsql)
if err!=nil {
log.Print("maildb insert fail @exec: ",err)
log.Print(insertsql)
return
}
}
I get this error:
2012/05/09 10:10:20 maildb insert fail @exec: SQL error or missing database: unrecognized token: """
2012/05/09 10:10:20 INSERT INTO mails (sender,subject,text,time) VALUES ("wLrOBizTcmS1MlqeXydUK9U6YJQ=", "abc", "321
", 1336551020) ;
(after 321 is an '\n' i dont know why its ommited here)
The strange thing is, that I can send the copy'n'pasted query in an sqlite console without problems.
do someone see whats going wrong?
please help!
答案1
得分: 2
我认为你的问题是在使用"来界定字符串字面值，而这不是标准的SQL语法，也不被SQLite支持。
引用它的手册：
> 字符串常量是通过将字符串放在单引号（'）中形成的。字符串中的单引号可以通过连续两个单引号来编码 - 就像Pascal中一样。不支持使用反斜杠字符的C风格转义，因为它们不是标准SQL。
所以明显的修复方法应该是使用
insertsql := fmt.Sprintf(`INSERT INTO mails (sender,subject,text,time) VALUES ('%v', '%v', '%v', %v) ;`, ...)
还要注意，像你这样构建SQL语句是天真的，并且容易受到SQL注入攻击的影响。正确的方法是首先创建一个预处理语句，然后将其参数绑定到实际值，然后执行它。我对Go绑定到SQLite没有经验，所以无法确定它们是否支持我提到的API，但我认为你应该尝试调查一下。
英文:
I think your problem is using " to delimit string literals while this is not standard SQL syntax, and not supported by sqlite as well.
To cite its manual:
> A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL
So the obvious fix should be using
insertsql := fmt.Sprintf(`INSERT INTO mails (sender,subject,text,time) VALUES ('%v', '%v', '%v', %v) ;`, ...)
Also note that constructing the SQL statements like you do is naive and is subject to SQL injection attacks. The proper way would be to first create a prepared statement, then bind its parameters to actual values and then executing it. I have no experience with Go bindings to sqlite so I can't tell if they support the API I referred to but I think you should try to investigate this.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论