2

Edit 1: I am trying to fetch using this below query but problem is that the end div is not taken the occurrence after the start Point i.e 

select ID, post_title, substr(post_content,instr(post_content,'<div style="position:absolute') + 0, instr(post_content,'</div>')) as temp from wp_posts where post_content LIKE '%mySpamFilter Text%' LIMIT 10

One of my Blog has been attacked by spammers, I have around 100+ posts which have the unwanted links or messages which was fused by spammers. I want to know how can I replace the string between two strings. 

Start string = '<div style="position:absolute;'
End String = '</div>'  (This End div should be the first end div after the above Start string)

I tried all the options which is available on Stack overflow but no luck so far. Please advice whats the best approach to go ahead. below is content of post_content column from one of the post from wp_posts table You can see after the word Paneer there is a lot of spam between a div class. I want to get rid of this from my database. I don't how I became victim of this attack but for now I just want to delete these.

 <p>Hey Foodies,</p>
<p>Biryani, the pride of Hyderabad, is definitely one dish which everyone knows to cook in their very own ways and styles. Biryani is one such thing which totally distinguishes itself with the plain rice we eat daily and the regular pulao. Biryani is the BIRYANI. So, lets look into the detailed procedure of the much loved Hyderabadi delicacy, the vegetarian version. Oh yes, this is the Hyderabadi Dum Veg Biryani!</p>
<p>This recipe would take 380-400 grams of uncooked Basmati rice which would serve 6.</p>
<div class="easyrecipe">
<link itemprop="image" href="http://mywebsite.in/wp-content/uploads/2013/09/Biryani-300x225.jpg">
<div class="item ERName">Hyderabadi Veg Dum Biryani</div>
<div class="ERClear"></div>
<div class="ERHead"><span class="xlate">Recipe Type</span>: <span class="type">Main Course</span></div>
<div class="ERHead">Cuisine: <span class="cuisine">Indian</span></div>
<div class="ERHead">Prep time: <time itemprop="prepTime" datetime="PT30M">30 mins</time></div>
<div class="ERHead">Cook time: <time itemprop="cookTime" datetime="PT1H15M">1 hour 15 mins</time></div>
<div class="ERHead">Total time: <time itemprop="totalTime" datetime="PT1H45M">1 hour 45 mins</time></div>
<div class="ERHead">Serves: <span class="yield">6</span></div>
<div class="ERIngredients">
<div class="ERIngredientsHeader">Ingredients</div>
<ul class="ingredients">
<li class="ingredient">For Vegetables: Carrot - 1 medium</li>
<li class="ingredient">Potatoes - 1 medium</li>
<li class="ingredient">Paneer ***<div style="position:absolute; left:-3905px; top:-3984px;">Gloves fast it <a href="http://www.goprorestoration.com/natural-viagra-foods">natural viagra foods</a> recently! Like as <a href="http://www.backrentals.com/shap/daily-cialis.html">daily cialis</a> the purchase 1 pad <a href="http://www.vermontvocals.org/buy-online-cialis.php">generic viagra cheap</a> and matte easy <a href="http://www.teddyromano.com/blue-pills/">blue pills</a> me many. Content but <a href="http://augustasapartments.com/qhio/10-mg-cialis">10 mg cialis</a> the as the helps Amazon <a href="http://www.mordellgardens.com/saha/cheap-viagra-canada.html">http://www.mordellgardens.com/saha/cheap-viagra-canada.html</a> . And baths etc would. I've <a href="http://www.teddyromano.com/brand-cialis/">brand cialis</a> Appears dollars hands still <a href="http://www.backrentals.com/shap/generic-cialis-europe.html">shop</a> October a smell <a href="http://www.hilobereans.com/buy-real-viagra/">http://www.hilobereans.com/buy-real-viagra/</a> in have. Of better <a href="http://www.creativetours-morocco.com/fers/herbal-viagra-australia.html">cheap generic cialis</a> it smelled to and <a rel="nofollow" href="http://www.goprorestoration.com/best-price-viagra">best price viagra</a> t customer anything been <a href="http://www.mordellgardens.com/saha/viagra-online-reviews.html">viagra online reviews</a> 5 supply time <a href="http://www.hilobereans.com/viagra-canada-pharmacy/">"here"</a> the product what <a href="http://www.vermontvocals.org/ed-supplements.php">buy levitra</a> biological by cream!</div>***  - 1/2 cup</li>
<li class="ingredient">Cauliflower - 1/2 cup</li>
<li class="ingredient">Green peas - 1 tbsp.</li>
<li class="ingredient">Yogurt - 1/2 cup</li>
<li class="ingredient">Mint - 1 tbsp.</li>
<li class="ingredient">Coriander - 1 tbsp.</li>
<li class="ingredient">Ginger-Garlic paste - 1 tsp.</li>
<li class="ingredient">Green chili - 4 to 5</li>
<li class="ingredient">Oil - 2 tbsp</li>
<li class="ingredient">Salt - As per taste</li>
<li class="ingredient">Biryani Masala - 1 and 1/2 tbsp.</li>
<li class="ingredient">Red Chili Powder - 1/2 tsp.</li>
<li class="ingredient">Garam Masala Powder - 1/4 tsp.</li>
<li class="ingredient">Whole Garam Masala - Custom or readymade</li>
<li class="ingredient">For Rice: Basmati Rice(soaked for 1 hour) - 380 gms</li>
<li class="ingredient">Water - 6-8 cups</li>
<li class="ingredient">Oil - 1-2 Tbsp.</li>
<li class="ingredient">Whole Garam masala - Custom or readymade</li>
<li class="ingredient">Salt - 3 Tsp.</li>
<li class="ingredient">For Dum(Bringing together): Yogurt - 1/2 cup</li>
<li class="ingredient">Saffron strands - dipped in milk, a few</li>
<li class="ingredient">Chopped Nuts - As desired</li>
<li class="ingredient">Mint and Coriander - two handfuls</li>
<li class="ingredient">Fried onions - two handfuls</li>
<li class="ingredient">Green chili - 4-5 slits</li>
<li class="ingredient">Water - 1/4 cup</li>
</ul>
</div>
<div class="ERInstructions">
<div class="ERInstructionsHeader">Instructions</div>
<div class="instructions">
<ol>
<li class="instruction">For Veggies: In a kadhai add oil. Now add whole garam masala, ginger-garlic paste. Saute for a minute. Now add carrot, potatoes, cauliflower, peas and salt. Saute until they are lightly cooked.</li>
<li class="instruction">After the veggies are a bit cooked, add in biryani masala, red chili powder, garam masala powder and gradually add yogurt stirring well to prevent curdling of curd.</li>
<li class="instruction">Now add mint, coriander, paneer. Cook until the moisture gets dried out which was caused due to adding yogurt. This would take approx 5 minutes. Veggies should not be cooked completely as we will dum it later on. It should 70% cooked.</li>
<li class="instruction">After the moisture is dried, add some fried onions and transfer to a bowl.</li>
<li class="instruction">For Rice: Boil water. Add oil, whole garam masala, salt. Boil until roaring boil. Add the soaked rice. In about 3-4 minutes in high flame rice will be 70% done. Test by eating some if it has a bite but its tastes like cooked or press between ur fingers. If breaks into 2 parts its ready. Strain it and spread on a big plate.</li>
<li class="instruction">For Dum: Take a cooker, add half of the vegetables, yogurt, green chilli, a pinch of salt and some nuts. Let it heat. Now add a handful of mint coriander. Now top it off with a good layer of rice.</li>
<li class="instruction">Next add the remaining vegetables, remaining mint-coriander(leave 1 tsp. for top), fried onions and the rice. Spread the rice and add the saffron mixture with a bit of mint-coriander, fried onion.</li>
<li class="instruction">Now sprinkle 1/4 cup of water over it, Cover and cook for 2 minutes in lowest flame.</li>
<li class="instruction">Transfer the cooker to a big pot with some water. This water bath prevents the dum to get burnt.. In about 30-35 minutes the Biryani is ready to make you dig into it.</li>
</ol>
</div>
</div>
<div class="ERNutrition"></div>
<div>
<div class="ERNotesHeader">Notes</div>
<div class="ERNotes">Cut vegetables in a uniform size.[br]Veggies are less, but the rice is used more for Biryanis.[br]You could use raw chopped nuts or saute them in oil and add.[br]Make sure to soak rice for atleast 1 hour.</div>
</div>
<div class="endeasyrecipe" style="display: none;">3.2.1255</div>
</div>
<p>&nbsp;</p>
<p><a href="http://mywebsite.in.cp-25.webhostbox.net/wp-content/uploads/2013/09/Biryani-2.jpg"><img class=" wp-image-411 alignright" alt="Biryani-2" src="http://mywebsite.in.cp-25.webhostbox.net/wp-content/uploads/2013/09/Biryani-2.jpg" width="189" height="142"></a></p>
<p>Recipe Ingredients are lengthy.. But it looks only onscreen.. Once you start preparing them they does not seem long or more.. Basic Ingredients, Easy Recipe and Traditional Hyderabadi Taste.. Dont forget to try it, I am sure you'll love it and make it again and again!!!</p>
<p><a href="http://www.youtube.com/mywebsite" target="_blank">Do Subscribe on YouTube</a></p>
<p><a href="http://www.fb.com/mywebsite" target="_blank">Like us on FB</a></p>
<p>&nbsp;</p>
<p>Video Recipe:</p>
<p><iframe src="//www.youtube.com/embed/zf0_AJfp5AA" height="500" width="835" allowfullscreen="" frameborder="0"></iframe></p>
Improve this question
7
  • You should sanitize your input. One possible method is to reject any input for ingredients that contain html tags. You could limit the size of ingredients too. If you just want, that the links are not clickable then you could use htmlspecialchars() for the output of ingredients. Commented May 18, 2014 at 20:18
  • Is this a preventive measure..?, I didn't get what am I supposed to do, I'm pretty new to the terminologies you used Commented May 18, 2014 at 20:20
  • Yes this is a preventive measure. But by the same method you should be able to filter the spam. I assume that every ingredient is equivalent to the value in one row in a certain column of your table. I would filter first those rows that contain the text <a and the text </a. That could give you a good starting point Commented May 18, 2014 at 20:28
  • Just to make sure that we both are on same page 1. I know the posts which have Spam comments in it 2. I cannot just <a </a use this to filter the spam as I have links to images in my posts 3. My spam messages spread across multiple places it may not be only on ingredients list, it is present at the end of posts or two lines before the post etc: 4. Only way I am seeing is I should be able to fetch/Replace the SUBSTR from the column post_content using Start string = '<div style="position:absolute;' End String = '</div>' (First end div after above) I am sorry if I am not clear with my question Commented May 18, 2014 at 20:35
  • Your example data didn't show any html tags. So I assumed there wasn't one. If you want to remove this with a substr-operation I would propose a backup of the table before this cleaning :-) Commented May 18, 2014 at 20:40

