So this DB query doesn't work unless I Sprintf into a string first. Why?

I am using PostgreSQL and have used db.Query before in a similar way, but for some reason when using PostGIS functions it does not work. What am I missing?

This works (however, obviously unsafe unsanitized data)

query := fmt.Sprintf(`SELECT id, ST_X(location::geometry) as lng, ST_Y(location::geometry) as lat, photo_ext, name, last_updated::DATE as lu
                             FROM   locations
                             WHERE  ST_Distance_Sphere(
                                            ST_GeomFromText(
                                                'POINT(%f %f)',4326
                                            ),
                                            ST_GeomFromText(
                                                'POINT(' || ST_X(location::geometry) || ' ' || ST_Y(location::geometry) || ')', 4326
                                            )
                                    ) <= 30 * 1609.34`,
    lngF, latF,
)
rows, err := db.Query(query)

pq: could not determine data type of parameter $1.

rows, err := db.Query(`SELECT id, ST_X(location::geometry) as lng, ST_Y(location::geometry) as lat, photo_ext, name, last_updated::DATE as lu
                             FROM   locations
                             WHERE  ST_Distance_Sphere(
                                            ST_GeomFromText(
                                                'POINT($1 $2)',4326
                                            ),
                                            ST_GeomFromText(
                                                'POINT(' || ST_X(location::geometry) || ' ' || ST_Y(location::geometry) || ')', 4326
                                            )
                                    ) <= 30 * 1609.34`, lngF, latF)

I've tried various variations of the latter with zero success. Golang community, please help.

---

评论：

wwader:

Try to change POINT(%f %f) to POINT(? ?)

NikkoTheGreeko:

Well, it's actually POINT($1 $2) already.  %f is from the unsafe Sprintf version which I want to avoid.

wwader:

Sorry i meant change POINT($1 $2) to POINT(? ?). Does that work?

NikkoTheGreeko:

Postgres's driver requires $1, $2, $3 etc...  ?, ?, ? is for MySQL and I think some others.

wwader:

Ok! did not know placeholders where done by drivers... but that makes very much sense. Good to know!

NikkoTheGreeko:

Fortunately in this example I am able to convert to a float which sanitizes the values.  There's another statement using user inputted text which scares me.

Justinsaccount:

What types are lngF and latF?

NikkoTheGreeko:

I've tried float32, float64, and string with the same result.

ephemient:

Usually something like $1::float4 or $1::float8 would work, but in this case it looks like you actually want to interpolate into the 'POINT()' as a string? I'd probably use just a single placeholder and bind fmt.Sprintf("POINT(%f %f)", lngF, latF) as a string.

mgutz:

are you missing a comma? point($1, $2)

ChristophBerger:

pq: could not determine data type of parameter $1.

Of what data type are the parameters lngF anD latF? Maybe pq needs different types.

Did you check the pq repository for any related issues?

And what is the simplest query string that fails with this error message? Like, does select * from table where a = $1 fail? (Try different types for the var that goes into $1)