I am trying to make a search bar which works with multiple words, but I am worried about SQL injection.
I am using node express with the npm mssql package.
Here's the code which gets the criteria, generates the SQL and runs it:
router
.get('/search/:criteria', function (req, res) {
var criteria = req.params.criteria;
var words = criteria.split(" ");
var x = ""
words.map(word => x += `name like '%${word}%' and `);
x = x.substring(0, x.length - 5); // Remove trailing 'and'
var query = `SELECT * FROM table WHERE ${x}`
new sql.ConnectionPool(db).connect().then(pool => {
return pool.request().query(query)
}).then(result => {
})
});
A search for something to search
would result in this query:
SELECT * FROM table
WHERE
name like '%something%'
and name like '%to%'
and name like '%search%'
I tried some SQL injections myself, but none of them seem to work.
Note: I am aware that we should always use inputs for this. It works fine for one word, but I don't know how to use inputs for many words. Ex:
new sql.ConnectionPool(db).connect().then(pool => {
return pool.request()
.input('input', '%'+criteria+'%')
.query(query)
})
The answer is: It's not safe. Your code does exactly nothing to make it safe, either. Don't build SQL by concatenating/interpolating user-supplied data into the statement.
In addition, you don't do any escaping for LIKE
itself, either, so that is just as unclean.
If you need dynamic SQL, build a prepared SQL statement with the expected number of placeholders and then bind user-supplied values to those placeholders.
router.get('/search/:criteria', (req, res) => {
const ps = new sql.PreparedStatement();
const sqlConditions = [];
const escapedValues = {};
// set up escaped values, safe SQL bits, PS parameters
req.params.criteria.split(" ").forEach((v, i) => {
const paramName = 'val' + i;
escapedValues[paramName] = v.replace(/[\\%_]/g, '\\$&');
sqlConditions.push(`name LIKE '%' + @${paramName} + '%' ESCAPE '\'`);
ps.input(paramName, sql.VarChar);
});
// build safe SQL string, prepare statement
const sql = 'SELECT * FROM table WHERE ' + sqlConditions.join(' AND ');
ps.prepare(sql);
// connect, execute, return
ps.execute(escapedValues).then(result => {
res(result)
});
});
(Disclaimer: code is untested, as I have no SQL Server available right now, but you get the idea.)