3 Answers 3

Reset to default
2

the answer is variation of the following: 

update wp_posts
set post_content = CONCAT(LEFT(post_content, LOCATE('div style=', post_content)-1),  
                   SUBSTRING(post_content, LOCATE('</div>', post_content)+7))
where LOCATE('div style=', post_content) > 0

Changing the "div style" to whatever beginning that works.

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

Comments

1

Your query is close, but you need to use LOCATE as well as INSTR to find the end of the embedded string: 

select 
  ID, 
  post_title, 
  substr(post_content,
   instr(post_content,'<div style="position:absolute'), 
   LOCATE('</div>',post_content,instr(post_content,'<div style="position:absolute')+30)-instr(post_content,'<div style="position:absolute')
   +6) as temp 
from wp_posts 
where post_content LIKE '%<div style="position:absolute%' 
LIMIT 10

See example here: http://sqlfiddle.com/#!2/d0cf2f/13

Improve this answer

5 Comments

SubStr returned empty :(
fixed - I forgot order of parameters is reversed in INSTR & LOCATE. Sorry.
Yes it looks like Solved my Problem, Hope I should be able to Replace this extactred SubSTR with Empty String, Thank you very much for your help with this regard.. Really Appreciate it
Once again thanks for your assistance, I was able to replace my Spam text with Empty string.. Thank you very much, I hope I don't become victim of Spam messages again
LOCATE() understands two arguments as well. So you do not need to use INSTR(): dev.mysql.com/doc/refman/5.7/en/… "This is the same as the two-argument form of LOCATE, except that the order of the arguments is reversed."
1

You can use the following query for this replacement:

UPDATE Customer SET address = REPLACE(address, substring_index(substring_index(address, 'prefixString', -1), 'postfixString', 1), '') WHERE address IS NOT NULL;

For Example:

address: Black booster deer street

replace booster with an empty string.

UPDATE Customer SET address = REPLACE(address, substring_index(substring_index(address, 'Black', -1), 'deer', 1), ' ') WHERE address IS NOT NULL;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.