How to put literal numbers into a query dynamically?

[fansek]

In my project, I generate a group by clause of a query dynamically from a specified field list.
In PostgreSQL, it is possible to refer to an output column in a group by clause by its ordinal number, such as in the following example:

select t.c, count(*)
from t
group by 1

Currently, I am using slonik's raw tag to place a literal number into a query based on which fields need to be put into a group by clause, such as in the following snippet:

const indices = projection
  .flatMap((spec, index) => (spec.aggregated ? [] : [index + 1]))
  .map((index) => raw(String(index)));
const optionalGroupByClause = indices.length === 0
  ? sql.fragment``
  : sql.fragment`group by ${sql.join(indices, sql.fragment`, `)}`;

Is it possible to do it without the use of raw tag?
I thought, that literalValue may help, but it accepts only strings. Is there a reason not to allow other primitive values to be used with that function? Thank you.

[firxworx]

My understanding is that if you know you have a number then it is perfectly safe to interpolate into an SQL query as-is. That should simplify things for you!

The whole point of template tags thing is to protect queries from SQL injection.

You would only have to be concerned if the "number" was coming from user input (such as a form field) in which case you'd want to parse it with zod/valibot/etc or manually using Number.parseInt() to ensure that you have a value that is indeed type of number.

In your case you have the index value returned by a built-in map() function so you are guaranteed it is a number (unless the map functions in your runtime have been monkey patched by a malicious actor :P).

const myNum = 1 // type `number`
const slonikQuery = sql`SELECT ${myNum}` // this is fine

In case it helps I have a helper function getQueryValue() that accepts common types like string, number, boolean, null, undefined, Date and checks it with typeof / instanceof and returns the appropriate ...Token, SqlFragment or primitive value. It throws if it gets an input value it wasn't designed to handle.

If you had one of these of your own you could simply use it in all cases like this with confidence.
I use it for building column and value lists of INSERT and UPDATE queries.

For strings it returns an sql fragment with the value interpolated inside (since at one point the docs suggested against using sql.literalValue() for interpolating strings but I can't find that any more... and I don't see why it wouldn't work).

For null or undefined it returns null. If its an instanceof Date it returns sql.date(v) and if its any of number or boolean it simply returns that value as-is. Can't inject malicious SQL into a value that's a verified number or boolean value.

[fansek]

Never mind, you can use sql.array. So my another example is not a good one. I will think about it again and if I come with something better, I will post it here. Thanks again.

[firxworx]

Hey @fansek I wasn't even referring to sql.raw() I meant if you have a straight up literal value you can drop it into a query as-is using typical TypeScript string interpolation. Also, I agree sql.array() is very useful for constructing queries!

For example long as you know you have a number you can safely just plop it into a query defined using the sql tagged template provided by slonik. If you have a string and it isn't from user input (perhaps it is known to be a UUID for example) then you can similarly plop it in.

Check out the example in the README: https://github.com/gajus/slonik?tab=readme-ov-file#tagged-template-literals

As I understand it, slonik's sql itself will take care of constructing the query for underlying pg with the $x placeholders and providing the values array.

[fansek]

Hi @firxworx,

I apologize for not responding sooner. Here is my real-life example I mentioned when I started this conversation.

If sql tag is used like this

const q = sql.unsafe`select c, count(*) from (values (1), (1), (2)) t(c) group by 1`;

q will evaluate to query

{
  sql: 'select c, count(*) from (values (1), (1), (2)) t(c) group by 1',
  values: [],
}

which can be executed

await pool.query(q)

and a result will be given back.

{
  command: 'SELECT',
  fields: [ { dataTypeId: 23, name: 'c' }, { dataTypeId: 20, name: 'count' } ],
  notices: [],
  rowCount: 2,
  rows: [ { c: 1, count: 2n }, { c: 2, count: 1n } ],
  type: 'QueryResult'
}

However, if q is assigned

sql.unsafe`select c, count(*) from (values (1), (1), (2)) t(c) group by ${1}`

it will evaluate to something like this

{
  sql: 'select c, count(*) from (values (1), (1), (2)) t(c) group by $slonik_1',
  values: [ 1 ]
}

which when executed will result in error.

error: column "t.c" must appear in the GROUP BY clause or be used in an aggregate function

Postgres is probably trying to compile the query before parameters are used for query execution and while it is doing so, it tries to validate group by clause which can't be accomplished if it is unknown at that time.

So you may say that I should simply use the first method.
However, if I need to parameterize the group by clause, I can't use that method.
I was thinking that I could use literalValue for this purpose, but it didn't work.

sql.unsafe`select c from t where c = ${sql.literalValue('abc')}`

will give result of

{
  sql: `select c from t where c = 'abc'`,
  values: []
}

while

sql.unsafe`select c from t where c = ${sql.literalValue(1)}`

will fail with an error.

Uncaught TypeError: subject is not iterable

Therefore, the only option I see is to use raw function to interpolate dynamically generated number into the query without making it a parameter of that query at the same time. It probably isn't a bad option in the case I described above, because I know how the number is generated before it is inserted into the query, which means that I am able to verify that it is safe. However, I don't see any reason why sql.literalValue doesn't support values with type number. Do you see any reason for that?

[firxworx]

Hmmm @fansek I think there might be a few different things going on with your examples.

The first two queries are very different, refer to t(c) in the first one and t in the second one. For the second one postgres thinks you are defining a t.c but it sees the GROUP BY and doesn't recognize it as being part of the group or part of an aggregate so it doesn't know what to do with it.

Step back and consider that the error is a pretty common one when you SELECT a column but then don't specify how the column with that name/alias/identifier should either be grouped or aggregated.

Maybe that helps?

Maybe try writing your aliases in full form including the SQL optional AS <alias> syntax to really emphasize where you are defining them?

---

For your last example if you know that the variable will be a number you can interpolate it directly into into the query. It doesn't have to be a raw or unsafe -- you can still use sql.type(...) to type the result.

You can make sure you're absolutely safe (in case you have a concern that you might circumvent slonik's safety at runtime) by using the Number() constructor or even a helper function that does a run-time type check and throws if the value is anything else.

