how to escape string but not affect LIKE result in nodejs mysql js

Question

Recently I am using mysqljs in nodejs web app.
I would like to escape all my parameters in SQL to prevent injection attack.
However in the LIKE schema, the SQL would be affected by the escape string sign `

Here is my query
SELECT event.name, host.name, Guest.name
        FROM Event as event
        LEFT JOIN Host on Host._id = event.host_id
        LEFT JOIN Event_Guest on Event_Guest.event_id = Event._id
        LEFT JOIN Guest on Event_Guest.guest_id = Guest._id
        WHERE host._id = event.host_id AND event.status IN ('on', 'off') AND 
        ( event.name LIKE "%?%" escape "'" OR host.name LIKE "%?%" OR guest.name LIKE "%?%")
        LIMIT ?, ?;
    `, [cond, cond, cond, skip, limit])

If I apply mysql.escape(cond)，the SQL would be LIKE "%'cond'%".
single quote would affect the result.

How can I do escaping params and keep the origin SQL ?

Answer 1

You could add the % to the start and end of the string instead of in the SQL, you may want to escape the original string too. Also, if you take a look at https://github.com/mysqljs/mysql#escaping-query-values you may notice you don't need to wrap your values in double quotes (").

Presuming we are trying to achieve an SQL query like this:

SELECT event.name, host.name, Guest.name
FROM Event as event
LEFT JOIN Host on Host._id = event.host_id
LEFT JOIN Event_Guest on Event_Guest.event_id = event._id
LEFT JOIN Guest on Event_Guest.guest_id = Guest._id
WHERE host._id = event.host_id
AND event.status IN ('on', 'off')
AND (event.name LIKE '%search%' escape "'" OR host.name LIKE '%search%', OR guest.name LIKE '%search')
LIMIT 10, 0;

This update code sample may work for you:

new_cond = cond.slice(0, 1)+'%'+s.slice(1, cond.length-1)+'%'+cond.slice(cond.length-1);
cond = mysql.escape(new_cond);  # Should look like '%term%'
status_in = ['on', 'off'];
escape_char = "'";
connection.query('SELECT event.name, host.name, Guest.name
    FROM Event as event
    LEFT JOIN Host on Host._id = event.host_id
    LEFT JOIN Event_Guest on Event_Guest.event_id = Event._id
    LEFT JOIN Guest on Event_Guest.guest_id = Guest._id
    WHERE host._id = event.host_id 
    AND event.status IN (?)
    AND ( event.name LIKE ? escape ?
          OR host.name LIKE ? 
          OR guest.name LIKE ?
    ) LIMIT ?, ?;', [status_in, cond, escape_char, cond, cond, skip, limit])