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 ?

A. J. Parr · Accepted Answer · 2017-07-26T06:53:29.633

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])

edited Jul 26 '17 at 06:53

answered Jul 26 '17 at 05:03

A. J. Parr

7,731
2
31
46

so brilliant ! But I have tried your methods, it still wrong. I tried not using ? and insert the string directly, `"....LIKE " + cond + "..." ` works. A little bit weird :/ – 鄭元傑 Jul 26 '17 at 06:04
Hey, I took a better look at mysqljs and your code. I've updated my answer a bit, could you see if this works for you? – A. J. Parr Jul 26 '17 at 06:25
still not work. :/ It would cause error `''%cond%''` SQL would misunderstanding the double single-quote. – 鄭元傑 Jul 26 '17 at 06:33
I've revised my answer, I've escaped more variables from the query because its simpler than escaping characters. I've also made the `cond` variable reformat itself so it should work with the parameterisation. This should be close to a working solution I think. – A. J. Parr Jul 26 '17 at 06:55
I have logged out `cond` variables at first and it doesn't contain any quote like `abc`. So I don't think it need `new_cond` to reorganize. Then `mysql.escape("%" + cond + "%")` would turn to `'%abc%'` with single-quotes. I have try to add ESCAPE in SQL but not works. I decided to use directly string instead of ? variables because it is still safe if I do sql escape in all params. Thanks for your patient. – 鄭元傑 Jul 26 '17 at 07:12
I think my problem would be escape twice! in the `con.query(sql, params)` quote > `This looks similar to prepared statements in MySQL, however it really just uses the same connection.escape() method internally.` That's why string would be weird `'\'%abc%\''` – 鄭元傑 Jul 26 '17 at 07:21

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

1 Answers1