Interpolation with Named Paramters

[mlh758]

I am trying to run a query using named parameters like this:

select
*
from
some_table
where
client like @CLIENT_NAME

The @ symbol appears to be the correct syntax based on the docs here but when I run this with this adapter like this:

queryTerm := "test"
rows, err := stmt.QueryContext(ctx, sql.Named("CLIENT_NAME", queryTerm))

I get Error: [42703] Column "CLIENT_NAME" does not exist

[mlh758]

The argument parsing looks really similar to what Godror is initially doing to pull out arguments but then later it checks the name field and binds appropriately.

It seems like the Vertica adapter is binding the values here and isn't checking for a name in the argument. Does that switch need to be modified to append the right data to the message?

[huebnerr]

Thanks for the feature request. As it is, the Vertica wire protocol doesn't support server-side binding of named parameters (only positional). The client-side driver could be made to mimic this behavior in Vertica with a map of named arguments and their indices in the statement, then forcing an interpolation.

Although this is not on the roadmap today, I've added it to the backlog. However, if you wish to contribute an implementation, please make sure to sign the CLA.

[mlh758]

I might be willing to take a stab at emulating the behavior since it would be really useful for my use case but could you elaborate on what you mean about it not supporting server-side binding of named parameters?

The docs here have this example:

SELECT customer_name, customer_address, customer_city, customer_state
FROM customer_dimension WHERE customer_state = @STATE;

it seems like @STATE is a named parameter, not just a positional argument in this case.

[huebnerr]

I'll have to ask @tomwall to explain that side further.

[sitingren]

The Vertica wire protocol support server-side binding of positional parameters (i.e. prepared statements), this means the driver sends the query (using ? as parameter placeholder) and parameter values separately to the server. The server would parse the query, bind parameter values and execute the query. The limitation is server-side binding does not allow executing multiple commands per call. But the advantage is it prevents SQL injection attacks.

Client-side binding of parameters means the driver takes the query (using ? as parameter placeholder) and parameter values, combines them into a single query string, and then send this string to the server directly. This is under the risk of SQL injection, but it allows you to execute multiple commands per call.

The syntax for parameter placeholders depends on Go drivers standard, not ADO.NET's standard, does Golang recommend using the at sign (@) or a colon (:) character for named parameters?

Therefore, I think there are two kind of ways to implement named parameters:

1. Add a wrapper of server-side binding: convert parameter values into a positional ordered parameter set; replace named parameter placeholders (@ or :) in the query with ?.
2. Add Client-side binding of named parameters. (Optional) Add SQL injection check.

[mlh758]

As far as I know, the Go sql package is neutral about what parameter syntax to use. Here is the issue where the feature was added. The oracle package uses : for all parameters, be it :1, :2 or :client, :environment.

Are you saying that the ADO.NET adapter is just emulating named arguments? I hadn't noticed before that the parameter docs I linked are under the ADO portion of the documentation. If we're emulating I would very much prefer to go with option 1 and convert the named arguments into positional arguments and then let the Vertica database handle interpolating the parameters in. I'm working with user input for some of my parameters and I'm not confident I could write a SQL injection check resilient enough to handle all possible edge cases.

Unfortunately that will probably require writing some kind of sql parser to correctly locate the arguments and replace them.

The goal would be to write a query like:

select
col1, col2
from
table_a a
join table_b b on b.a_id = a.id
  and client = :client
  and domain= :domain
join table_c c on c.b_id = b.id
  and client = :client
  and domain :domain
where a.client = :client
and a.domain = :domain
and c.filter = :filter

and call it with:

conn.Query(
  queryString,
  sql.Named("client", client"),
  sql.Named("domain", domain),
  sql.Named("filter", filter)
)

For whatever reason Vertica doesn't propagate the filter clause through the joins so we can't write the above as:

select
col1, col2
from
table_a a
join table_b b on b.a_id = a.id
  and b.client = a.client
  and b.domain= a.client
join table_c c on c.b_id = b.id
  and c.client = b.client
  and c.domain b.client
where a.client = :client
and a.domain = :domain
and c.filter = :filter

Using the latter query structure leads to a query plan that may never complete while the former completes in a couple of seconds. Unfortunately, with positional arguments only the call on the Go side is going to look like:

conn.Query(
  queryString,
  client,
  domain,
  client,
  domain,
  filter,
  client,
  domain,
...
)

and that's a maintenance nightmare.

[huebnerr]

Have verified with @tomwall that the ADO.NET driver emulates named parameters in the manner I mentioned.

[mlh758]

That's good to know, thank you. For consistency with the existing docs it might be best to continue their @param_name syntax for named parameters.

[mlh758]

I have some code for this ready once #62 merges.

[huebnerr]

Thanks, @mlh758 . #62 is now merged.