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!

4 Replies

Angelelz•15mo ago

Is this correct pg syntax? Are you attempting to Update a table?

z3nOP•15mo 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•15mo 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

z3nOP•15mo ago

Great, I'll try that. Thanks for your help!

Gaming

Programming

Getting syntax error when using the sql operator (pg)