Know each system’s responsibilities

Mike Solomon

When I was working on the web application flesh, I needed to use the Go SQL package to run some queries. Unsurprisingly, I needed to substitute values in for parameters using special methods in Go’s SQL API, since simple string concatenation could potentially lead to SQL injection vulnerabilities. I’ve done this sort of thing before, so I recognized the question mark syntax I found in an example that read:

err := db.QueryRow("SELECT name FROM foo WHERE id=?", id).Scan(&s)

Unfortunately, when I tried to run the code against my database (Postgres) the parameters weren’t getting substituted in.

I quite literally spent hours pouring over the Go documentation and searching the Internet for anything that could tell me what was wrong. The two examples in the Go documentation were very minimal but seemed to suggest that I was simply using the functions as expected.

My search ended when I finally realized that the substitution didn’t take place in Go.

The Go package I was using forwarded the query and parameters along to Postgres, which in retrospect is perfectly natural because Postgres knows how to substitute parameters appropriately, while the Go code doesn’t need to know the specifics.

The problem still isn’t immediately obvious—shouldn’t Postgres substitute the parameters correctly? Well, it turns out that Postgres doesn’t support question mark syntax for parameter substitution; it instead uses numbered parameters such as $1, $2, …. The Go examples simply chose a common (yet not universal) parameter substitution convention.

The point is that if I had understood which system was responsible for the action I was concerned about – parameter substitution – then I would have saved an immense amount of time debugging. Instead of searching for a solution in Go documentation, I would have checked how Postgres expected it to be done and found an answer in minutes.

Next time you have trouble figuring out why a system’s behavior doesn’t match your expectations, make sure you are examining the right system—it could be you are looking at the client and not the provider.