You can see what literalValue() actually does by looking at the code:

slonik/packages/sql-tag/src/factories/createSqlTag.ts

Line 175 in d034e2f

literalValue: (value) => {

and in turn it calls escaleLiteralValue():

slonik/packages/sql-tag/src/utilities/escapeLiteralValue.ts

Line 4 in d034e2f

export const escapeLiteralValue = (subject: string): string => {

You can get an idea about what it is designed to do and how it is expected to work by looking at the tests:

https://github.com/gajus/slonik/blob/d034e2fcdb827b3cf0efdc53c04d8bbc6d4fbced/packages/sql-tag/src/utilities/escapeLiteralValue.test.ts

---

I wonder if your examples are too abstract and we're getting caught up in the abstractions. Like what is a real relevant schema (or a functional equivalent of it if you feel you need to obfuscate it) and what are you actually trying to do?

---

FYI I have seen some pretty inspiring query-building by @ardsh in https://github.com/ardsh/slonik-trpc and https://github.com/ardsh/sql-api-engine.

I have no idea who the guy is but he's a slonik wizard and you can find examples of GROUP BY used in constructed queries in his repos.

[fansek]

Hi @firxworx,

1. Thanks for pointing out that there is an issue in my second example. However, it is simply a copy-paste issue. I have corrected it in place (t -> t(c)). My point remains valid.
2. The interpolation, you are talking about does not work, as I demonstrated above.
3. I know how literalValue works under the hood. However, node-postgres and similar libraries are not trying to provide user with a safe interface. I don't care, if slonik would introduce literalNumber or change existing literalValue or whatever. It just makes sense to me to have that in the library which tries to provide user with a safe interface. That's all.

I am sorry that I couldn't sufficiently explain the issue to you. Thanks for your time.

[fansek]

I am closing this discussion, because I am obviously the only one bothered by the issue I described above. I've made up my mind and I think that using raw function is OK in an example like the one I was writing about.