Back to Top

Author Archives: Dunnie

Converting wordpress sql xml post table to html and then doc file

Updated on date by author

Hi, sometimes you only have available from backups an old wordpress post table sql xml file, which you might want to convert to a word doc file for say rewriting for another web site.

The following simple snippet of code I have used successfully to extract each posts title and contents (not excerpt) and add to a html file.

Starting point was a post sql xml file called bhrt.me.wp_posts.xml.. dumped from a mysql database (wp_posts table)/

Script could be called anything, but I called it ‘extract_posts.php’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

<?php
 
// replace bhrt.me.wp_posts.xml with your post table sql xml filename
 
$fname = "bhrt.me.wp_posts.xml";
 
$xml=simplexml_load_file($fname);
 
echo("<html>\n
<head>\n
<meta charset=\"UTF-8\">\n
<meta http-equiv=\"Content-Type\" content=\"application/xhtml+xml; charset=utf-8\"/>\n
</head>\n
<body>\n");
 
foreach ($xml->database->table as $table) {
$post_title="";
$post_type="";
$post_content="";
 
foreach ($table->column as $column) {
 
    switch((string) $column['name']) {
    case 'post_title':
        $post_title = $column;
        break;
    case 'post_content':
        $post_content = $column;
        break;
    case 'post_type':
        $post_type = $column;
        break;
    }
 
}
 
if((($post_type=='post')||($post_type=='page'))&&($post_content!="")) {
 
if(stripos($post_content,'[contact-form')===false) {
 
echo "<div>\n";
 
echo "<p><h2>".$post_title."</h2></p>\n";
 
echo("<br>\n");
 
$post_content = preg_replace("/<img[^>]+\>/i", "", $post_content); 
 
echo "<p>".$post_content."</p>\n";
 
echo "</div>\n";
 
echo("<br>\n");
 
}
 
}
 
}
 
echo("</body>\n
</html>");
 
?>

Run locally via ‘php extract_posts.php > output-filename.html’ Then load the html file into your word or openoffice package – tested with libreoffice.

Posted in PHP, Wordpress |

htaccess protect wp-login.php file

Updated on date by author

There is a major bout of wordpress hacking doing the rounds.. which on servers with many hundreds of wordpress sites can equate to a denial of service attack.

Try the following as a means to protect ram and cpu resources by .htaccess protecting wp-login.php file from public access. The following method assumes you are using something like cpanel’s file manager, but could be done locally as well with relevant files.

1) Edit root wordpress level .htaccess file and add the following to the top of the file:

1
2
3
4
5
6
7
8
9
10

# BEGIN Admin Protection
ErrorDocument 401 "Unauthorized Access"
ErrorDocument 403 "Forbidden"
<FilesMatch "wp-login.php">
AuthName "Authorized Only"
AuthType Basic
AuthUserFile /home/{account}/.wppassword
require valid-user
</FilesMatch>
# END Admin Protection

Replace {account} with your’s account account name. Save.

2) Next go to the top level – above public folders and files and create a blank file called .wppassword and using an external service like http://www.htaccesstools.com/htpasswd-generator/ create a username and a password to be used in this .wppassword file. Copy returned content and add to your just created .wppassword file and save.

3) Now go to login to your wordpress site.. a popup box should appear asking for your username and password, which will be the same as you selected for (2) above. If not, please check which .htaccess file you edited.. should be the same level as the wp-login.php file – same directory, or if username/password not accepted, check the path to your .wppassword file eg line AuthUserFile /home/{account}/.wppassword

I hope this helps..we have a server with many hundreds of wordpress sites, and due to hacking server load kept on soaring from 4.0 to well over 100 crashing the web server and also denting search engines ability to index the websites..

portflood protection can also provide temporary cover, but false positives tend to not make it a long term solution.

Posted in Firewall, Wordpress |

US phone number validation function

Updated on date by author

A quick function used to validate and format US based phone numbers.. enter phone number into function, and either returns false if deemed to be invalid, or returns better formatted… idea for form validation checks.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

