Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

database/sql: "pq: could not determine data type of parameter $1" #27500

Closed
BradEwing opened this issue Sep 4, 2018 · 2 comments
Closed

database/sql: "pq: could not determine data type of parameter $1" #27500

BradEwing opened this issue Sep 4, 2018 · 2 comments

Comments

@BradEwing
Copy link

@BradEwing BradEwing commented Sep 4, 2018

What version of Go are you using (go version)?

go version go1.11 darwin/amd64

Does this issue reproduce with the latest release?

Yes

What operating system and processor architecture are you using (go env)?

GOARCH="amd64"
GOHOSTARCH="amd64"
GOHOSTOS="darwin"
GOOS="darwin"

What did you do?

I've prepared the following code, running against a PostgresSQL 9.6.9 database:

func GeoQuery(ctx context.Context, db *sql.DB, latitude float64, longitude float64, radius int, categoryString string) ([]*GeoRow, error) {
	q := `SELECT
		ST_Contains(geometry, ST_GeomFromText('SRID=4326; POINT($1::double precision $2::double precision)')) as contains,
		round(ST_Distance_Sphere(geometry, ST_GeomFromText('SRID=4326; POINT($3::double precision $4::double precision)'))) as distance,
		FROM geos
		WHERE category in ($5)
		AND ST_Intersects(geometry, ST_Buffer(ST_GeogFromText('SRID=4326; POINT($6::double precision $7::double precision)'), $8)::geometry);
	`
	rows, err := db.QueryContext(ctx, q, longitude, latitude, longitude, latitude, categoryString, longitude, latitude, radius)
	if err != nil {
		return nil, err
	}

	formattedRows := make([]*GeoRow, 0)
	for rows.Next() {
		// Process the rows...
	}
	return formattedRows, nil
}

What did you expect to see?

I expect the query to execute successfully and return any data if it matches the conditions. When I execute the SQL myself on the DB (filling in the values myself), it works.

What did you see instead?

I consistently see the following error, regardless if I explicitly cast types or not:
pq: could not determine data type of parameter $1

If I prepare my SQL statement using fmt.Sprintf instead of passing args to db.QueryContext, it works just fine. However I want to avoid this due to concerns over SQL injection. Ex:

q := fmt.Sprintf(`SELECT
	ST_Contains(geometry, ST_GeomFromText('SRID=4326; POINT(%f, %f)')) as contains,
	round(ST_Distance_Sphere(geometry, ST_GeomFromText('SRID=4326; POINT(%f %f)'))) as distance,
	classification_category,
	classification_type,
	FROM geos
	WHERE category in (%s)
	AND ST_Intersects(geometry, ST_Buffer(ST_GeogFromText('SRID=4326; POINT(%f %f)'), %d)::geometry);`, longitude, latitude, longitude, latitude, categoryString, longitude, latitude, radius)
rows, err := db.QueryContext(ctx, q)
if err != nil {
	return nil, err
}
@adamdecaf
Copy link
Contributor

@adamdecaf adamdecaf commented Sep 4, 2018

@BradEwing Is this a problem in github.com/lib/pq? lib/pq#639 looks similar.

and you will now observe that the error for attempt r3 is pq: could not determine data type of parameter $1 which is what inspired this whole exercise in the first place. the solution of course is to use $1::int[] IS NULL to bypass the type inference step which is apparently lacking ever so slightly

@gopherbot add WaitingForInfo

@BradEwing
Copy link
Author

@BradEwing BradEwing commented Sep 4, 2018

@adamdecaf upon some deeper investigation it looks like you're correct that the issue is with the github.com/lib/pq driver and not database/sql. I'll close this ticket and reopen over there. Thanks for the quick response!

@BradEwing BradEwing closed this Sep 4, 2018
@golang golang locked and limited conversation to collaborators Sep 4, 2019
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
3 participants
You can’t perform that action at this time.