insert variable into sqlite query

Question

I'm trying to obtain rows from 3 different tables if the column 'description' in table 1 contain certain substring.

this code is working correctly and bring me the desired results:

sql = "SELECT * FROM NonMandatoryObject 
 JOIN Object ON Object.M_id = NonMandatoryObject.NM_id 
JOIN Pictures ON Pictures.PICS_id = NonMandatoryObject.NM_id 
WHERE  description like '%TheSubStringIWantToSearch%'   "

db.all(sql,   (err, rows) => {
        if (err) console.log('failure')
        else {
            res.send(rows)
            console.log('success')
        }
    })

the problem is that the format of the program is to contain variables in distinct array and when I put '?' instead of variable name and provide this name the search crashes.

sql = "SELECT * FROM NonMandatoryObject 
 JOIN Object ON Object.M_id = NonMandatoryObject.NM_id 
JOIN Pictures ON Pictures.PICS_id = NonMandatoryObject.NM_id 
WHERE  description like '%?%'   "

arr = ['TheSubStringIWantToSearch']

db.all(sql, arr, (err, rows) => {
        if (err) console.log('failure')
        else {
            res.send(rows)
            console.log('success')
        }
    })

what is the proper way to insert the variable name into the sql query?

Thanks.

Peter Thoeny · Answer 1 · 2023-01-03T01:02:24.010

You can create a SQL template that contains a token like %LIKE% (or multiple if needed). Then you can create a SQL based on the template using a .replace() to replace the token with your search string:

const sqlTemplate = `SELECT * FROM NonMandatoryObject 
 JOIN Object ON Object.M_id = NonMandatoryObject.NM_id 
JOIN Pictures ON Pictures.PICS_id = NonMandatoryObject.NM_id 
WHERE  description like '%LIKE%'   `

const arr = ['TheSubStringIWantToSearch'];

let likeValue = '%'
  + arr[0]
    .replace(/['"\x00-\x1f]/g, '') // remove dangerous chars
    .replace(/([%_])/g, '!$1')     // escape % and _
  + '%';
let sql = sqlTemplate.replace(/%LIKE%/, likeValue);
db.all(sql,   (err, rows) => {
        if (err) console.log('failure')
        else {
            res.send(rows)
            console.log('success')
        }
    })

Note that the likeValue has special SQL chars removed (to guard against SQL injection exploits), and escapes % and _.

Thomas Finlay · Accepted Answer · 2023-01-02T22:36:53.437

I think you might find this answer interesting (it's in python, but a similar idea): https://stackoverflow.com/a/3105370/7045733

My understanding is that for the ? to be treated as a placeholder, it needs to be on its own (not inside quotes), so the '%' symbols need to be added to the string before it is passed to SQLite. (e.g. instead of passing 'TheSubStringIWantToSearch' from your code, you'd need to pass '%TheSubStringIWantToSearch%').

Note that if 'TheSubStringIWantToSearch' could contain any of the special symbols used by a LIKE comparison (it looks like '%' and '_' are the only special symbols used in LIKE expressions in SQLite: https://www.sqlite.org/lang_expr.html#like), then you will need to use escaping. See this paragraph from the documentation for more details. If the substring you want to search is going to come from users, I would highly recommend implementing escaping, since you never know what they are going to search!

For escaping, you might implement something like this:

sql = "SELECT * FROM NonMandatoryObject 
 JOIN Object ON Object.M_id = NonMandatoryObject.NM_id 
JOIN Pictures ON Pictures.PICS_id = NonMandatoryObject.NM_id 
WHERE  description like ? escape '!'"

arr = ['%' + searchSubstring.replace(/_/g, '!_').replace(/%/g, '!%') + '%']

db.all(sql, arr, (err, rows) => {
        if (err) console.log('failure')
        else {
            res.send(rows)
            console.log('success')
        }
    })

Regarding Peter Thoeny's answer:

I would recommend avoiding using .replace() to format data into the sql data string itself, since that can open you up to SQL injection which can be dangerous (especially on user-generated content). You can read more about SQL injection here.

@DimitriyGlefa no problem! If this answered your question, I’d appreciate it if you could mark it as accepted. Otherwise, I’d be happy to amend my answer to cover anything I’ve missed :) — Thomas Finlay, Jan 03 '23 at 01:22

insert variable into sqlite query

2 Answers2