All posts by tiX

Apache SSL Configuration for HTTPS and HTTP

HTTP

1
2
3
4
5
6
7
8
9
10
11
12
<VirtualHost *:80>
        ServerName yourdomain.com
        ServerAlias www.yourdomain.com
        DocumentRoot /var/www/yourdomain.com/httpdocs/web

        ErrorLog /var/www/yourdomain.com/logs/error_log
        CustomLog  /var/www/yourdomain.com/logs/access_log common

        <Directory /var/www/yourdomain.com/httpdocs/web>
                AllowOverride All
        </Directory>
</VirtualHost>

HTTPS (with certificate key chain)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<VirtualHost *:443>
        # http://support.godaddy.com/help/article/5349/installing-ssl-certificate-apache-2x
        SSLEngine on
        SSLCertificateFile /etc/httpd/conf/ssl.crt/yourdomain.com.crt
        SSLCertificateKeyFile /etc/httpd/conf/ssl.key/yourdomain.com.key
        SSLCertificateChainFile /etc/httpd/conf/ssh.chain/sf_bundle.crt
        # http://www.networking4all.com/en/support/tools/site+check/cipher+suite/
        SSLProtocol -ALL +SSLv3 +TLSv1
        SSLCipherSuite ALL:!ADH:RC4+RSA:+HIGH:+MEDIUM:-LOW:-SSLv2:-EXP

        ServerName yourdomain.com
        ServerAlias www.yourdomain.com
        DocumentRoot /var/www/yourdomain.com/httpdocs/web

        ErrorLog /var/www/yourdomain.com/logs/error_log
        CustomLog  /var/www/yourdomain.com/logs/access_log common

        <Directory /var/www/yourdomain.com/httpdocs/web>
                AllowOverride All
        </Directory>
</VirtualHost>

Amazon Load Balancer setting SSL with Certificate Chain

First of all, let’s assume you have the following files with you:

  • yourdomain.key Your domain’s private Key
  • yourdomain.crt Your domain’s public Key
  • sf_bundle.crt The Certificate Chain

Step 1 – Preparing the files

Create a PEM-encoded version of your private key

1
openssl rsa -in yourdomain.key -outform PEM -out yourdomain.pem

Step 2 – Setting the certificate on Amazon

On your Amazon account go to Load Balancers > Your Load Balencer > Listeners

Load Balencer Protocol: HTTPS
Load Balencer Port: 443
Instance Protocol: HTTP
Instance Port: 80
Cipher: ELBSample-OpenSSLDefaultCipherPolicy
Certificate Name: Yourdomain.com
Private Key: <past yourdomain.pem file here>
Public Key Certificate: <past yourdomain.crt file here>
Certificate Chain: <past sf_bundle.crt file here>

Note: This means every request to the Load Balancer will be made on HTTPS. The traffic from the Load Balancer to the destiny instance will be regular HTTP. This way you don’t have to setup any certificate on your instance’s Apache/Nginx web server.

Step 3 – Test

If everything went as expected you should be able to open https://yourdomain.com.

Now, use a SSL check tool to see if everything is OK: http://www.sslshopper.com/ssl-checker.html#hostname=https://yourdomain.com

You should see something like this:

2833574

 

Amazon RDS Tunnel (Access from your desktop)

If you need to access an Amazon RDS instance locally you can:

  1. Add your IP on the RDS Security Group
  2. Create a tunnel to the RDS

Best chances are you have a dynamic IP address, so you already know that option 1. is a real pain.

You can create a tunnel (2.) by performing the following command on a new terminal window:

1
ssh -l <username> -L 33060:<ec2_rfc_ip>:3306 -N <ext_hostname>

As long as you keep this window open, you’ll be able to access the RDS by connecting to localhost on port 33060:

Remove accents from string

Handy code for removing accents from a given string. It is used on WordPress.

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
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
<?php

function seems_utf8($str)
{
    $length = strlen($str);
    for ($i = 0; $i < $length; $i++)
    {
        $c = ord($str[$i]);
        if ($c < 0x80)
            $n = 0;# 0bbbbbbb
       elseif (($c & 0xE0) == 0xC0)
            $n = 1;# 110bbbbb
       elseif (($c & 0xF0) == 0xE0)
            $n = 2;# 1110bbbb
       elseif (($c & 0xF8) == 0xF0)
            $n = 3;# 11110bbb
       elseif (($c & 0xFC) == 0xF8)
            $n = 4;# 111110bb
       elseif (($c & 0xFE) == 0xFC)
            $n = 5;# 1111110b
       else
            return false;# Does not match any model
       for ($j = 0; $j < $n; $j++)
        { # n bytes matching 10bbbbbb follow ?
           if ((++$i == $length) || ((ord($str[$i]) & 0xC0) != 0x80))
                return false;
        }
    }
    return true;
}