function VALIDATE_USPHONE($value)
{
$regex = '/(?:1(?:[. -])?)?(?:\((?=\d{3}\)))?([2-9]\d{2})'
        .'(?:(?<=\(\d{3})\))? ?(?:(?<=\d{3})[.-])?([2-9]\d{2})'
        .'[. -]?(\d{4})(?: (?i:ext)\.? ?(\d{1,5}))?/'; 
 
    if ($Valid = preg_match($regex, $value, $matches ) ) {
    $formatted = "($matches[1]) $matches[2]-$matches[3]";
    if ($matches[4]) $formatted .= " x$matches[4]";
    return $formatted;
    } else {
    return false;
    }
 
}
Posted in PHP |

Unix script snippet to convert php file underscores.

Updated on date by author

A simple unix/linux script to HELP convert php file underscores and alter local site link backs via rpl command – helps improve seo. This assumes the rpl command is installed, and all php files are at the same level – no recursive sub directory changes. Warning – could also change external links if they happened to have the same underscore original file name.. Also assumes no white spaces in the original php file names.

1
2
3
4
5
6
7

#!/bin/sh
for FILE in `ls -1 *_*.php`
do
echo $FILE changes to `echo $FILE | sed s/_/-/g`
rpl $FILE `echo $FILE | sed s/_/-/g` *
mv $FILE `echo $FILE | sed s/_/-/g`
done
Posted in Unix Scripts |

Code to email an attachment

Updated on date by author

PHP function code to add an attachment – data to be put into an attached file called $filename, is stored in variable $data ::

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

<?php
 
function attach_mail($to, $subject, $message, $header, $filename, $data){
$data = chunk_split(base64_encode($data));
$uid = md5(uniqid(time()));
$header .= "MIME-Version: 1.0\r\n";
$header .= "Content-Type: multipart/mixed; boundary=\"".$uid."\"\r\n\r\n";
$header .= "This is a multi-part message in MIME format.\r\n";
$header .= "--".$uid."\r\n";
$header .= "Content-type:text/html; charset=iso-8859-1\r\n";
$header .= "Content-Transfer-Encoding: 7bit\r\n\r\n";
$header .= $message."\r\n\r\n";
$header .= "--".$uid."\r\n";
$header .= "Content-Type: application/octet-stream; name=\"".basename($filename)."\"\r\n" .
"Content-Description: ".basename($filename)."\r\n" .
"Content-Disposition: attachment;\r\n" . " filename=\"".basename($filename)."\"; size=".strlen($data).";\r\n" .
"Content-Transfer-Encoding: base64\r\n\r\n" . $data . "\r\n\r\n";
$header .= "--".$uid."--";
$ok = @mail($to, $subject, "", $header);
if($ok){ return 1; } else { return 0; }
}
 
?>
Posted in Misc Topics |

WordPress code to find deleted youtube videos

Updated on date by author

The following php snippet of code I use to find deleted youtube videos in wordpress posts.. hope it helps someone.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

<?php
// put post content into variable $post_content
 
$post_content=$post->post_content; // via mysql direct post table row $post
 
preg_match_all("/\[youtube\=http:\/\/([a-zA-Z0-9\-\_]+\.|)youtube\.com\/watch(\?v\=|\/v\/|#!v=)([a-zA-Z0-9\-\_]{11})([^&lt;\s]*)\]/", $post_content, $matches, PREG_SET_ORDER);
// scan through each youtube url match
foreach ($matches as $match) {
$videoid=trim($match[3]); // youtube video reference code
if(!check_youtube($videoid)) {
echo($videoid." dead in post <br>\n");
// put code in here to handle post with deleted youtube video
}}
 
function check_youtube($videoid) {
if (strtolower(file_get_contents('http://gdata.youtube.com/feeds/api/videos/'.$videoid)) == 'video not found') {
  return false;
} else {
  return true;
}}
 
?>
Posted in Misc Topics |

A php imap script to download gmail attachments

Updated on date by author

This is a simple php script to automatically download gmail attachments based on incoming author and subject text of email into folder script is run on, but other imap search queries could be used. I use it locally on my linux box in the form of ‘php download-directly-from-gmail.php’ I hope it helps someone.

eg.

