insert variable into sqlite query-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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 _.