databasegogeospatialcratecratedb

How to pass a location parameter in when calculating distance?

I would like to execute the following query from my Go application

SELECT hour, count 
FROM location_counts 
WHERE distance(point, 'POINT(-80.205 26.14)') <= 100

I will be passing in the POINT(-80.205 26.14) and 100 values and I'm not quite sure how to include the POINT... parameter in a secure way. When I try to pass it in as a parameter like below:

rows, err := g.DB.Query(
    "select hour, count from location_counts where distance(point, '?') <= ?",
    "POINT(-80.205 26.14)",
    100,
)

It throws the error:

SQLActionException[SQLParseException: Cannot cast 'POINT(-80.205 26.14)' to type double]

If I drop the value directly into the string it works but I would open myself up for SQL Injection vulnerabilities.

How can I securely include the POINT... value in my query?

Solution

  • Removing the surrounding quotes of the placeholder question mark should fix it, like:

    rows, err := g.DB.Query( "select hour, count from location_counts where distance(point, ?) <= ?", "POINT(-80.205 26.14)", 100, )

    Placeholders should never be quoted as they will be replaced directly by the typed parameter.