orion@orion-Dimension-2350 ~/Downloads/SHUTTERSTOCK IMAGES $ php download-directly-from-gmail.php
173-shutterstock_117192613.jpg .. saving
174-shutterstock_125141474.jpg .. saving
175-shutterstock_126624197.jpg .. saving
170-shutterstock_107627765.jpg .. saving
171-shutterstock_116964424.jpg .. saving
172-shutterstock_117192571.jpg .. saving
166-shutterstock_77529775.jpg .. saving
167-shutterstock_85118617.jpg .. saving
168-shutterstock_93945283.jpg .. saving
169-shutterstock_107147873.jpg .. saving

Script below.. with password and username nulled.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117

<?php
set_time_limit(0); 
 
 /* connect to gmail with your login account details */
 
$hostname = '{imap.gmail.com:993/imap/ssl}INBOX';
$username = 'somebody@gmail.com'; # e.g somebody@gmail.com
$password = 'your-gmail-password';
 
 
$inbox = imap_open($hostname,$username,$password) or die('Cannot connect to Gmail: ' . imap_last_error());
 
 
$emails = imap_search($inbox,'FROM "person" SUBJECT "something in subject"'); // finds all incoming mail from "person" containing partial text in subject 'something in subject'
 
if($emails) {
 
    $count = 1;
 
 
    rsort($emails);
 
 
    foreach($emails as $email_number) 
    {
 
        $overview = imap_fetch_overview($inbox,$email_number,0);
 
        $message = imap_fetchbody($inbox,$email_number,2);
 
        $structure = imap_fetchstructure($inbox, $email_number);
 
        $attachments = array();
 
        if(isset($structure->parts) && count($structure->parts)) 
        {
            for($i = 0; $i < count($structure->parts); $i++) 
            {
                $attachments[$i] = array(
                    'is_attachment' => false,
                    'filename' => '',
                    'name' => '',
                    'attachment' => ''
                );
 
                if($structure->parts[$i]->ifdparameters) 
                {
                    foreach($structure->parts[$i]->dparameters as $object) 
                    {
                        if(strtolower($object->attribute) == 'filename') 
                        {
                            $attachments[$i]['is_attachment'] = true;
                            $attachments[$i]['filename'] = $object->value;
                        }
                    }
                }
 
                if($structure->parts[$i]->ifparameters) 
                {
                    foreach($structure->parts[$i]->parameters as $object) 
                    {
                        if(strtolower($object->attribute) == 'name') 
                        {
                            $attachments[$i]['is_attachment'] = true;
                            $attachments[$i]['name'] = $object->value;
                        }
                    }
                }
 
                if($attachments[$i]['is_attachment']) 
                {
                    $attachments[$i]['attachment'] = imap_fetchbody($inbox, $email_number, $i+1);
 
                    if($structure->parts[$i]->encoding == 3) 
                    { 
                        $attachments[$i]['attachment'] = base64_decode($attachments[$i]['attachment']);
                    }
                    elseif($structure->parts[$i]->encoding == 4) 
                    { 
                        $attachments[$i]['attachment'] = quoted_printable_decode($attachments[$i]['attachment']);
                    }
                }
            }
        }
 
        foreach($attachments as $attachment)
        {
            if($attachment['is_attachment'] == 1)
            {
                $filename = $attachment['name'];
                if(empty($filename)) $filename = $attachment['filename'];
 
                if(empty($filename)) $filename = time() . ".dat";
 
                echo($filename." ");
 
                if(file_exists($email_number . "-" . $filename)) {
                echo(".. file already exists! \n");
                } else {
                echo(".. saving \n");
                $fp = fopen($email_number . "-" . $filename, "w+");
                fwrite($fp, $attachment['attachment']);
                fclose($fp);
                }
            }
 
        }
 
    }
 
} 
 
imap_close($inbox);
 
echo "Done";
 
?>
Posted in PHP |

A php script to find blacklisted ips connected to server

Updated on date by author

This is a simple script which I find useful in finding blacklisted ips using server (spammers etc), via a third party api (yasb.intuxication.org), connected to server, so can permanently ban them on the server firewall.

Log into the server as root via terminal / sshd (I use putty under Linux), and run this following standard network command to produce a file of ips connected to the server (snapshot)..

