Support for bound parameters

[mcraveiro]

Hi sqlgen developers,

I believe I hit a limitation with calling postgres functions, but I just want to confirm this with you. I want to call assorted postgres functions we have which we use for various clean ups and other housekeeping within the system. These do not have a natural associated type. At present we are doing string concatenation but there is a danger of SQL injection. Claude tells me:

Looking at the actual sqlgen library in this codebase, the execute method signature is:

Result<Nothing> execute(const std::string& _sql) noexcept;

It only takes a single SQL string - there's no variadic version that accepts additional parameters. The sqlgen library version used here doesn't support the ("SELECT ... $1", param1, param2) syntax that Gemini mentioned.

This is a reference to a Gemini suggestion, which seems entirely hallucinated:

1. The Idiomatic Way: Using the session->execute with Bound Parameters

The sqlgen library (and the underlying reflect-cpp integration) does support parameter binding to prevent SQL injection, even for custom statements. However, the syntax for passing parameters to execute is often through a structured object or a variadic list, depending on the specific backend implementation (PostgreSQL/SQLite).

To call a function safely, you should use the placeholder syntax (Postgres uses $1, $2, etc.) and pass the variables as additional arguments.

Idiomatic Example:

// Assume session is a pointer to your sqlgen postgres session
std::string tenant_id = "tenant-123";
std::string user_email = "user@example.com";

// The idiomatic way to avoid injection:
session->execute(
   "SELECT ores_iam_provision_tenant_fn($1, $2)", 
    tenant_id, 
   user_email
);

This hallucination actually seems fairly sensible and natural to sqlgen, so if it's not yet available I think it's worthwhile adding. Creating types for every function is a bit painful as we have a lot of these utility functions. If you think this is a good idea, I'll have a quick go at prototyping it when I get a moment.

Cheers

[mcraveiro]

Prototyped under Add parameterized execute for postgres #122. Please review.