Drizzle Team•16mo ago

Raw sql`` quoting issue

Hi, I doing a work around until Drizzle supports generated columns. I have a manual script that adds the generated column + its index. I'm dynamically building the sql query since there's no typings yet.

const column = 'col-name'
sql`${column} < ${'10'}`

const column = 'col-name'
sql`${column} < ${'10'}`

The syntax above returns no data or sometimes all the data b/c it generates: 'col-name' < '10', which doesn't match b/c of the quote around the column name. If I hardcoded the query to:

sql`col-name < '10'`

sql`col-name < '10'`

Then it returns the correct results. Am I using sql incorrectly? Thanks! NOTE: I'm doing ${'10'} to test the ${} syntax which seems to be working fine. It's only an issue w/ the column.

17 Replies

AlcaponeYou•16mo ago

I got around this by using

new SQL([new StringChunk(`${someColumn} < 10`)])

new SQL([new StringChunk(`${someColumn} < 10`)])

Luxaritas•16mo ago

What you're seeing here is expected. SQL does not "understand" left side vs right side of expressions. Whenever you interpolate, it always escapes the value you put in as if it's a value Typically you want to refer to the column name via the schema you created Eg, if you have a users table with a age column,

sql`${user.age} < ${'10'}

sql`${user.age} < ${'10'}

age in this case won't be a raw string, it'll be a data type the sanitizer knows it can inject "raw"

bloberenober•16mo ago

asuming someColumn is a string, you can do either sql.raw(`'${someColumn}'`) or name(someColumn) (2nd one is preferred)

AlcaponeYou•16mo ago

Thanks Luxaritas, I'm hacking about w/ generated columns so there's no typings yet. Cool thanks Dan, I'll give that a shot.

bloberenober•16mo ago

So the resulting expression might look like this:

const someColumn = 'col-name';
const value = 10;
sql`${name(someColumn)} < ${value}`;

const someColumn = 'col-name';
const value = 10;
sql`${name(someColumn)} < ${value}`;

AlcaponeYou•16mo ago

sql`${name(someColumn)} < 10`

sql`${name(someColumn)} < 10`

[{"queryChunks":[{"value":[""]},{"value":"col-name"},{"value":[" "]},"<",{"value":[" "]},"10",{"value":[""]}],"decoder":{},"shouldInlineParams":false}]

[{"queryChunks":[{"value":[""]},{"value":"col-name"},{"value":[" "]},"<",{"value":[" "]},"10",{"value":[""]}],"decoder":{},"shouldInlineParams":false}]

When I log the sql, it looks like there's empty chunks. It throws a syntax error near "<"

bloberenober•16mo ago

can you enable query logging and post the resulting query that's being run?

AlcaponeYou•16mo ago

Query: select `id`, `author`, <more columns> from `test` where (`col-name` ? ?) -- params: ["<", "10"]`

Query: select `id`, `author`, <more columns> from `test` where (`col-name` ? ?) -- params: ["<", "10"]`

bloberenober•16mo ago

that's really weird

AlcaponeYou•16mo ago

the query looks like valid sql the queryChunks is odd it looks like it's incorrectly parsing the chunks

bloberenober•16mo ago

for some reason, it puts < to params list

AlcaponeYou•16mo ago

oh, that's on purpose my comparator is from a variable

bloberenober•16mo ago

but you posted this query:

sql`${name(someColumn)} < 10`

sql`${name(someColumn)} < 10`

AlcaponeYou•16mo ago

sorry that's right. I'm mixing examples w/ my project

bloberenober•16mo ago

if you need the comparator to be a variable, wrap it in sql.raw() so that sql knows it's not a parameter but a SQL piece

AlcaponeYou•16mo ago

perfect, that fixed it. Thanks a lot!

bloberenober•16mo ago

I'm craving proper sql documentation 😖 it's really flexible, and poorly documented right now

Gaming

Programming

Raw sql`` quoting issue