DT
Drizzle Team•17mo ago
z3n

Getting syntax error when using the sql operator (pg)

Trying to execute this line of code: 
await db.execute(sql`SET db.tenant_id = ${tenantId};`);
await db.execute(sql`SET db.tenant_id = ${tenantId};`);
the query that is produced by drizzle: 
Query: SET db.tenant_id = $1; -- params: ["2"]
Query: SET db.tenant_id = $1; -- params: ["2"]
and getting this error: 
PostgresError: syntax error at or near "$1"
PostgresError: syntax error at or near "$1"
No idea why, any insight would be appreciated!
5 Replies
Angelelz
Angelelz•17mo ago
Is this correct pg syntax? Are you attempting to Update a table?
z3n
z3nOP•17mo ago
Hi, thanks for the reply! Yes it is. It runs in the db just fine. Also works with sql.raw() but that would introduce a security flaw. This sets a temporary variable in the database and is later accessed using 
SELECT current_setting('db.tanant_id');
SELECT current_setting('db.tanant_id');
for row level security
Angelelz
Angelelz•17mo ago
I guess it postgres doesn't support params in that sql statement, has to be hardcoded? You could do 
await db.execute(sql`SET db.tenant_id = ${sql.raw(tenantId)};`);
await db.execute(sql`SET db.tenant_id = ${sql.raw(tenantId)};`);
That would basically bypass sql inyection countermeasures
z3n
z3nOP•17mo ago
Great, I'll try that. Thanks for your help!
James A Rosen
James A Rosen•4w ago
Following up on this old thread… Postgres allows setting a setting with the SET keyword: 
set foo.bar = 'baz';
select current_setting('foo.bar');
-- | current_setting |
-- |             baz |
set foo.bar = 'baz';
select current_setting('foo.bar');
-- | current_setting |
-- |             baz |
But you can't put SET in a prepared statement: 
PREPARE set_foo_bar (text) AS
set foo.bar to $1;
-- ERROR:  syntax error at or near "set"
PREPARE set_foo_bar (text) AS
set foo.bar to $1;
-- ERROR:  syntax error at or near "set"
Postgres also allows setting a setting with the set_config function. The third argument is whether the setting is scoped to the transaction (true) or to the session (false): 
select set_config('foo.bar', 'baz', true);
select set_config('foo.bar', 'baz', true);
Because it's a function, you can put this in a prepared statement: 
PREPARE set_foo_bar (text) AS
select set_config('foo.bar', $1, true);
PREPARE set_foo_bar (text) AS
select set_config('foo.bar', $1, true);
Because web applications tend to reuse one Postgres session for many end-users, I recommend passing true for the third argument. That means you must wrap your work in a transaction. Otherwise, the setting will disappear before your next statement!

Did you find this page helpful?