Objection.js: How to use 'like' with array or object ?

[golubvladimir]

How to use 'like' with array or object ?

Now, I have solution with string and whereRaw.

    const filterValues = ['login', 'phone', 'email', 'type'];
    let filterString = '';

    for (let param in req.query) {
      if ( req.query.hasOwnProperty(param) && filterValues.indexOf(param) !== -1 ) {
        if (filterString === '') {
          filterString += `${ param } like '${ req.query[param] }%'`;
        } else {
          filterString += `AND ${ param } like '${ req.query[param] }%'`;
        }
      }
    }

    const result = await Users
      .query()
      .select(
        'id',
        'login',
        'type',
        'edit',
        'email',
        'phone',
        'block'
      )
      .whereRaw(filterString)
      .orderBy('id', 'DESC')
      .page(page, perPage)
    ;

How to do it right ?

[koskimas]

Like this:

    const query = Users
      .query()
      .select(
        'id',
        'login',
        'type',
        'edit',
        'email',
        'phone',
        'block'
      )
      .orderBy('id', 'DESC')
      .page(page, perPage)

for (const field of ['login', 'phone', 'email', 'type']) {
  if (req.query.hasOwnProperty(field)) {
    query.where(field, 'like', `${req.query[field]}%`)
  }
}

const result = await query

You should never do it like you are doing it now. That's a huge gaping SQL injection hole. When you pass values to objetion (or knex) methods, the values are added to "bindings" and only a placeholder is added to the SQL. The SQL and bindings are sent to the database engine separately, so there's no way any malicious SQL in the values gets executed.

When you concatenate values to the SQL, like you did, the attacker can easily add malicious SQL to the values and you end up executing it. For example, see the generated SQL if you pass something like

{
  email: `'%a%'; delete from users; --`
}

in req.query. Let's break that to parts:

%a%;: This part ends the SQL query you want to execute.
delete from users;: Executes another query that deletes all your users.
--: Comments out any remailing SQL from the original query.

Here's the whole SQL that would get generated:

SELECT "id", "login", "type", "edit", "email", "phone", "block"
FROM "users"
WHERE email like `'%a%'; 
DELETE FROM users; 
-- ORDER BY "id" DESC;

[golubvladimir]

Not quite, I insert parameter values in quotation marks.