Name binding mode incorrectly recognizes colons in string literals as variable start points

[suiriass]

Hello,

I've encountered an issue with the name binding mode in the sqlx ORM framework. When using the colon (:) symbol as the variable start point, the framework generally works as expected. However, if a string literal in the SQL query contains a colon (e.g., an IPv6 address), the framework incorrectly recognizes it as a variable and requires a variable parameter. This seems to be a bug.

To reproduce the issue, consider the following example:

`
package main

import (
"fmt"
"github.com/jmoiron/sqlx"
_ "github.com/mattn/go-sqlite3"
)

type User struct {
ID int db:"id"
Name string db:"name"
IPv6 string db:"ipv6"
}

func main() {
db, err := sqlx.Connect("sqlite3", ":memory:")
if err != nil {
fmt.Println("Error connecting to the database:", err)
return
}

db.MustExec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, ipv6 TEXT)`)

query := `INSERT INTO users (name, ipv6) VALUES (:name, '2001:0db8:85a3:0000:0000:8a2e:0370:7334')`
_, err = db.NamedExec(query, map[string]interface{}{
	"name": "John Doe",
})

if err != nil {
	fmt.Println("Error executing query:", err)
	return
}

fmt.Println("User inserted successfully")

}
`
In this example, the IPv6 address is directly written as a string literal in the SQL query, and it contains colons. The framework incorrectly recognizes these colons as variable start points and expects them to be included in the parameter map.

I would appreciate it if you could look into this issue and provide a fix or workaround. Thank you for your time and effort in developing and maintaining this great ORM framework.

Best regards,

[evorts]

@suiriass was it in your case it is impossible to insert the ipv6 value as arguments?
for example:

query := `INSERT INTO users (name, ipv6) VALUES (:name, :ipv6)`
_, err = db.NamedExec(query, map[string]interface{}{
	"name": "John Doe",
	"ipv6": "2001:0db8:85a3:0000:0000:8a2e:0370:7334",
})

[suiriass]

@evorts This is an example to illustrate the problem, and sometimes unexpected bugs will appear.
`

example

select * from port_groups where group =:group and port = 'tcp:8080'
`

[gurza]

@suiriass the issue you're experiencing is a limitation of the sqlx functionality. It interprets colons as the start of a named parameter, regardless of their context. This means when a colon appears in a string literal, it's still interpreted as the start of a parameter. I can't agree with you that this is a bug.

The simplest solution to this issue is to avoid embedding values directly in the SQL query string. Instead, provide them as parameters. @evorts has already suggested this solution to you. This is also a good practice for preventing SQL injection attacks.

[gurza]

@suiriass it appears that further discussion of this issue no longer holds any practical significance. I would suggest closing it.

[suiriass]

@suiriass it appears that further discussion of this issue no longer holds any practical significance. I would suggest closing it.

I agree with the description of parameter parsing, I have read and verified the related use of gorm, similar problems exist, but gorm has an optimization point. If there are no variables in the map, you can ignore the variables and string substitutions to avoid errors and get the correct result. So I suggest whether this point can be optimized, thank you

        # gorm code
        var rrList []DomainDnsRrInfo
	query := "SELECT * FROM domain_dnsrr_info WHERE acc_domain = '@acc_domain'"
	namedParams := map[string]interface{}{
		//"acc_domain": "dnsrrtes.com.",    comments no errer, otherwise an error will be reported
	}
	err = db.Raw(query, namedParams).Scan(&rrList).Error