Skip to content

template literal style ambiguity #186

@danielebriggi

Description

@danielebriggi
Member

When using the template-literal-style:

const result = await connection.sql`
    SELECT * FROM artists
    WHERE name LIKE "%${filter}%"
    LIMIT ${limit}
`;

there is ambiguity about the expectation. In this case 3, I'd expect the query to work but the prepared statement requires the entire string %myvalue% as value.
In the case above, the query is converted into

SELECT * FROM artists
WHERE name LIKE "%?%"
LIMIT ?

with parameters ["myvalue", 10].
SQLite instead requires parameters to be ["%myvalue%, 10].

This template literal syntax should be deprecated.
The correct syntax to properly escape parameters without ambiguity is (case 5 below):

const result = await connection.sql(`
    SELECT * FROM artists
    WHERE name LIKE ?
    LIMIT ?
`, `%${filter}%`, 10);

Tested cases

Case 1 ✅

⚠️ parameters are not escaped

const result = await connection.sql(`
    SELECT * FROM artists
    WHERE name LIKE "%${filter}%"
    LIMIT ${limit}
`);

Case 2 ✅

filter = `%${filter}%`
const result = await connection.sql`
    SELECT * FROM artists
    WHERE name LIKE ${filter}
    LIMIT ${limit}
`;

Case 3 ❌

const result = await connection.sql`
    SELECT * FROM artists
    WHERE name LIKE "%${filter}%"
    LIMIT ${limit}
`;

Case 4 ✅

const result = await connection.sql(`
    SELECT * FROM artists
    WHERE name LIKE ?
    LIMIT ${limit}
`, `%${filter}%`);

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

    Development

    No branches or pull requests

      Participants

      @danielebriggi

      Issue actions

        template literal style ambiguity · Issue #186 · sqlitecloud/sqlitecloud-js