Easily find and replace text in all blog posts

Thanks to a comment left by John McLear, I discovered today that characters such as &, <, >, " in some of my older posts got converted to special HTML entities.

Fixing this straight in the DB is probably the easiest way to go...

First thing is to figure out which of the WordPress tables are for my blog:

1
2
3
4
5
6
CMD="mysql giantdorks --skip-column-names -Be"
TBS=$($CMD "show tables like 'wp%options'")
for TB in $TBS; do
  URL=$($CMD "select option_value from $TB where option_name='siteurl'")
  echo $TB is for $URL
done
1
2
3
wp_1_options is for http://giantdorks.org/
wp_2_options is for http://giantdorks.org/alain/
wp_3_options is for http://giantdorks.org/jason/

Dump my posts:

1
mysqldump giantdorks wp_2_posts > giantdorks.alain.posts.sql

Let's count how many special HTML entities are found in my posts:

1
grep -Eo '&[A-Za-z]+;' giantdorks.alain.posts.sql | sort | uniq -c
1
2
3
4
    539 &amp;
    744 &gt;
    362 &lt;
    416 &quot;

I'll use the following sed one liner to do the replacement for me:

1
sed 's/&amp;/\&/g;s/&gt;/>/g;s/&lt;/</g;s/&quot;/"/g;'

Let's test it.

This post contained &lt; instead of < and several '&quot;' instead of double quotes:

1
grep -Eo "(.){15}fp = fsockopen(.){40}" giantdorks.alain.posts.sql | head -1
1
\n&lt;?php\r\n$fp = fsockopen(&quot;127.0.0.1&quot;, &quot;80&quot;,

Let's pass to sed for replacement:

1
grep -Eo "(.){15}fp = fsockopen(.){40}" giantdorks.alain.posts.sql | head -1 | sed 's/&amp;/\&/g;s/&gt;/>/g;s/&lt;/</g;s/&quot;/"/g;'
1
\n<?php\r\n$fp = fsockopen("127.0.0.1", "80",

This post contained &gt instead of > and &amp; instead of &:

1
grep -Eo "(.){45}Example: spam-stats-month Oct 2009" giantdorks.alain.posts.sql | head -1
1
  echo -e 1&gt;&amp;2 \"\\n Usage error..\\n Example: spam-stats-month Oct 2009

Let's pass to sed for replacement:

1
grep -Eo "(.){45}Example: spam-stats-month Oct 2009" giantdorks.alain.posts.sql | head -1 | sed 's/&amp;/\&/g;s/&gt;/>/g;s/&lt;/</g;s/&quot;/"/g;'
1
  echo -e 1>&2 \"\\n Usage error..\\n Example: spam-stats-month Oct 2009

Looks good, let's do it:

1. Will make a backup of the sql dump in case something goes terribly wrong
2. Then do the replacement with sed
3. Load the table dump
4. Finally purge the varnish cache to see my changes:

1
2
3
4
cp giantdorks.alain.posts.sql giantdorks.alain.posts.sql.bak
sed -i 's/&amp;/\&/g;s/&gt;/>/g;s/&lt;/</g;s/&quot;/"/g;' giantdorks.alain.posts.sql
mysql giantdorks < giantdorks.alain.posts.sql
sudo varnishadm -T 127.0.0.1:6082 -S /etc/varnish/secret purge.url ".*"

Yay..

Leave a comment

NOTE: Enclose quotes in <blockquote></blockquote>. Enclose code in <pre lang="LANG"></pre> (where LANG is one of these).