P
Prisma•2d ago
Bluestopher

Help with queryRawUnsafe

Hey all, I have a query that previously worked that started to fail. Based off documentation, this is expected since the column names are dynamically generated when using query raw. Can I get help on converting it to a queryRawUnsafe? Here is a sampe query of what I am trying to convert: 
import { Prisma } from '@prisma/client"

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const sqlColumns = Prisma.sql`${Prisma.join(
   columns.map((col)) => Prisma.raw(col)),
   ', '
)};

const sqlValues = Prisma.sql`${Prisma.join(
   values.map((val) => Prisma.sql`${val}`),
   ', '
)};

const result = await client.$queryRaw`
   INSERT INTO "Table1" (
      ${ sqlColumns }
   )
   SELECT
      ${ sqlValues }
   FROM "Table2"
   WHERE "id" = ${id}
   RETURNING *
`;
import { Prisma } from '@prisma/client"

const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const sqlColumns = Prisma.sql`${Prisma.join(
   columns.map((col)) => Prisma.raw(col)),
   ', '
)};

const sqlValues = Prisma.sql`${Prisma.join(
   values.map((val) => Prisma.sql`${val}`),
   ', '
)};

const result = await client.$queryRaw`
   INSERT INTO "Table1" (
      ${ sqlColumns }
   )
   SELECT
      ${ sqlValues }
   FROM "Table2"
   WHERE "id" = ${id}
   RETURNING *
`;
I have tried following this implementation, but ran into unexpected trailing spaces error: 
const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = '${id}'
   RETURNING *
`);
const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = '${id}'
   RETURNING *
`);
Should I add a validation steps the trims each value or do we have a better approach?
1 Reply
Prisma AI Help
Prisma AI Help•2d ago
You selected the bot's instantaneous insights. Let me compile your answer right away! When using $queryRawUnsafe, you need to be careful about SQL injection risks. Your second approach is on the right track, but there's a safer way to handle parameterized queries with $queryRawUnsafe. The issue with your implementation is that you're directly interpolating values into the SQL string, which can lead to SQL injection vulnerabilities. Instead, you should use parameterized queries: 
const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = $1
   RETURNING *
`, id);
const id = "test-id";
const columns = ['column1', 'column2'].join(', ');
const values = ['val1', 'val2'].join(', ');

const result = await client.$queryRawUnsafe(`
   INSERT INTO "Table1" (${columns})
   SELECT ${values}
   FROM "Table2"
   WHERE "id" = $1
   RETURNING *
`, id);
For PostgreSQL, parameters are represented by $1, $2, etc., and you pass the actual values as additional arguments to $queryRawUnsafe Raw queries with relational databases. If you're dealing with dynamic table or column names, you can't use parameters for those (SQL doesn't support parameterized identifiers), but you can validate them before using them in your query. For the "unexpected trailing spaces error," make sure your column and value strings are properly trimmed: 
const columns = ['column1', 'column2'].map(col => col.trim()).join(', ');
const values = ['val1', 'val2'].map(val => val.trim()).join(', ');
const columns = ['column1', 'column2'].map(col => col.trim()).join(', ');
const values = ['val1', 'val2'].map(val => val.trim()).join(', ');
Remember that $queryRawUnsafe should be used with caution as it increases the risk of SQL injection if not properly handled SQL injection prevention. GitHub discussions also suggest using Prisma.raw() for safer dynamic table/column names when possible GitHub Issue: queryRaw doesn't support dynamic tables names.

Did you find this page helpful?