`find` with multiple `WHERE` clauses where one is invalid generates invalid SQL

[JaffParker]

Issue type:

[ ] question
[X] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[X] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[ ] latest
[ ] @next
[X] 0.2.22 (or put your version here)

Hiding first case as it's a duplicate of #2195

CASE 1:

Suppose you have an array of IDs and you want to fetch entries with those IDs. Array can be empty sometimes due to your app's logic. Code sample:

// it can contain IDs or be empty, need to be empty for this demo
const arr = []
const entries = await repo.find({ id: In(arr) })

With debug logging enabled this is the query you will see:

SELECT * FROM `table` WHERE `id` IN ();

... which will fail due to syntax error.

CASE 2:

You have a table for which users might submit a search string. That string needs to be matched against the fields amount: number and customerSummary: { name: string }. Because the Like filter is always considered a string, we start getting TS errors about wrong types, which forces us to add a bunch of //@ts-ignore like this:

const searchString = 'blah'
const entries = await repo.find({
  where: [
    {
      //@ts-ignore
      amount: Like(`%${searchString}%`),
    },
    {
      //@ts-ignore
      customerSummary: Like(`%name":"${searchString}%`),
    },
    {
      note: Like(`%${searchString}%`),
    }
  ]
})

This would produce a query:

SELECT * FROM `table` WHERE (`amount` LIKE '%searchString%') OR (`customerSummary` LIKE '%name":"searchString%') OR (`note` LIKE '%searchString%');

All good, But! If you make a typo in let's say, customerSumary, Typescript will not tell you since you had to tell it to ignore types on that line. In that case instead of TypeORM throwing an error (ideally) or at least ignoring the condition, you get a query like this:

SELECT * FROM `table` WHERE (`amount` LIKE '%searchString%') OR () OR (`note` LIKE '%searchString%');

So obviously this is a syntax error. Interstingly, it does obviously check for field validity, but doesn't bother letting me know instead just omitting it.

[imnotjames]

Suggestions on what to do in case 1?

On case 2 I think we should throw an exception when building that query - it obviously does not work.

[JaffParker]

@imnotjames I'd imagine throw an error as well. In all the cases that it happened to me, I'd prefer to know the error and where it came from instead of spending the first 5 minutes of debugging on trying to find where it is

[imnotjames]

IN clause case is a duplicate of #2195

[imnotjames]

For case 2.. oof. Ok, so - right now TypeORM is pretty flexible on allowing you to include data that's not really.. ideal. Such as invalid fields.

The right thing to do for consistency is to silently ignore the field.. but.. oof.. that hurts.

I'm going to update a few bits of this issue to make it more reflect that case 2 piece

[imnotjames]

#5603

It looks like this may have been fixed around when you opened this! I can no longer replicate this using the example you gave.

When trying to it emits the following error:

EntityColumnNotFound: No entity column "invalid" was found.

To note, the test I created follows.

---

test/github-issues/6465/entity/Test.ts:

import { Entity, PrimaryColumn, Index, Column } from "../../../../src";

@Entity()
export class Test {
    @PrimaryColumn()
    id: number;

    @Index("description_index", { fulltext: true })
    @Column()
    description: string;
}

---

test/github-issues/6465/issue-6465.ts:

import "reflect-metadata";
import { expect } from "chai";
import { Connection } from "../../../src";
import { closeTestingConnections, createTestingConnections, reloadTestingDatabases } from "../../utils/test-utils";
import { Test } from "./entity/Test";
import {EntityColumnNotFound} from "../../../src/error/EntityColumnNotFound";

describe("github issues > #6465 `find` with multiple `WHERE` clauses where one is invalid generates invalid SQL", () => {

    let connections: Connection[];
    before(async () => {
        connections = await createTestingConnections({
            entities: [Test]
        });
    });
    beforeEach(() => reloadTestingDatabases(connections));
    after(() => closeTestingConnections(connections));

    it("prevents usage of invalid columns in `find`s `where` clauses", () => Promise.all(connections.map(async (connection) => {
        const repo = await connection.getRepository(Test);

        expect(repo.find({
            where: [
                {
                    id: 1,
                },
                {
                    //@ts-ignore
                    invalid: "This is bad!"
                },
                {
                    description: "Hello World"
                }
            ]
        })).to.be.rejectedWith(EntityColumnNotFound);
    })));
});

[imnotjames]

Duplicate of #3416

[imnotjames]

Closing because case 2 is also a duplicate. If you're still seeing this problem please update in that issue with how to replicate.