How to use TypeORM raw SQL query parameter within an anonymous code block

Question

My code below, keeps getting the error -

bind message supplies 1 parameters, but prepared statement "" requires 0.

Similar parametrized raw SQL query work fine when it is not wrapped in an anonymous code block (https://stackoverflow.com/a/40853818/8252769, the "DO $$...END $$"). But I need it so I can execute the INSERT conditionally in one SQL statement.

import { Injectable } from "@nestjs/common";
import { InjectRepository } from "@nestjs/typeorm";
import { Connection, getRepository, Repository } from "typeorm";

.........

const queryRunner = this.connection.createQueryRunner();

    await queryRunner.connect();

    try {
        await queryRunner.query(
          `DO $$
          BEGIN
            IF NOT(SELECT EXISTS(SELECT id FROM "document" WHERE id = $1))
            THEN
              INSERT INTO .............
            ELSE
              RAISE EXCEPTION 'Operation is only allowed when the document no longer exist.';
            END IF;
          END $$;`,
          [
            documentId,
          ],
        ),
      );
    } catch (ex) {
      throw ex;
    } finally {
      await queryRunner.release();
    }

A DO block cannot receive parameters. Thus the reference $1 is invalid. It may be best to write an actual stored procedure. — Belayer
– Belayer, Commented Apr 28, 2022 at 23:45

tevvek · Accepted Answer · 2022-04-29 02:39:49Z

-1

I just tested passing the value to the string parameter rather than as a parameters array to the query function and it seems to work:

try {
    await queryRunner.query(
        `DO $$
        BEGIN
        IF NOT(SELECT EXISTS(SELECT id FROM "document" WHERE id = '${documentId}'))
        THEN
            INSERT INTO .............
        ELSE
            RAISE EXCEPTION 'Operation is only allowed when the document no longer exist.';
        END IF;
        END $$;`,
    ),
    );
} catch (ex) {}

Don't forget the '' around the value when you pass it as it has to be an actual string for the database.

answered Apr 29, 2022 at 2:39

tevvek

5945 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

danieldaugherty Over a year ago

This is a great way to get hacked. See: SQL injection.

talski Over a year ago

@danieldaugherty if you sanitize the input variables, it's not a problem

juztcode Over a year ago

if it's not sth related to user input it's usually okay

Collectives™ on Stack Overflow

How to use TypeORM raw SQL query parameter within an anonymous code block

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related