鄭元傑 鄭元傑 - 3 years ago 185
Node.js Question

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

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 Source

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.

cond = '%'+mysql.escape(cond)+'%';
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])
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download