In express
if with log the query built by kysely
via the config:
export const db = new Kysely<DB>({ dialect, log(event) { if (event.level === "query") { console.log(event.query.sql); console.log(event.query.parameters); } } })
then we eventually get the following kind of queries to prevent sql injection:
select *, (select to_json(obj) from (select count("MessagesSession"."id") as "count" from "MessagesSession" where "MessagesSession"."channelId" = "Channel"."id" and "MessagesSession"."hostUserId" = $1 and "MessagesSession"."isDeleted" != $2 and "MessagesSession"."isDraftInstantIssue" = $3) as obj) as "draft" from "Channel" where "Channel"."projectId" = $4 and "Channel"."isDeleted" = $5 [ '77af0d8c-46aa-44c1-bb2b-730033cbf188', true, true, 'ff515aff-d53f-407c-9c11-602998b74ba8', false ]
To exeucute this in our ordinary SQL shell we can prepare
and execute
it:
prepare plan(uuid, bool, bool, uuid, bool) as select *, (select to_json(obj) from (select count("MessagesSession"."id") as "count" from "MessagesSession" where "MessagesSession"."channelId" = "Channel"."id" and "MessagesSession"."hostUserId" = $1 and "MessagesSession"."isDeleted" != $2 and "MessagesSession"."isDraftInstantIssue" = $3) as obj) as "draft" from "Channel" where "Channel"."projectId" = $4 and "Channel"."isDeleted" = $5; execute plan( '77af0d8c-46aa-44c1-bb2b-730033cbf188', true, true, 'ff515aff-d53f-407c-9c11-602998b74ba8', false )
From this we get:
Now we can investigate what's wrong with our query.