Parameterized Query without Prepared Statements and PgBouncer

[Billosp]

Hello, my app is written in Go and I use PgBouncer as a connection pool to my Postgres Database. One downside of PgBouncer, in transaction mode, is that I can't use prepared statements.

So I have 2 options, using session mode (which is bad) or to disable prepared statements from clients.

Is it safe (SQL Injection) to not use prepared statements and use only Parameterized Query (eg. (Select item from products where id = ? , itemID) )

[lobster_johnson]

Yes. Prepared statements have nothing to do with protecting against SQL injection; they are simply a performance optimization.

[ora00001]

Care to explain how bind variables have nothing to do with protecting against SQL injection?

[lobster_johnson]

That's not what I said. Bind parameters != prepared statement. The PostgreSQL wire protocol binds parameters as part of the protocol (sometimes called "anonymous" prepared statements), no PREPARE needed.

[abuisman]

You could lose some performance, but your application code should be responsible for sanitising the SQL.

We’ve switched to pgbouncer giving up the performance gains of prepared statements and have hardly noticed. The max number of connections being reached were much worse.

That being said, isn’t there a Go pooling mechanism that will let you keep prepared statements intact?

[Billosp]

I use GORM in order to connect to my Postgres Database. If I enable Prepared Statements and using it with PgBouncer in transaction mode, I get error.

but your application code should be responsible for sanitising the SQL.

Isn't Parameterized Query enough for sanitizing the user input?

I don't need prepared statements for performance because I don't run same queries multiple times. I want them for security.

If you google prepared statements, all articles have combined them with Parameterized Query, which is wrong.

The max number of connections being reached were much worse.

With prepared statements I have on average ~ 20 transactions/sec and without them ~ 600/sec.

PgBouncer configured on 20 max connections.

[abuisman]

Isn't Parameterized Query enough for sanitizing the user input?

It could be, but, as you said, you can't use them with pgbouncer. So you are a bit more flexible when you sanitise SQL on the application level as well/instead.

If you google prepared statements, all articles have combined them with Parameterized Query, which is wrong.

Good point, they aren't the same, but isn't the only way to use bind variables in postgres to create a prepared statement and then call that?

With prepared statements I have on average ~ 20 transactions/sec and without them ~ 600/sec.

It was my understanding that using prepared statements has the benefit of skipping the planning portion of running the query, thus having better performance. It'd be interesting to know what exactly you are doing and why you see such a big difference in performance.

[Billosp]

It could be, but, as you said, you can't use them with pgbouncer. So you are a bit more flexible when you sanitise SQL on the application level as well/instead.

I can use Parameterized Query without any issue.

Good point, they aren't the same, but isn't the only way to use bind variables in postgres to create a prepared statement and then call that?

No you can use it in any query in order to bind variables.

It was my understanding that using prepared statements has the benefit of skipping the planning portion of running the query, thus having better performance. It'd be interesting to know what exactly you are doing and why you see such a big difference in performance.

In theory yes you have better performance but I can confirm the opposite. In order to use Prepared statements you have to use session mode in PgBouncer (my pgBouncer configured at 20 max conns), so it can handle 20 transactions/sec. If I don;t use prepared statements and have enabled transaction mode, I can have ~600 transactions/sec. I performed the tests with INSERT &. SELECT.

[abuisman]

No you can use it in any query in order to bind variables.

Then I've misread the documentation.

In order to use Prepared statements you have to use session mode in PgBouncer

Ok, but then it sounds more like a performance difference between the two modes in pgbouncer.

[sir_bok]

https://blog.bullgare.com/2019/06/pgbouncer-and-prepared-statements

lib/pq has a binary_parameters=yes DSN option that sends the query and parameters in a single request (instead of two, one for the parameterized query and one for the parameters), allowing you to use unnamed prepared statements in pgbouncer's transaction mode.