escaping subqueries and other chars

[cryptix]

Hi,

when I started adding tests for one of my db package I had the problem that my queries mostly all contained sub queries (and thus ( and )) were spread across multiple lines or contained other regex-relevant characters which then were treated by the regex engine which then resulted in lots of exec query 'rawQuery', does not match regex 'rawQuery'

After escaping the first bunch by hand I hacked together this small helper:

// small testing helper to escape `(`, `)` and `$` to not be used as regex modifiers
func escapeQueryToRegex(in string) (out string) {
    // replace multiple spaces, tabs and newlines with one space
    out = strings.Join(strings.Fields(in), " ")

    // slice of regex relevant characters which need to be escaled
    chars := []string{`(`, `)`, `$`, `+`}
    for _, r := range chars {
        out = strings.Replace(out, r, `\`+r, -1)
    }

    return out
}

I wonder if there is a smarter solution for this problem and if not would have no problem with you guys including this in go-sqlmock. It's an awesome package. Thanks!

[l3pp4rd]

hi, sorry for the late answer. could you give an example of the query you had troubles with?
currently the query stripper does it in a simple way, by stripping only whitespace. I agree this is not a perfect solution it may be improved, cheers.

[l3pp4rd]

Well, the issue you had, most probably is because you haven't escaped regular expression special characters. See the commit reference above. And it would be a good practice to keep your query matching short, because sqlmock matches them in the given order. If you expect a query "SELECT" and an update query is fired, then a test will fail, because it matches queries in order. So you can keep them like:

sqlmock.ExpectQuery("SELECT (.*) FROM orders")
sqlmock.ExpectQuery("SELECT (.*) FROM users")
sqlmock.ExpectQuery("SELECT (.*) FROM orders")
sqlmock.ExpectExec("UPDATE orders")
sqlmock.ExpectExec("INSERT INTO products")

If queries are not fired in the same order, the test would fail.
The query stripper only strips whitespace, newlines and such and makes only a single space between sql parts.

[l3pp4rd]

closing it, if there are still some questions - reopen

[thurt]

fyi for future readers: you may find regexp.QuoteMeta helpful. It will automatically escape special regexp characters.