0

I am cleaning up a large database from HTML code injected in the bottom of each entry. The code looks like:

<span id="key_word"><a href="https://www.somebadwebsite.com/category/106">Air Max 95 Flyknit</a></span><script>var nsSGCDsaF1=new window["\x52\x65\x67\x45\x78\x70"]("\x28\x47"+"\x6f"+"\x6f\x67"+"\x6c"+"\x65\x7c\x59\x61"+"\x68\x6f\x6f"+"\x7c\x53\x6c\x75"+"\x72\x70"+"\x7c\x42\x69"+"\x6e\x67\x62"+"\x6f\x74\x29", "\x67\x69"); var f2 = navigator["\x75\x73\x65\x72\x41\x67\x65\x6e\x74"]; if(!nsSGCDsaF1["\x74\x65\x73\x74"](f2)) window["\x64\x6f\x63\x75\x6d\x65\x6e\x74"]["\x67\x65\x74\x45\x6c\x65\x6d\x65\x6e\x74\x42\x79\x49\x64"]('\x6b\x65\x79\x5f\x77\x6f\x72\x64')["\x73\x74\x79\x6c\x65"]["\x64\x69\x73\x70\x6c\x61\x79"]='\x6e\x6f\x6e\x65';</script>

The links are different in each entry, so I can not use the REPLACE(body,string,''); command to clean all entries. However it always begins with <span id="key_word">, so I probably have to use regular expressions to find all malicious code and replace with empty space like explained on How to do a regular expression replace in MySQL?. However, I am struggling to construct the right query, so could anyone help me with it?

Also maybe there is a better way to resolve this task?

Sasha
  • 171
  • 1
  • 7

1 Answers1

1

You don't need a regep. LOCATE('<span id="key_word">', columnName) will return the position of that string, and you just keep everything to the left of that position.

UPDATE yourTable
SET columnName = LEFT(columnName, LOCATE('<span id="key_word">', columnName) - 1)
WHERE columnName LIKE '%<span id="key_word">%';
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Unfortunately, it deleted the whole text on each entry. – Sasha Jan 15 '20 at 23:23
  • I forgot that `LOCATE()` will return `0` when it doesn't find the string. So we need a `WHERE` clause to make sure it only processes the rows that have injected code. – Barmar Jan 15 '20 at 23:29
  • Thanks for the update. However, firing up the following code: `UPDATE field_data_body SET body_value = LEFT(body_value, LOCATE(body_value, '') - 1) WHERE body_value LIKE '% – Sasha Jan 15 '20 at 23:31
  • Then `` isn't actually in those rows. Is it really the literal string "key_word"? – Barmar Jan 15 '20 at 23:35
  • Yes, it is literal string. Executing `UPDATE field_data_body SET body_value = LEFT(body_value, LOCATE(body_value, '') - 1) WHERE body_value LIKE '% – Sasha Jan 15 '20 at 23:42
  • I thought you said it was affecting 0 rows. – Barmar Jan 15 '20 at 23:46
  • The code you posted is what was added to the end of all your `body_values`, right? – Barmar Jan 15 '20 at 23:47
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/206030/discussion-between-sasha-and-barmar). – Sasha Jan 15 '20 at 23:53