MySQL在Go驱动程序中报错语法错误。

huangapple go评论88阅读模式
英文:

mysql complains at syntax from go driver

问题

我正在使用github.com/go-sql-driver/mysql和mysql 5.7.10。我有一个函数:

bulkSetStatus := func(docVers []*_documentVersion) error {
    if len(docVers) > 0 {
        query := strings.Repeat("CALL documentVersionSetStatus(?, ?); ", len(docVers))
        args := make([]interface{}, 0, len(docVers)*2)
        for _, docVer := range docVers {
            args = append(args, docVer.Id, docVer.Status)
        }
        _, err := db.Exec(query, args...)
        return err
    }
    return nil
}

len(docVers) == 1时,它可以正常工作,但是当有多个时,会导致多个CALL到存储过程,出现错误:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL documentVersionSetStatus(?, ?)' at line 1

我还尝试在每个调用之间加入换行符,但是仍然出现相同的错误。如果我在mysql workbench中运行多个对该存储过程的CALL,它可以正常工作,我不确定这里的语法有什么问题。

我已经记录下了带有参数的完整文本,结果如下:

CALL documentVersionSetStatus("9c71cac14a134e7abbc4725997d90d2b", "inprogress"); CALL documentVersionSetStatus("beb65318da96406fa92990426a279efa", "inprogress");
英文:

I'm using the github.com/go-sql-driver/mysql and mysql 5.7.10. I have a function:

bulkSetStatus := func(docVers []*_documentVersion) error {
	if len(docVers) > 0 {
		query := strings.Repeat("CALL documentVersionSetStatus(?, ?); ", len(docVers))
		args := make([]interface{}, 0, len(docVers)*2)
		for _, docVer := range docVers {
			args = append(args, docVer.Id, docVer.Status)
		}
		_, err := db.Exec(query, args...)
		return err
	}
	return nil
}

which works if len(docVers) == 1 but when there are more, resulting in multiple CALLs to the stored procedure, it errors:

> Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL documentVersionSetStatus(?, ?)' at line 1

I have also tried a newline character between each call but I get the same error. If I run this in mysql workbench with multiple CALLs to this procedure it works fine, I'm not sure what is wrong with the syntax here.

I have logged out the exact full text with the arguments and it is as expected:

CALL documentVersionSetStatus("9c71cac14a134e7abbc4725997d90d2b", "inprogress"); CALL documentVersionSetStatus("beb65318da96406fa92990426a279efa", "inprogress");

答案1

得分: 3

go-sql-driver默认情况下不允许在一个查询中使用多个语句(就像你通过将多个CALL语句链接在一起那样),这是出于安全考虑,以防攻击者成功执行SQL注入(例如通过注入0 OR 0; DROP TABLE foo)。

要允许这样做,你必须在连接到数据库时显式启用它,例如通过传递multiStatements参数:

db, err := sql.Open("mysql", "user:password@/dbname?multiStatements=True")

来源:https://github.com/go-sql-driver/mysql#multistatements

英文:

go-sql-driver, by default, does not allow you to have multiple statements in one query (as you are doing by chaining together multiple CALL statements like that) due to the security implications if an attacker manages to perform SQL injection (for example, by injecting 0 OR 0; DROP TABLE foo).

To allow this, you must explicitly enable it by passing multiStatements parameter when connecting to the database, e.g.

db, err := sql.Open("mysql", "user:password@/dbname?multiStatements=True")

Source: https://github.com/go-sql-driver/mysql#multistatements

答案2

得分: 0

我已经通过手动字符串插值来修复了proc调用,而不是使用正确的?方式来进行:

bulkSetStatus := func(docVers []*_documentVersion) error {
	if len(docVers) > 0 {
		query := strings.Repeat("CALL documentVersionSetStatus(%q, %q); ", len(docVers))
		args := make([]interface{}, 0, len(docVers)*2)
		for _, docVer := range docVers {
			args = append(args, docVer.Id, docVer.Status)
		}
        _, err := db.Exec(fmt.Sprintf(query, args...))
        return err
	}
	return nil
}

所以我将?替换为%q,并使用fmt.Sprintf来注入参数,我应该注意到slugonamission的答案部分正确,我确实需要添加连接字符串参数multiStatements=true,以便使其与我的其他更改一起工作。我将在github仓库上记录一个问题,看起来当有多个语句时可能存在一些参数插值问题,我认为错误发生是因为mysql数据库尝试运行带有?文字的脚本。

英文:

I have fixed the proc call by doing some manual string interpolation for the parameters instead of using the correct ? way of doing it:

bulkSetStatus := func(docVers []*_documentVersion) error {
	if len(docVers) > 0 {
		query := strings.Repeat("CALL documentVersionSetStatus(%q, %q); ", len(docVers))
		args := make([]interface{}, 0, len(docVers)*2)
		for _, docVer := range docVers {
			args = append(args, docVer.Id, docVer.Status)
		}
        _, err := db.Exec(fmt.Sprintf(query, args...))
        return err
	}
	return nil
}

so I swap out the ? for %q and us fmt.Sprintf to inject the parameters, I should note that slugonamission's answer is partially correct, I did need to add the connection string parameter multiStatements=true in order to get this to work with my other changes. I will log an issue on the github repo it looks like there may be some param interpolation issue when there is more than one statement, I think the error was happening because the mysql db was trying to run the script with ? literals in it.

huangapple
  • 本文由 发表于 2016年2月25日 04:14:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/35612144.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定