1

I have a Todo application with a database for the todos. It contains a column done which will be set to false or true. Now I want to filter the data by all or unfinished. So either done is irrelevant or it has to be false.

I am using SQLite3 in a TypeScript application. Currently I do this by using string templates but I'd prefer an SQL-based solution.

db.prepare(`
  select 
    rowid as id, 
    title, 
    description
  from todo
    ${selectAll ? '' : 'where done = 0'}
    limit ?
    offset ?
`).all(limit, offset);

My idea was to use the CASE clause but it seems not to work around the WHERE clause.

Is there any better solution?

selectAll is a TypeScript variable that is set depending on the query parameters of the app.

Output when selectAll is false

id | title             | description          | done
1  | Clean the kitchen | as the title says... | 1
2  | Do the shopping   | potatoes, tomatoes   | 0
3  | Program stuff     | Todo app             | 1

Output when selectAll is true

id | title             | description          | done
2  | Do the shopping   | potatoes, tomatoes   | 0
  • 1
    Can you edit to add the output you want ? – Daniel E. Dec 28 '18 at 08:55
  • @DanielE. sure. One moment. –  Dec 28 '18 at 08:58
  • 1
    Why "better"? What is the problem with using different SQL for different queries? (Please note that you will have even more queries when you modify the app later.) – CL. Dec 28 '18 at 09:11
  • @CL. That is true, yes. With better I meant pure sql based solutions for that, to prevent any attacks. I don't think that there's a way to exploit my conditional statement above, but when I to modify it later on and I continue to use conditional statements for that, it may affect the app's security. –  Dec 28 '18 at 09:20
  • 1
    You need parameters (`?`) for any values whose textual representation is controlled by the user, but only for those. – CL. Dec 28 '18 at 11:07

1 Answers1

1

You can use boolean logic:

where ? = '' or done = 0
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786