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:
- 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
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:
This is a reference to a Gemini suggestion, which seems entirely hallucinated:
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