Fulltext search fails when inserting string containing question mark

[jczacharia]

Describe the bug
When creating a FullTextType and inserting text with a '?' in it, it looks like pg/mikro replaces the question mark with the next column value and then ignores said column afterward, leaving a syntax error and a mismatch in the number of columns to values. Should there be a way to properly sanitize the question marks?

Example:

  @Index({ type: 'fulltext' })
  @Property({ type: FullTextType })
  searchable!: string;

  @Property()
  column1!: string;

Inserting into DB:

 searchable: 'Searchable ? text',
 column1: 'foo',

Stack trace

previous error: insert into "entity" ("searchable", "column1") values to_tsvector('simple', 'Searchable 'foo' text')
syntax error at or near "foo"
    at Parser.parseErrorMessage (...\node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (...\node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (...\node_modules\pg-protocol\src\parser.ts:103:30)
    at Socket.<anonymous> (...\node_modules\pg-protocol\src\index.ts:7:48)
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Expected behavior
I would expect to be able to input question marks (or any character) into fulltext properies.

Versions

Dependency	Version
node	16.17.0
typescript	~4.7.2
mikro-orm	^5.3.1
@mikro-orm/postgresql	^5.3.1

[B4nan]

I cant reproduce this (tried v5.4 and TS 4.8, but I dont think its related to that). Please provide complete repro.

[jczacharia]

https://github.com/jczacharia/fulltext-question-mark

Error:

J:\repos\fulltext\node_modules\@mikro-orm\postgresql\PostgreSqlExceptionConverter.js:32   
                return new core_1.SyntaxErrorException(exception);
                       ^
SyntaxErrorException: insert into "test_history" ("client_first_name", "client_last_name", "client_name_full", "id") values ('Janet?', 'Smith', to_tsvector('simple', 'Janet'MEGh7mZxMm9jK7GQ8sE9m' Smith'), NULL) returning "id" - syntax error at or near "MEGh7mZxMm9jK7GQ8sE9m"
    at PostgreSqlExceptionConverter.convertException (J:\repos\fulltext\node_modules\@mikro-orm\postgresql\PostgreSqlExceptionConverter.js:32:24)
    at PostgreSqlDriver.convertException (J:\repos\fulltext\node_modules\@mikro-orm\core\drivers\DatabaseDriver.js:187:54)
    at J:\repos\fulltext\node_modules\@mikro-orm\core\drivers\DatabaseDriver.js:191:24    
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at PostgreSqlDriver.nativeInsert (J:\repos\fulltext\node_modules\@mikro-orm\knex\AbstractSqlDriver.js:222:21)
    at ChangeSetPersister.persistNewEntity (J:\repos\fulltext\node_modules\@mikro-orm\core\unit-of-work\ChangeSetPersister.js:85:21)
    at ChangeSetPersister.executeInserts (J:\repos\fulltext\node_modules\@mikro-orm\core\unit-of-work\ChangeSetPersister.js:29:13)
    at ChangeSetPersister.runForEachSchema (J:\repos\fulltext\node_modules\@mikro-orm\core\unit-of-work\ChangeSetPersister.js:68:13)
    at UnitOfWork.commitCreateChangeSets (J:\repos\fulltext\node_modules\@mikro-orm\core\unit-of-work\UnitOfWork.js:671:9)
    at UnitOfWork.persistToDatabase (J:\repos\fulltext\node_modules\@mikro-orm\core\unit-of-work\UnitOfWork.js:623:13)

previous error: insert into "test_history" ("client_first_name", "client_last_name", "client_name_full", "id") values ('Janet?', 'Smith', to_tsvector('simple', 'Janet'MEGh7mZxMm9jK7GQ8sE9m' Smith'), NULL) returning "id" - syntax error at or near "MEGh7mZxMm9jK7GQ8sE9m" 
    at Parser.parseErrorMessage (J:\repos\fulltext\node_modules\pg-protocol\src\parser.ts:369:69)
    at Parser.handlePacket (J:\repos\fulltext\node_modules\pg-protocol\src\parser.ts:188:21)
    at Parser.parse (J:\repos\fulltext\node_modules\pg-protocol\src\parser.ts:103:30)     
    at Socket.<anonymous> (J:\repos\fulltext\node_modules\pg-protocol\src\index.ts:7:48)  
    at Socket.emit (node:events:513:28)
    at addChunk (node:internal/streams/readable:315:12)
    at readableAddChunk (node:internal/streams/readable:289:9)
    at Socket.Readable.push (node:internal/streams/readable:228:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)

Looks like it has something to do with the onUpdate or the EventSubscriber.

My use case requires maintaining history of certain entities, though I don't think I need to search the history directly but I did come across this error.

[B4nan]

Oh I see, weird I havent been able to reproduce this in the existing test. Its actually as simple as this:

import { Entity, MikroORM, PrimaryKey, Property } from '@mikro-orm/core';
import { FullTextType } from '@mikro-orm/postgresql';

@Entity()
export class Test {

  @PrimaryKey()
  id!: number;

  @Property({ type: FullTextType })
  clientNameFull!: string;

  @Property()
  foo!: string;

}

let orm: MikroORM;

beforeAll(async () => {
  orm = await MikroORM.init({
    entities: [Test],
    dbName: `mikro_orm_test_3457`,
    type: 'postgresql',
  });
  await orm.schema.refreshDatabase();
});

afterAll(() => orm.close(true));

test('load entities', async () => {
  const test = new Test();
  test.clientNameFull = 'Janet?';
  test.foo = '123';
  await orm.em.fork().persistAndFlush(test);
});

edit: ok now I get it, the problem is there only in single entity insert, the existing test I was modifying uses multi insert

[B4nan]

This should help

5095ddb