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