/**
 * Converts all accent characters to ASCII characters.
 *
 * If there are no accent characters, then the string given is just returned.
 *
 * @param string $string Text that might have accent characters
 * @return string Filtered string with replaced "nice" characters.
 */

function remove_accents($string)
{
    if (!preg_match('/[\x80-\xff]/', $string))
        return $string;

    if (seems_utf8($string))
    {
        $chars = array(
            // Decompositions for Latin-1 Supplement
            chr(195) . chr(128) => 'A', chr(195) . chr(129) => 'A',
            chr(195) . chr(130) => 'A', chr(195) . chr(131) => 'A',
            chr(195) . chr(132) => 'A', chr(195) . chr(133) => 'A',
            chr(195) . chr(135) => 'C', chr(195) . chr(136) => 'E',
            chr(195) . chr(137) => 'E', chr(195) . chr(138) => 'E',
            chr(195) . chr(139) => 'E', chr(195) . chr(140) => 'I',
            chr(195) . chr(141) => 'I', chr(195) . chr(142) => 'I',
            chr(195) . chr(143) => 'I', chr(195) . chr(145) => 'N',
            chr(195) . chr(146) => 'O', chr(195) . chr(147) => 'O',
            chr(195) . chr(148) => 'O', chr(195) . chr(149) => 'O',
            chr(195) . chr(150) => 'O', chr(195) . chr(153) => 'U',
            chr(195) . chr(154) => 'U', chr(195) . chr(155) => 'U',
            chr(195) . chr(156) => 'U', chr(195) . chr(157) => 'Y',
            chr(195) . chr(159) => 's', chr(195) . chr(160) => 'a',
            chr(195) . chr(161) => 'a', chr(195) . chr(162) => 'a',
            chr(195) . chr(163) => 'a', chr(195) . chr(164) => 'a',
            chr(195) . chr(165) => 'a', chr(195) . chr(167) => 'c',
            chr(195) . chr(168) => 'e', chr(195) . chr(169) => 'e',
            chr(195) . chr(170) => 'e', chr(195) . chr(171) => 'e',
            chr(195) . chr(172) => 'i', chr(195) . chr(173) => 'i',
            chr(195) . chr(174) => 'i', chr(195) . chr(175) => 'i',
            chr(195) . chr(177) => 'n', chr(195) . chr(178) => 'o',
            chr(195) . chr(179) => 'o', chr(195) . chr(180) => 'o',
            chr(195) . chr(181) => 'o', chr(195) . chr(182) => 'o',
            chr(195) . chr(182) => 'o', chr(195) . chr(185) => 'u',
            chr(195) . chr(186) => 'u', chr(195) . chr(187) => 'u',
            chr(195) . chr(188) => 'u', chr(195) . chr(189) => 'y',
            chr(195) . chr(191) => 'y',
            // Decompositions for Latin Extended-A
            chr(196) . chr(128) => 'A', chr(196) . chr(129) => 'a',
            chr(196) . chr(130) => 'A', chr(196) . chr(131) => 'a',
            chr(196) . chr(132) => 'A', chr(196) . chr(133) => 'a',
            chr(196) . chr(134) => 'C', chr(196) . chr(135) => 'c',
            chr(196) . chr(136) => 'C', chr(196) . chr(137) => 'c',
            chr(196) . chr(138) => 'C', chr(196) . chr(139) => 'c',
            chr(196) . chr(140) => 'C', chr(196) . chr(141) => 'c',
            chr(196) . chr(142) => 'D', chr(196) . chr(143) => 'd',
            chr(196) . chr(144) => 'D', chr(196) . chr(145) => 'd',
            chr(196) . chr(146) => 'E', chr(196) . chr(147) => 'e',
            chr(196) . chr(148) => 'E', chr(196) . chr(149) => 'e',
            chr(196) . chr(150) => 'E', chr(196) . chr(151) => 'e',
            chr(196) . chr(152) => 'E', chr(196) . chr(153) => 'e',
            chr(196) . chr(154) => 'E', chr(196) . chr(155) => 'e',
            chr(196) . chr(156) => 'G', chr(196) . chr(157) => 'g',
            chr(196) . chr(158) => 'G', chr(196) . chr(159) => 'g',
            chr(196) . chr(160) => 'G', chr(196) . chr(161) => 'g',
            chr(196) . chr(162) => 'G', chr(196) . chr(163) => 'g',
            chr(196) . chr(164) => 'H', chr(196) . chr(165) => 'h',
            chr(196) . chr(166) => 'H', chr(196) . chr(167) => 'h',
            chr(196) . chr(168) => 'I', chr(196) . chr(169) => 'i',
            chr(196) . chr(170) => 'I', chr(196) . chr(171) => 'i',
            chr(196) . chr(172) => 'I', chr(196) . chr(173) => 'i',
            chr(196) . chr(174) => 'I', chr(196) . chr(175) => 'i',
            chr(196) . chr(176) => 'I', chr(196) . chr(177) => 'i',
            chr(196) . chr(178) => 'IJ', chr(196) . chr(179) => 'ij',
            chr(196) . chr(180) => 'J', chr(196) . chr(181) => 'j',
            chr(196) . chr(182) => 'K', chr(196) . chr(183) => 'k',
            chr(196) . chr(184) => 'k', chr(196) . chr(185) => 'L',
            chr(196) . chr(186) => 'l', chr(196) . chr(187) => 'L',
            chr(196) . chr(188) => 'l', chr(196) . chr(189) => 'L',
            chr(196) . chr(190) => 'l', chr(196) . chr(191) => 'L',
            chr(197) . chr(128) => 'l', chr(197) . chr(129) => 'L',
            chr(197) . chr(130) => 'l', chr(197) . chr(131) => 'N',
            chr(197) . chr(132) => 'n', chr(197) . chr(133) => 'N',
            chr(197) . chr(134) => 'n', chr(197) . chr(135) => 'N',
            chr(197) . chr(136) => 'n', chr(197) . chr(137) => 'N',
            chr(197) . chr(138) => 'n', chr(197) . chr(139) => 'N',
            chr(197) . chr(140) => 'O', chr(197) . chr(141) => 'o',
            chr(197) . chr(142) => 'O', chr(197) . chr(143) => 'o',
            chr(197) . chr(144) => 'O', chr(197) . chr(145) => 'o',
            chr(197) . chr(146) => 'OE', chr(197) . chr(147) => 'oe',
            chr(197) . chr(148) => 'R', chr(197) . chr(149) => 'r',
            chr(197) . chr(150) => 'R', chr(197) . chr(151) => 'r',
            chr(197) . chr(152) => 'R', chr(197) . chr(153) => 'r',
            chr(197) . chr(154) => 'S', chr(197) . chr(155) => 's',
            chr(197) . chr(156) => 'S', chr(197) . chr(157) => 's',
            chr(197) . chr(158) => 'S', chr(197) . chr(159) => 's',
            chr(197) . chr(160) => 'S', chr(197) . chr(161) => 's',
            chr(197) . chr(162) => 'T', chr(197) . chr(163) => 't',
            chr(197) . chr(164) => 'T', chr(197) . chr(165) => 't',
            chr(197) . chr(166) => 'T', chr(197) . chr(167) => 't',
            chr(197) . chr(168) => 'U', chr(197) . chr(169) => 'u',
            chr(197) . chr(170) => 'U', chr(197) . chr(171) => 'u',
            chr(197) . chr(172) => 'U', chr(197) . chr(173) => 'u',
            chr(197) . chr(174) => 'U', chr(197) . chr(175) => 'u',
            chr(197) . chr(176) => 'U', chr(197) . chr(177) => 'u',
            chr(197) . chr(178) => 'U', chr(197) . chr(179) => 'u',
            chr(197) . chr(180) => 'W', chr(197) . chr(181) => 'w',
            chr(197) . chr(182) => 'Y', chr(197) . chr(183) => 'y',
            chr(197) . chr(184) => 'Y', chr(197) . chr(185) => 'Z',
            chr(197) . chr(186) => 'z', chr(197) . chr(187) => 'Z',
            chr(197) . chr(188) => 'z', chr(197) . chr(189) => 'Z',
            chr(197) . chr(190) => 'z', chr(197) . chr(191) => 's',
            // Euro Sign
            chr(226) . chr(130) . chr(172) => 'E',
            // GBP (Pound) Sign
            chr(194) . chr(163) => '');

        $string = strtr($string, $chars);
    }
    else
    {
        // Assume ISO-8859-1 if not UTF-8
        $chars['in'] = chr(128) . chr(131) . chr(138) . chr(142) . chr(154) . chr(158)
                . chr(159) . chr(162) . chr(165) . chr(181) . chr(192) . chr(193) . chr(194)
                . chr(195) . chr(196) . chr(197) . chr(199) . chr(200) . chr(201) . chr(202)
                . chr(203) . chr(204) . chr(205) . chr(206) . chr(207) . chr(209) . chr(210)
                . chr(211) . chr(212) . chr(213) . chr(214) . chr(216) . chr(217) . chr(218)
                . chr(219) . chr(220) . chr(221) . chr(224) . chr(225) . chr(226) . chr(227)
                . chr(228) . chr(229) . chr(231) . chr(232) . chr(233) . chr(234) . chr(235)
                . chr(236) . chr(237) . chr(238) . chr(239) . chr(241) . chr(242) . chr(243)
                . chr(244) . chr(245) . chr(246) . chr(248) . chr(249) . chr(250) . chr(251)
                . chr(252) . chr(253) . chr(255);

        $chars['out'] = "EfSZszYcYuAAAAAACEEEEIIIINOOOOOOUUUUYaaaaaaceeeeiiiinoooooouuuuyy";

        $string = strtr($string, $chars['in'], $chars['out']);
        $double_chars['in'] = array(chr(140), chr(156), chr(198), chr(208), chr(222), chr(223), chr(230), chr(240), chr(254));
        $double_chars['out'] = array('OE', 'oe', 'AE', 'DH', 'TH', 'ss', 'ae', 'dh', 'th');
        $string = str_replace($double_chars['in'], $double_chars['out'], $string);
    }

    return $string;
}

