0

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) )

Improve this question

2 Answers 2

Reset to default
0

If the go library was implemented correctly (and I have no reason to think it wasn't) their parameterized query method should be just as secure as their prepared statement method.

But why are you using pgbouncer in the first place and why do you think session pooling is bad? You seem to be creating a lot of needless problems for yourself.

Improve this answer
3
  • I use a managed DB in Digital Ocean which for a basic plan it gives 25 connections. If I use session pooling I can have 25 transactions/sec. I tested it with transaction mode and it could handle 600 transactions/sec. In many posts they combine prepared statements with parameterized query which is wrong. What are the downsides of just using parameterized query instead of prepared statements (with parameterized queries)?
    – Bill
    Jun 10, 2022 at 10:26
  • @Bill A client side language-specific pooler should be able to be able to accommodate a 25 connection limit at least as well as pgbouncer can. It would just need to be configured appropriately. However, that might also inhibit use of prepared statements. The downside of parameterized statements over prepared ones is the the server needs to parse, analyze, and plan them anew for each execution. This can be a bottleneck especially with complex queries or over partitioned tables. But I would not pre-emptively worry about that.
    – jjanes
    Jun 11, 2022 at 16:08
  • Yes but the prepared statements have value when you use them multiple times eg. in a loop etc. I have created an API with many endpoints (100-120), so in this case I think that parameterized queries, instead of prepared statements, are more valuable. In addition, 600 transactions without prepared statements with pgbouncer instead of 20 trans are better.
    – Bill
    Jun 11, 2022 at 23:53
0

One good way to escape the problem is to put your SQL statement into a PL/pgSQL function. Plans for statements in PL/pgSQL functions will be cached just like prepared statements, so you enjoy the same advantages. Since you don't have to explicitly prepare the statement, this technique can be used just as well with transaction pooling mode.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.