Replace prepareStatement with "With" feature, create real prepared statements

[domdorn]

Hi,

when using postgresql-async, I've noticed that we're actually "wasting" prepared statements, because they are implemented in a way that they can only be used in a one-time fashion. Imho, the reason people are using prepared statements is to get some type safety and prevent SQL Injection attacks.

I was thinking, that it might be a better way to allow the simple binding / prevent sql injection using "WITH" queries, e.g. WITH var(lastname) AS (SELECT 'Troy'::varchar(16)) SELECT * FROM users, var v WHERE lastname = v.lastname; ( see https://www.postgresql.org/docs/current/static/queries-with.html )

we could have something like connection.createQueryWith("SELECT * FROM users WHERE lastname = $1", Seq("Troy")) and a

connection.createPreparedStatement("insert into users values (?)") {
 stmt => {
 stmt.execute(Seq("troy"))
 stmt.execute(Seq("steve"))
 stmt.executeAll(Seq( Seq("chris"), Seq("michael"))
}
}

What do you think?

[domdorn]

Is there any interest in this? I could try to create a PR, but probably would need some guidance. I think I would have to create a own subclass of ClientMessage with appropriate Encoders that would prepend the WITH (...) part of the query ?

[mauricio]

I don't understand this, where did you see that the client is not using actual prepared statements and is wasting them?

[domdorn]

The problem is not "not using prepared statements" - the problem is, we're using prepared statements in a "just one query" fashion. Prepared Statements per se are made for multiple executions, yet the API provided allows only to execute them once. The "WITH" statement would more or less give the same features of Prepared Statements with a reduced cost if they are only used in a one-time fashion (because they don't allocate any persistent data structure on the server).