netstat -ntu | grep -v “::” | grep “:” | awk ‘{print $5}’ | cut -d: -f1 | sort | uniq -c | sort -nr > ip-check.txt

This will produce a file ip-check.txt, which contains lines of the format: No of connections ip address

eg

2 127.0.0.1
1 213.199.179.141
1 199.59.148.82
1 174.133.195.84

(your list on a web server will be much longer than this example and likely to have many more connections per ip)

Next step is to create a file called check-spam-ips.php in the same folder as ip-check.txt was just created above.

which uses the following php code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

<?php
 
$data=file('ip-check.txt');
 
foreach($data as $line) {
 
$line = trim($line);
list($count,$ip)=explode(' ',$line);
$url = "http://yasb.intuxication.org/api/check.xml?ip=".$ip;
 
$info=file_get_contents($url);
 
$orgxml = simplexml_load_string($info);
 
if ($orgxml===false) {
    echo "Failed loading XML\n";
    foreach(libxml_get_errors() as $error) {
        echo $error->message."<br>\n";
    }
exit;
}
 
$spam=$orgxml->spam;
 
if($spam=='true') {
echo($ip." ".$spam."\n");
}
 
}
 
?>

Then run the script as follows..

php check-spam-ips.php

This will then produce to the screen a list of ips connected to server found to have been previously blacklisted.

You can then use your servers portal software or iptables to ban these blacklisted ips or investigate further.

I hope this is of use to anyone.

Posted in Firewall |

Code to fix wordpress post counts for each category

Updated on date by author

Sometimes the wordpress category post counts displayed (widgets/admin side) become out of sync, this simple snippet of php code fixed my own website(s) and I hope it helps someone else too. It could be future extended to also clear out orphaned terms too (which caused the out of sync issue to begin with). Usual cause of post count to become out of sync is when plugins clear out redundant posts by just deleting the wp_post table, and not removing term items and adjusting category term count.

This is probably not the neatest bit of code (would be better to use mysql JOIN statements between tables), but unlike other solutions I have seen on the web, this one actually works on my wordpress system.

Basically copy code onto a script under wordpress site (public area), and call it via browser eg http://sitedomainname/fixpostcounts.php

It assumes the prefix of mysql tables starts with the default “wp_” – change accordingly. Will look into making a plugin version aswell (time permitting).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

<?php
// post count per category fix by platinumshore
set_time_limit(0);
 
    require('wp-config.php'); 
 
    $username = DB_USER; 
    $password = DB_PASSWORD; 
    $server = DB_HOST; 
    $dB = DB_NAME; 
    $connect = mysql_connect($server, $username, $password);
    if (!$connect){ 
    die('Could not connect: ' . mysql_error());
    }
    else{
    mysql_select_db($dB, $connect);
    }
 
$query="Select * from `wp_term_taxonomy` where `taxonomy`='category'";
$result = mysql_query($query);
$category_ids = array();
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$category_ids[] = $row["term_taxonomy_id"];
}
mysql_free_result($result); 
 
foreach($category_ids as $category_id) {
$query="Select * from `wp_term_relationships` where `term_taxonomy_id`='".$category_id."'";
$result = mysql_query($query);
$count=0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$post_id = $row["object_id"];
// does post id actually exist ?
$num_rows = mysql_num_rows(mysql_query("SELECT * FROM `wp_posts` where `post_status`='publish' and `post_type`='post' and `ID`='".$post_id."'"));
if($num_rows>0) {
$count=$count+1; // count of actual valid posts found
}}
mysql_free_result($result);
 
// get term id for this category id
$query="Select * from `wp_term_taxonomy` where `term_taxonomy_id`='".$category_id."'";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$term_id = $row['term_id'];
 
// get category name from term id
$query="Select * from `wp_terms` where `term_id`='".$term_id."'";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$name = $row['name'];
 
echo("Category Name:".$name.", Real Post Count:".$count."<br>\n");
 
$query = "Update `wp_term_taxonomy` set `count`='".$count."' where `term_taxonomy_id`='".$category_id."'";
mysql_query($query);
 
}
 
 
?>
Posted in Wordpress |