template literal style ambiguity

[danielebriggi]

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 ✅

:warning: 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}%`);