修复数据库中被SyntaxHighlighter替换的特殊符号

2012 年 11 月 21 日2012 年 11 月 21 日 by Orange

< > " & '修改自http://quartergeek.com/fix-special-char-replaced-by-syntaxhighlighter

代码增加了单引号的替换，同时增加几个变量提醒用户修改。

自从换上了Crayon Syntax Highlighter，发现原来<>&”’符号被WP替换成了< > " & ‘而Crayon Syntax Highlighter又不能转换，只好转回来，于是写了下面这个脚本，成功地替换回来了。

set_charset("utf8");
$result = $conn->query("SELECT post_content, ID FROM ".$ta);
$stmt = $conn->prepare("UPDATE ".$ta." SET post_content = ? WHERE ID = ?"); 

$search  = array("<", ">", """, "&", "'");
$replace = array("<"   , ">"   , "\""    , "&"    ,"'");
while ($row = $result->fetch_array())
{
  $id = $row['ID'];
  $post_content = str_replace($search, $replace, $row['post_content']);
  $stmt->bind_param('si', $post_content, $id);
  if (!$stmt->execute())
  {
    die("\n[ERROR!]".$id."\n");
  }
  else
  {
    echo "[Success]".$id."\n";
  }
}

$result->free();
$conn->close();
?>

PHP 使用 GeoLiteCity 库解析 IP 为地理位置

2012 年 11 月 21 日2012 年 11 月 21 日 by Orange

关于把 IP 地址转换为地理位置可以使用网络上很多的 API，好处就是不用在本地存储一个 IP 数据库，而且一般网络上的 IP 库会自动更新，不利的地方就是太依赖于网络，性能表现也可能会弱些。比如像下面的 API:

http://api.hostip.info/get_html.php?ip=58.63.236.31
http://api.hostip.info/flag.php?ip=58.63.236.31

这里介绍 PHP 如何使用 GeoLiteCity.dat 库把 IP 转换为地理位置，GeoLiteCity.dat 可以在 http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz 下，解压出 GeoLiteCity.dat，即可，我们可以手动去更新新的 IP 库。

下面 PHP 解析 IP 的过程参考自 WordPress 插件 Visitor Maps and Who’s Online 的实现。可以找到该插件的两个文件 include-whos-online-geoip.php 和 visitor-maps.php 告诉了我们怎么做。你可以点击这里的链接下载到这两个文件，我这里把 include-whos-online-geoip.php 改名为 geoipcity.inc.php，然后参考 visitor-maps.php 中的 get_location_info($user_ip) 函数，那么我们可以写出自己的解析 IP 地址的程序 resolve_ip.php:

city)) ? $record->city : '';  //城市
    $location_info['state_name']   = (isset($record->country_code) && isset($record->region)) //州名
          ? $GEOIP_REGION_NAME[$record->country_code][$record->region] : '';
    $location_info['state_code']   = (isset($record->region)) ? strtoupper($record->region) : ''; //州代号
    $location_info['country_name'] = (isset($record->country_name)) ? $record->country_name : '--'; //国家名
    $location_info['country_code'] = (isset($record->country_code)) ? strtoupper($record->country_code) : '--'; //国家代号
    $location_info['latitude']     = (isset($record->latitude)) ? $record->latitude : '0';   //维度
    $location_info['longitude']    = (isset($record->longitude)) ? $record->longitude : '0'; //经度
    //php 站点设置了 utf-8 字符集必要时进行转码
    $charset = 'utf-8';
    // this fixes accent characters on UTF-8, only when the blog charset is set to UTF-8
    if ( strtolower($charset) == 'utf-8' && function_exists('utf8_encode') ) {
        if ($location_info['city_name'] != '' ) {
            $location_info['city_name'] = utf8_encode($location_info['city_name']);
        }
        if ($location_info['state_name'] != '') {
            $location_info['state_name'] = utf8_encode($location_info['state_name']);
        }
        if ($location_info['country_name'] != '') {
            $location_info['country_name'] = utf8_encode($location_info['country_name']);
        }
    }
    return $location_info;
}
//查询一个 IP 测试下
$record = get_ip_record("206.19.49.154");
var_dump($record);
$location = get_location_info("206.19.49.154");
var_dump($location);
?>

执行后输出如下(可以作为系统脚本直接用 php resolve_ip.php 来执行)：

object(geoiprecord_VMWO)#2 (10) {
  ["country_code"]=>
  string(2) "US"
  ["country_code3"]=>
  string(3) "USA"
  ["country_name"]=>
  string(13) "United States"
  ["region"]=>
  string(2) "TX"
  ["city"]=>
  string(10) "Richardson"
  ["postal_code"]=>
  string(5) "75080"
  ["latitude"]=>
  float(32.9722)
  ["longitude"]=>
  float(-96.7376)
  ["area_code"]=>
  int(972)
  ["dma_code"]=>
  float(623)
}
array(7) {
  ["city_name"]=>
  string(10) "Richardson"
  ["state_name"]=>
  string(5) "Texas"
  ["state_code"]=>
  string(2) "TX"
  ["country_name"]=>
  string(13) "United States"
  ["country_code"]=>
  string(2) "US"
  ["latitude"]=>
  float(32.9722)
  ["longitude"]=>
  float(-96.7376)
}

只要取你想要的数据就是了，里面还有诸如区号，邮编等数所，GeoLiteCity.dat 是个二进制文件，比普通文本要紧凑省空间。

不知道您有没有多留一份心，有无浏览链接：http://geolite.maxmind.com/download/geoip/api/php/，是这样的：

看到 GeoIp 给我们提供了不少的例子，那么多 sample.php，而实际上前面用到的 geoipcity.inc.php，就是 geopip.inc、geoipcity.inc 和 geoipregionvars.php 三个程序的内容合体。

再往上看：

官方提供的 API 何止 PHP 啊，几乎能全线满足您的实际需求了，c、java、perl、python、vb、ruby、tcl 等……，放其他程序里以后也不用愁了。

再进到 http://geolite.maxmind.com/download/geoip/database/ 瞧瞧：

正考虑着呢，不是说 IPv4 快用净了吗？IPv6 的数据也正为我们准备着呢？当然，天朝的 IPv9 恐怕永远不会有的。

本只是把 Visitor Maps and Who’s Online 里的解析 IP 的做法抽出来用用，可总能不断深入再深入，不知道可喜还是可怕了。

其實很久以前就注意到,某些計數器可以很準確的判定IP來源的位置,甚至連所在城市都可以顯示出來
後來才知道是因為有GeoIP這樣的東西
MaxMind – GeoIP | IP Address Location Technology
這是MaxMind開發的東西,有不同平台的API和定期更新的”IP-位置”資料庫
如果要在網站上應用的話,可以找Apache module,perl和php的API

在Apache上面的模組叫”mod_geoip”
さすが,租用的主機空間不可能給我隨便安裝模組,所以先在自己的電腦試…
不過仔細看才發現那檔案是linux上面用的,只有個c檔要自己make install
好吧…只好跳過…不想為了這東西去裝linux

再來試試perl,它的module是”Geo::IP
這也是要安裝的,所以先在自己的電腦試..
不過不知道是不是activeperl的版本問題,根本就裝不起來…orz
在目前的主機上面雖然可以自己安裝perl module,但是卻限制CPAN上面的東西,還好搜尋了一下就發現這個模組
Apache2::Geo::IP
但是不知道為什麼,安裝到最後出現錯誤:

CODE:

  CPAN.pm: Going to build R/RK/RKOBES/Apache-GeoIP-1.99.tar.gz

Please install either mod_perl 1 or mod_perl 2 first at Makefile.PL line 118. No 'Makefile' created RKOBES/Apache-GeoIP-1.99.tar.gz /usr/bin/perl Makefile.PL PREFIX=/home/jwintaiw/perl/usr -- NOT OK Running make install Make had some problems, won't install

[Copy to clipboard]

呃阿…orz
還好仔細看,發現主機裡面有預裝另外一個類似功能的”Geo::IPfree”
它會從ip或主機名尋找之後回傳三個變數的陣列:二文字國碼,國家全名,IP
小試了一下可以動作,不過速度無敵慢!

最後嘗試PHP API
網頁上面有geoip和geoipcity兩種,前者是從ip/主機名尋找之後回傳二文字國碼和國家全名;後者是回傳更詳細的位置資訊,包含國家,程式和經緯度
不過兩者資料庫差很多,geoip需要的資料庫大約1MB附近,但是geocity的資料庫快30MB…|||
這邊照著該網頁上放的範例自己弄了一個測試的程式,有興趣可以試試…
http://snow-sugar.net/lookup.php (會放多久不確定,因為database太大)
同樣的東西也在自己電腦的自架網站動作確認,感覺大概像這樣

其實最重要的應用不在這裡,而是可以利用辨識來源IP的國家進而讓server作不同的動作,例如台港的IP就自動轉入中文頁面,歐美的IP就自動轉入英文頁面,韓國的IP就block等等…因為IP對應位置的資料庫有持續更新,也讓誤動做的可能減少

Setting up awstats with apache 2 & geoip on debian-ubuntu

2012 年 11 月 21 日 by Orange

Awstats is a web log analyzer. It gives really useful statistics about traffic on your web sites.This log analyzer works as aCGI or from command line and shows you all possible information your log contains, in few graphical web pages.

yesterday, it took me many hour to configure it on my server, I took help from many blogs to complete it.

Hope this can help someone.

1. Installing Awstats:

ab@web1:~$ sudo apt-get install awstats

This command will install awstats.Now we have to configure Apache.

2. Configuring Apache2:

Now make the following changes inawstats.conf file.

ab@web1:~$ sudo vi /etc/apache2/awstats.conf

Alias /awstatsclasses “/usr/share/awstats/lib/”
Alias /awstats-icon/ “/usr/share/awstats/icon/”
Alias /awstatscss “/usr/share/doc/awstats/examples/css”
ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
ScriptAlias /awstats/ /usr/lib/cgi-bin/
Options ExecCGI -MultiViews +SymLinksIfOwnerMatch

After, Making these changes, we have to include this file (/etc/apache2/awstats.conf) in to our apache.conf file, so we can access it over http.

ab@web1:~$ sudo vi /etc/apache2/apache2.conf

Include /etc/apache2/awstats.conf

Now, reload the apache.

ab@web1:~$ sudo /etc/init.d/apache2 reload

Now you can try to open http://www.mysite.com/awstats/awstats.pl but you will get this error: Error: SiteDomain parameter not defined in your config/domain file. You must edit it for using this version of AWStats.

That means we have to configure Awstats.

3. Configuring Awstats:

By default awstats provides a default configuration file named /etc/awstats/awstats.conf. Copy this file to /etc/awstats/awstats.www.mysite.com.conf:

~$sudo cp /etc/awstats/awstats.conf /etc/awstats/awstats.www.mysite.com.conf

and edit the file:

~$sudo vi /etc/awstats/awstats.www.mysite.com.conf

and check for the following lines and edit those for your needs:

LogFile=”/var/log/apache2/access.log”
SiteDomain=”mysite.com”

This is all setted up, now you need to generate the first stats.

4. Generating the First Stats:

in order to generate the first stats, you need to call the script as root using the following command line:

~:$sudo -u www-data /usr/bin/perl /usr/lib/cgi-bin/awstats.pl -update -config=www.mysite.com

After finishing this you can try to open http://www.mysite.com/awstats/awstats.pl this will show you the stats.

Now for automatic update the stats you need to put the following command in your cron.

0 2 * * * www-data [ -x /usr/lib/cgi-bin/awstats.pl -a -f /etc/awstats/awstats.conf -a -r /var/log/apache/access.log ] && /usr/lib/cgi-bin/awstats.pl -config=mysite.com -update >/dev/null

GeoIP Information for AWStats

Improve country and city location information in your web analytics reports

AWStats offers three different ways to provide Country level information about visitors (“hosts”) connecting to your site. By default, the host domain extension is used if you have performed reverse DNS look-up. While this results in human readable host names as well, reverse DNS lookup is time consuming. Alternatively, GeoIP plugins can be used. They match the host IP with a database of IP allocations which is already installed locally on your computer, significantly reducing the time needed to match a host to a location. There are two country level GeoIP databases available. AGeo::IPFree Perl module is available, but it does not appear that the database is being maintained. The better solution is MaxMind’s Geo::IP GeoLite Country open source version, said to be 97% accurate.

Download the current GeoLite Country, GeoLite City and Organization (AS Numbers)databases
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz
wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz
wget http://geolite.maxmind.com/download/geoip/database/asnum/GeoIPASNum.dat.gz

Move data files to directory and unzip.

mv GeoIP.dat.gz /usr/local/share/GeoIP/
gunzip /usr/local/share/GeoIP/GeoIP.dat.gz
mv GeoLiteCity.dat.gz /usr/local/share/GeoIP/
gunzip /usr/local/share/GeoIP/GeoLiteCity.dat.gz
mv GeoIPASNum.dat.gz /usr/local/share/GeoIP/
gunzip /usr/local/share/GeoIP/GeoIPASNum.dat.gz
Install the GeoIP program.

1. wget http://geolite.maxmind.com/download/geoip/api/c/GeoIP-1.4.5.tar.gz
tar -xvzf GeoIP-1.4.5.tar.gz
cd GeoIP-1.4.5
./configure
make
make check
sudo make install

2.wget http://geolite.maxmind.com/download/geoip/api/perl/Geo-IP-1.35.tar.gz
tar -xvzf Geo-IP-1.35.tar.gz
cd Geo-IP-1.35
perl Makefile.PL LIBS=’-L/usr/local/lib’
make
make test
sudo make install

Update your awstats.mysite.conf configuration file

Look for the following geoip LoadPlugin options in your AWStats configuration file. Update them with the full path to each of the two database files.

LoadPlugin=”geoip GEOIP_STANDARD /usr/local/share/GeoIP/GeoIP.dat”
LoadPlugin=”geoip_city_maxmind GEOIP_STANDARD /usr/local/share/GeoIP/GeoLiteCity.dat”
LoadPlugin=”geoip_org_maxmind GEOIP_STANDARD /usr/local/share/GeoIP/GeoIPASNum.dat”

Well, It’s done and now you can check the stats with country info.

Cheers!!

DH的虚拟主机上是有GeoIP.dat的，可以这样找到路径：

$ locate GeoIP.dat
/usr/local/share/GeoIP/GeoIP.dat
/usr/share/GeoIP/GeoIP.dat

安装库模块：

perl -MCPAN -e ‘install “Geo::IP”‘

perl -MCPAN -e ‘install “Geo::IP::PurePerl”‘

我找到两个，比了一下时间，/usr/local那个要更新一点，所以用这个。所以，像下边这样修改common.conf：

找到
# LoadPlugin=”geoip GEOIP_STANDARD /pathto/GeoIP.dat”
取消注释，改为
LoadPlugin=”geoip GEOIP_STANDARD /usr/local/share/GeoIP/GeoIP.dat”

这样做，就可以开启GeoIP功能了，也就是在IP栏的右边，显示出IP所在国家。

如果，你认为DH的GeoIP.dat数据比较老，想使用比较新的，可以直接从MaxMind下载。可以下载GeoIP.dat.gz和GeoLiteCity.dat.gz，之后解压，放在某处。假设放在/home/yourname/awstats/目录下。

LoadPlugin=”geoip GEOIP_STANDARD /pathto/GeoIP.dat”
LoadPlugin=”geoip_city_maxmind GEOIP_STANDARD /pathto/GeoIPCity.dat”
把以上两句改为：
LoadPlugin=”geoip GEOIP_STANDARD /home/awstats/awstats/GeoIP.dat”
LoadPlugin=”geoip_city_maxmind GEOIP_STANDARD /home/yourname/awstats/GeoIPCity.dat”

这样，就可以有国家和城市两栏显示了。

但是，这个城市显示比较粗糙一点，还可以利用QQ的纯真库IP信息来显示，有人做了一个这样的插件，qqhostinfo。需要以下步骤：

1. 下载纯真版IP数据库，解压缩后，只使用QQWry.dat
2. 下载qqhostinof.pm
3. 下载分析QQWry.dat的脚本
4. 把以上文件都放在cgi-bin/plugins下
5. 修改qqwry.pl，把./QQWry.dat改为${DIR}/plugins/QQWry.Dat
6. 修改common.conf，加入LoadPlugin=”qqhostinfo”

经过以上设置就可以了，会在GeoIPCity右边出现Location栏，里边有很具体的地址信息。

在使用qqhostinfo的时候，需要安装Net::XWhois模块。发现DH的主机上已经装了这个模块。

如果在没有的虚拟主机上安装，由于没有管理员权限，所以，正常的安装过程在make install的时候会报错的。所以，可以这样：

$ make Makefile.PL PREFIX=/home/yourname/awstats/lib # 这样可以把最后的安装都放在这个目录里
$ make
$ make install

另外，如果对中文搜索引擎统计有更高需求的话，可以使用车东的TOP 8 Chinese local search engines。这东西是将近一年前的，适用于AWStats-6.6，对于6.7来说不太合适。我做了一个6.7的search_engines.pm.patch，可以对search_engines.pm打patch。不管在本地，还是在DH主机的shell里，可以

patch search_engines.pm search_engines.pm.patch

1.更新若干有用的模块：

       ppm install Geo::IP::PurePerl
       ppm install Geography::Countries
       ppm install IP::Country
       ppm install Geo-IPfree
       ppm install  Net-Xwhois

2.GeoIP和qqhostinfo需要下载相关文件

GeoIP需要GeoIP.dat；qqhostinfo需要qqhostinfo.pm、qqwry.pl、QQWry.dat3个文件，把他们都放到cgi-bin的插件目录里面.

GeoIP.dat，qqhostinfo.pm、qqwry.pl、QQWry.dat3 打包CSDN下载：
http://download.csdn.net/detail/debugcs/4360307

3、插件的开启

        # 开启 GeoIP 反查模块
       LoadPlugin=”geoip GEOIP_STANDARD C:/Perl/lib/GeoIP.dat”

       # 开启 IP 反查的外挂
       LoadPlugin=”geoipfree”

       # 开启 decodeutfkeys 外挂，解决“用以搜索的关键词”乱码
       LoadPlugin=”decodeutfkeys”

       # 设定IIS的log时间
       LoadPlugin=”timezone +8″好了，所以的设置都弄好了，awstats已经能够很好的工作了。
       # Plugin: qqhostinfo，显示访问者地区，下面的语句需要手工添加。
       LoadPlugin=”qqhostinfo”

       # 开启HostInfo，这个是qqhostinfo插件的前提

       LoadPlugin=”hostinfo”

* 上面插件加载地址按需修改

日志统计页面GeoIP显示效果：

It may not be able to give the same level of detail as a commercial package such as Urchin, but it provides more than enough detail for most purposes.
On Ubuntu you can install awstats via apt:
apt-get install awstats
This will install the base package however, if you want to lever its full power you will need to install a couple of extra Perl modules.
apt-get install libnet-ip-perl
If you would like to see which countries your visitors are based in then you should look at one of the IP to Country plugins which will give you a Geo IP lookup.
You can choose between either geoipfree or GeoIp.
Other useful plugins are:

Plugin: HostInfo – which gives you a popup whois window
Plugin: IPv6 – adds IPv6 support
Plugin: Tooltips – confused by all the information? This will take away the mystery

On rpm-based system there is a handy little perl script that walks you through the initial configuration. On Ubuntu you have to do it by hand, however most of the configuration is self-explanatory.
Simply copy the provided sample config to a new config file:
cp awstats.conf awstats.yoursite.tld.conf
NB: You will need a separate configuration file for each vhost you wish to examine.
Edit the conf file to match your site’s setup. Important things to pay attention to are any domain aliases, the location of the log files and any IPs you wish to ignore (eg. your own)
You can also decide which optional plugins you want to load. Simply add a line to invoke each one:
LoadPlugin="geoipfree" LoadPlugin="ipv6" LoadPlugin="Tooltips"
geoipfree requires an extra perl module. On ubuntu the package is called libgeo-ipfree-perl
You are almost ready!
You now need to configure Apache to allow access to your awstats pages.
Rather than messing about with my main Apache config file I found it neater to place the awstats specific information in an external file called awstats.conf:
Options None AllowOverride None Order allow,deny Allow from all

Alias /awstatsicons/ /usr/share/awstats/icon/
ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/

AllowOverride None
Options ExecCGI -MultiViews +SymLinksIfOwnerMatch
Order allow,deny
Allow from all

Then use an include directive in your main Apache config (apache2.conf):
Include /etc/apache2/awstats.conf
You then need to add it in to your vhost:
ScriptAlias /whereyouwanttoaccessit/ /usr/lib/cgi-bin/
If you navigate to:

http://www.yourdomain.tld/whereyouwanttoaccessit/awstats.pl?config=yourdomain.tld

You *should* be able to access your stats.
Of course you’ll need to actually generate the stats first:
/usr/lib/cgi-bin/awstats.pl -config=yourdomain.tld -update
I would recommend you run this from the command line once to create the first set of stats and then add a cronjob:
crontab -e 0,10,20,30,40,50 * * * * /usr/lib/cgi-bin/awstats.pl -config=yourdomain.tld -update >/dev/null wq
This will update your stats every 10 minutes, which means that you’ll only have to update a few lines at a time. If your site is busy this makes sense
The last part of the line is important:
>/dev/null
By default cron emails the user each time it runs. You really don’t want 6 emails an hour telling you that Awstats has run

How to reset awstats

Just delete the files in /var/lib/awtstats (directory defined by DirData parameter)

sudo su - root
cd /var/lib/awstats
rm *
logout

Notes

you have to be root because for some reason, sudo rm -R awstats/* wouldn’t work.
if you get the message Never updated (See 'Build/Update' on awstats_setup.html page) whereas the awstats files are there in /var/lib/awtstats, it’s probably a permission problem on this folder.

桔子小窝

Day: 2012 年 11 月 21 日