-
Notifications
You must be signed in to change notification settings - Fork 0
Open
Labels
bugSomething isn't workingSomething isn't working
Description
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 ✅
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}%`);
Metadata
Metadata
Assignees
Labels
bugSomething isn't workingSomething isn't working
Type
Projects
Milestone
Relationships
Development
Select code repository
Activity