$str = "ľ š č ť ž ý á í é Č Á Ž Ý";
echo remove_accents($str); // Output: l s c t z y a i e C A Z Y
?>

Sending SMTP email as a group

In Google Apps you can create groups that redirect all incoming emails to the specified recipients. What if you also need to send emails as this group through SMTP? Here’s how to do it:

Step 1: Find out the IP address that’s gonna be using the SMTP

1
123.45.67.89

Step 2: Whitelist that IP on the Google Apps Admin Panel

1
Google Apps > Settings for Gmail > Advanced settings > Email whitelist > 123.45.67.89

Step 3: Whitelist that IP on the DNS SPF record:

1
v=spf1 ip4:123.45.67.89 include:_spf.google.com ~all

Step 4: SMTP configuration

1
2
3
4
5
Host: aspmx.l.google.com
Port: 25
Connection Type: open
Username: your.group@domain.com
Password: or a fake password (it will not be used)

More help here: http://support.google.com/a/bin/answer.py?hl=en&answer=176600

MySQL database size

1
2
3
4
5
6
SELECT table_schema "Data Base Name",
SUM( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
SUM( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

nginx serve subdirectory as domain root

Say you have the following file structure:

1
2
3
4
5
site.com/
  main_site/
  site_a/
  site_b/
  site_c/

If you want:

  • http://site.com to show /main_site/’s files
  • all remaining sites to be served as usual (example: http://site.com/site_b/)

All you have to do is to edit site.com.conf’s file as follows:

1
2
3
4
5
6
7
8
9
10
11
  location / {
    try_files $uri $uri/ @missing; # try to get site.com/requested_file, if does not exist jump to @missing
  }

  location /main_site {
    # do nothing
  }

  location @missing { # if file is not present on the root, serve the /main_site
    rewrite ^ /main_site$request_uri?;
  }

Source

MySQL delete duplicate lines (MyISAM and InnoDB)

Sometimes this task can be a real pain in the ass.. Heres a quick & dirty way to remove.

Let’s say you have the following table:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| col_a | varchar(10)      | YES  |     | NULL    |                |
| col_b | varchar(10)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

With the following data:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  3 | YY    | ZZ    |
|  4 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Note: On this example I’ll assume a unique row is identified by the pair “col_a” + “col_b”.

If you want to delete duplicate rows and you don’t care for the PRIMARY KEY or which of the rows gets selected, you can add a UNIQUE INDEX.

For MyISAM engine tables

1
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);

Doing this will automatically purge the duplicate rows instead of shouting an error because we’re using IGNORE.

Et voila:

+----+-------+-------+
| id | col_a | col_b |
+----+-------+-------+
|  1 | YY    | ZZ    |
|  2 | YY    | XX    |
|  5 | XX    | XX    |
+----+-------+-------+

Important Note on InnoDB engine tables

If your under MySQL 5.5/InnoDB this will not work due to a known “InnoDB index limitation“.

Luckily, someone at stackoverflow.com came up with a simple solution:

1
2
SET SESSION old_alter_table=1;
ALTER IGNORE TABLE `example_table` ADD UNIQUE INDEX `idx_uq` (`col_a`, `col_b`);