Gosqlite（golang）中出现奇怪的sqlite错误

Question

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 ' ' 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!

Answer 1

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.