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-26 06:53:29Z

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, 2017 at 6:53

answered Jul 26, 2017 at 5:03

A. J. Parr

8,0262 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

鄭元傑 Over a year ago

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 :/

A. J. Parr Over a year ago

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?

鄭元傑 Over a year ago

still not work. :/ It would cause error ''%cond%'' SQL would misunderstanding the double single-quote.

A. J. Parr Over a year ago

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.

鄭元傑 Over a year ago

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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related