Thanks to you both for your answers and your support.
Let's take this query as an example:
Code: Select all
SELECT * FROM Songs WHERE SongTitle LIKE '%a%'
The expression
%a% may be taken from a search input. Because it's encapsulated with single quotes, entering a search expression like
%a'% breaks the query and leads to an injection:
Code: Select all
SELECT * FROM Songs WHERE SongTitle LIKE '%a'%'
So SQlite requires escaping the single quote by adding an additional single quote:
Code: Select all
SELECT * FROM Songs WHERE SongTitle LIKE '%a''%'
It's the same when using double quotes for encapsulating and contained double quotes:
Code: Select all
SELECT * FROM Songs WHERE SongTitle LIKE "%a""%"
Maybe there are other chars that need escaping.
So based on the string encapsulation the specific quote character needs to be escaped.
For easy use I've created a small tagged template that handles escaping. It's used like this:
Code: Select all
app.db.getTracklist(Sql.query`SELECT * FROM Songs WHERE SongTitle LIKE ${searchString}`, -1);
Because it's a tagged template it automatically separates template expressions which then get escaped correctly.
Inserting unescaped values works by using a special wrapper method:
Code: Select all
app.db.getTracklist(Sql.query`SELECT * FROM Songs ORDER BY ${Sql.raw(sorting)}`, -1);
It also supports arrays for things like
IN searches:
Code: Select all
app.db.getTracklist(Sql.query`SELECT * FROM Songs WHERE IDSong IN (${songIds})`, -1);
If you're interested I can send you the .ts file but please don't take it as bulletproof.
Regarding possible damage:
Yeah, I don't know what may happen in a worst case. I think a common scenario would be a failing script in case somebody uses single/double quotes in a search. Damaging a users database could be a worst case scenario, yes.
Being a web developer myself I'm quite sensitive to these things so sorry if I'm a bit too harsh about this.
Thanks to you both for your answers and your support.
Let's take this query as an example:
[code]SELECT * FROM Songs WHERE SongTitle LIKE '%a%'[/code]
The expression [i]%a%[/i] may be taken from a search input. Because it's encapsulated with single quotes, entering a search expression like [i]%a'%[/i] breaks the query and leads to an injection:
[code]SELECT * FROM Songs WHERE SongTitle LIKE '%a'%'[/code]
So SQlite requires escaping the single quote by adding an additional single quote:
[code]SELECT * FROM Songs WHERE SongTitle LIKE '%a''%'[/code]
It's the same when using double quotes for encapsulating and contained double quotes:
[code]SELECT * FROM Songs WHERE SongTitle LIKE "%a""%"[/code]
Maybe there are other chars that need escaping.
So based on the string encapsulation the specific quote character needs to be escaped.
For easy use I've created a small tagged template that handles escaping. It's used like this:
[code]app.db.getTracklist(Sql.query`SELECT * FROM Songs WHERE SongTitle LIKE ${searchString}`, -1);[/code]
Because it's a tagged template it automatically separates template expressions which then get escaped correctly.
Inserting unescaped values works by using a special wrapper method:
[code]app.db.getTracklist(Sql.query`SELECT * FROM Songs ORDER BY ${Sql.raw(sorting)}`, -1);[/code]
It also supports arrays for things like [i]IN[/i] searches:
[code]app.db.getTracklist(Sql.query`SELECT * FROM Songs WHERE IDSong IN (${songIds})`, -1);[/code]
If you're interested I can send you the .ts file but please don't take it as bulletproof.
Regarding possible damage:
Yeah, I don't know what may happen in a worst case. I think a common scenario would be a failing script in case somebody uses single/double quotes in a search. Damaging a users database could be a worst case scenario, yes.
Being a web developer myself I'm quite sensitive to these things so sorry if I'm a bit too harsh about this. :)