Admin Life Errors, Fixes, and Encounters


freebsd 11 – install php, apache 2.4, Cyrus-sasl, postfix, postfixadmin, imap, pop3, squirrelmail, SpamAssassin, clamav

feebsd-update fetch
freebsd-update install

update ports
portsnap fetch
portsnap extract

portsnap fetch update

Install Mysql
cd /usr/ports/databases/mysql57-server

Install Apache + Php
cd /usr/ports/www/apache24
make install clean

cd /usr/ports/lang/php56
make config
[X] MAILHEAD   Enable mail header patch

make install clean

cd /usr/ports/lang/php56-extensions
make install clean
[X] FTP         FTP support
[X] GD          GD library support
[X] IMAP        IMAP support
[X] MBSTRING    multibyte string support
[X] MCRYPT      Encryption support
[X] MYSQL       MySQL database support
[X] MYSQLI      MySQLi database support
[X] ZIP         ZIP support
[X] ZLIB        ZLIB support

cd /usr/ports/www/mod_php56
make config
[X] MAILHEAD   Enable mail header patch
make install clean

cp -rp /usr/local/etc/php.ini-production /usr/local/etc/php.ini

vi /usr/local/etc/apache24/httpd.conf
DirectoryIndex index.html
DirectoryIndex index.html index.htm index.php

AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps

Install Named
cd /usr/ports/dns/bind910
make install clean
vi /usr/local/etc/namedb/named.conf
listen-on       {; };
//      listen-on       {; };

vi /etc/resolv.conf
change to

vi /etc/rc.conf


cat /root/.mysql_secret

mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'test';

/usr/local/etc/rc.d/mysql-server restart

Install Cyrus-sasl
cd /usr/ports/security/cyrus-sasl2
make install clean

vi /usr/local/lib/sasl2/smtpd.conf
mech_list: PLAIN LOGIN

Install cyrus-sasl2-saslauthd
cd /usr/ports/security/cyrus-sasl2-saslauthd
make install clean

vi /etc/rc.conf

Install Postfix
cd /usr/ports/mail/postfix
make install clean
[X] MYSQL     MySQL database support
[X] SASL      Cyrus SASL support (Dovecot SASL is always built in)

Installing postfix-3.1.4,1...
===> Creating groups.
Using existing group 'mail'.
Creating group 'maildrop' with gid '126'.
Creating group 'postfix' with gid '125'.
===> Creating users
Creating user 'postfix' with uid '125'.
Adding user 'postfix' to group 'mail'.
Would you like to activate Postfix in /usr/local/etc/mail/mailer.conf [n]?n

echo 'postfix:  root' >> /etc/aliases
chown postfix:postfix /etc/opiekeys

pw groupadd vmail -g 500
pw useradd vmail -u 500 -g 500 -s /sbin/nologin -d /dev/null
mkdir /home/domains
chown -R vmail:vmail /home/domains
chmod -R ug+rwx,o-rwx /home/domains

mkdir /usr/local/etc/postfix/TLS
cd /usr/local/etc/postfix/TLS
openssl req -new -x509 -nodes -out smtpd.pem -keyout smtpd.pem -days 7300

Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:Hong Kong
Locality Name (eg, city) []:Hong Kong
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Jason Tang Ltd
Organizational Unit Name (eg, section) []:IT
Common Name (eg, YOUR name) []:Jason Tang
Email Address []: [email protected]

vi /usr/local/etc/postfix/
myhostname =
mydomain =
myorigin = $mydomain
mydestination = $myhostname localhost localhost.$mydomain
mynetworks =
inet_interfaces = all

virtual_mailbox_base = /home/domains/
virtual_mailbox_maps = mysql:/usr/local/etc/postfix/
virtual_mailbox_domains = mysql:/usr/local/etc/postfix/
virtual_alias_domains =
virtual_alias_maps = mysql:/usr/local/etc/postfix/

virtual_uid_maps = static:500
virtual_gid_maps = static:500

virtual_transport = maildrop
maildrop_destination_recipient_limit = 1
maildrop_destination_concurrency_limit = 1

message_size_limit = 51200000
virtual_mailbox_limit = 209715200

broken_sasl_auth_clients = yes
smtpd_sasl_auth_enable = yes
smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination
smtpd_sasl_local_domain = $mydomain
smtpd_sasl_security_options = noanonymous
smtpd_sasl_path = smtpd
smtpd_banner=$myhostname ESMTP $mail_name ($mail_version)

smtp_use_tls = yes
smtpd_use_tls = yes
smtp_tls_note_starttls_offer = yes
smtpd_tls_key_file = /usr/local/etc/postfix/TLS/smtpd.pem
smtpd_tls_cert_file = /usr/local/etc/postfix/TLS/smtpd.pem
smtpd_tls_CAfile = /usr/local/etc/postfix/TLS/smtpd.pem
smtpd_tls_loglevel = 1
smtpd_tls_received_header = yes
smtpd_tls_session_cache_timeout = 3600s
tls_random_source = dev:/dev/urandom

mime_header_checks = regexp:/usr/local/etc/postfix/mime_header_checks.regexp

vi /usr/local/etc/postfix/
smtps     inet  n       -       n       -       -       smtpd
-o smtpd_tls_wrappermode=yes
-o smtpd_sasl_auth_enable=yes
-o smtpd_client_restrictions=permit_sasl_authenticated,reject
-o milter_macro_daemon_name=ORIGINATING

vi /usr/local/etc/postfix/
user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = alias
select_field = goto
where_field = address
additional_conditions = AND active = '1'

vi /usr/local/etc/postfix/
user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = domain
select_field = description
where_field = domain
additional_conditions = AND active = '1'

vi /usr/local/etc/postfix/
user = postfix
password = postfix
hosts = localhost
dbname = postfix
table = mailbox
select_field = maildir
where_field = username
additional_conditions = AND active = '1'

vi /usr/local/etc/postfix/mime_header_checks.regexp
/filename=\"?(.*)\.(bat|cmd|com|pif|exe)\"?$/ REJECT

vi /etc/rc.conf

Install Sudo
cd /usr/ports/security/sudo
make install clean

vi /usr/local/etc/sudoers
www ALL=(vmail) NOPASSWD: /usr/local/bin/, /usr/local/bin/, /usr/local/bin/

Install Postfixadmin
cd /usr/ports/mail/postfixadmin
make install clean
[X] MYSQL  MySQL database support

1. Copy *.sh Files
cp -rp /usr/local/share/postfixadmin/ADDITIONS/postfixadmin*.sh /usr/local/bin
chmod 755 /usr/local/bin/postfixadmin*.sh

vi /usr/local/bin/

maildirmake "$maildir"
/usr/local/bin/maildirmake "$maildir"

vi /usr/local/bin/

vi /usr/local/bin/

2. Create the MySQL Database
mysql -u root -p
GRANT ALL ON postfix.* TO postfix@localhost IDENTIFIED BY "postfix";

3. Configure PostfixAdmin
vi /usr/local/www/postfixadmin/
$CONF['configured'] = true;
$CONF['database_type'] = 'mysqli';
$CONF['database_password'] = 'postfix';

$CONF['admin_email'] = '[email protected]';

$CONF['aliases'] = '0';
$CONF['mailboxes'] = '0';
$CONF['maxquota'] = '0';
$CONF['domain_quota_default'] = '0';

$CONF['domain_quota'] = 'NO';

$CONF['vacation'] = 'YES';
$CONF['vacation_domain'] = '';

$CONF['footer_text'] = 'Return to PostfixAdmin';
$CONF['footer_link'] = '';

$CONF['mailbox_postcreation_script']='/usr/local/bin/sudo -u vmail /usr/local/bin/';
// $CONF['mailbox_postedit_script'] = '';
$CONF['mailbox_postdeletion_script']='/usr/local/bin/sudo -u vmail /usr/local/bin/';
// $CONF['domain_postcreation_script'] = '';
$CONF['domain_postdeletion_script']='/usr/local/bin/sudo -u vmail /usr/local/bin/';

$CONF['new_quota_table'] = 'NO';

4. Configure Apache
vi /usr/local/etc/apache24/httpd.conf
Alias /postfixadmin "/usr/local/www/postfixadmin/"
<Directory "/usr/local/www/postfixadmin">
Options Indexes
AllowOverride ALL
Require all granted

/usr/local/etc/rc.d/apache24 restart

You should see a list of 'OK' messages.

Change setup password
Setup password           <-------- Input your Setup Password
Setup password (again)   <-------- Input your Setup Password again

And then click "Generate password hash"

If you want to use the password you entered as setup password, edit or config.local.php and set

$CONF['setup_password'] = '8c487e1722baa627e55712178141b21b:518761bc455e27d52eb1ed0faffecb8aeea2c90e';

vi /usr/local/www/postfixadmin/

$CONF['setup_password'] = '8c487e1722baa627e55712178141b21b:518761bc455e27d52eb1ed0faffecb8aeea2c90e';

You should see a list of 'OK' messages.

Create superadmin account
Setup password      <-------- Input your Setup password
Admin:              <-------- Input your Email address(Just for postfixadmin)
Password:           <-------- Input your Password
Password (again):   <-------- Input your Password again

And then click "Add Admin"

The admin [email protected] has been added!

You are done with your basic setup.

You can now login to PostfixAdmin using the account you just created.

mv /usr/local/www/postfixadmin/setup.php /usr/local/www/postfixadmin/setup.php.disabled
chmod 000 /usr/local/www/postfixadmin/setup.php.disabled

Install Courier-imap
cd /usr/ports/mail/courier-imap
make install clean
[X] AUTH_MYSQL   MySQL support

chmod +x /var/run/authdaemond

vi /usr/local/etc/authlib/authdaemonrc

vi /usr/local/etc/authlib/authmysqlrc
MYSQL_SERVER            localhost
MYSQL_USERNAME          postfix
MYSQL_PASSWORD          postfix
MYSQL_SOCKET            /tmp/mysql.sock
MYSQL_DATABASE          postfix
MYSQL_USER_TABLE        mailbox
MYSQL_UID_FIELD         '500'
MYSQL_GID_FIELD         '500'
MYSQL_LOGIN_FIELD       username
MYSQL_HOME_FIELD        concat('/home/domains/',maildir)
MYSQL_NAME_FIELD        name
MYSQL_MAILDIR_FIELD     concat('/home/domains/',maildir)
MYSQL_QUOTA_FIELD       concat(quota,'S')
MYSQL_WHERE_CLAUSE      active='1'

cp -rp /usr/local/etc/courier-imap/pop3d.cnf.dist /usr/local/etc/courier-imap/pop3d.cnf

cp -rp /usr/local/etc/courier-imap/imapd.cnf.dist /usr/local/etc/courier-imap/imapd.cnf


vi /usr/local/etc/courier-imap/pop3d-ssl

vi /usr/local/etc/courier-imap/imapd-ssl

vi /etc/rc.conf

Install Maildrop
cd /usr/ports/mail/maildrop
make WITH_AUTHLIB=yes install clean
[X] AUTH_MYSQL   MySQL support

vi /usr/local/etc/postfix/
maildrop  unix  -       n       n       -       -       pipe
flags=DRhu user=vmail argv=/usr/local/bin/maildrop -d ${recipient}

Install Squirrelmail
cd /usr/ports/mail/squirrelmail
make install clean

#for multiple language support
cd /usr/ports/mail/squirrelmail-translations
make install clean

#for multiple language support
chown -R www:www /usr/local/www/squirrelmail

vi /usr/local/etc/apache24/httpd.conf
Alias /webmail "/usr/local/www/squirrelmail/"
<Directory "/usr/local/www/squirrelmail">
Options Indexes
AllowOverride ALL
Require all granted

cd /usr/local/www/squirrelmail/config
cp config_default.php config.php



Install clamav
cd /usr/ports/security/clamav
make install clean
[X] MILTER        Compile the milter interface

vi /usr/local/etc/clamav-milter.conf
AddHeader Yes

vi /etc/rc.conf

/usr/local/etc/rc.d/clamav-clamd restart


Install SpamAssassin
cd /usr/ports/security/p5-IO-Socket-SSL
make config
[X] IDN        International Domain Names support

make install clean

cd /usr/ports/mail/spamassassin
make install clean


vi /usr/local/etc/mail/spamassassin/
rewrite_header Subject *****SPAM*****
report_safe 1
required_score 5.0
use_bayes 1
bayes_auto_learn 1

vi /etc/rc.conf
spamd_flags="-u spamd -H /var/spool/spamd"

/usr/local/etc/rc.d/sa-spamd restart
chown -R spamd:spamd /root/.spamassassin

cd /usr/ports/mail/spamass-milter
make install clean

vi /usr/local/etc/rc.d/spamass-milter
: ${spamass_milter_socket_mode="644"}
: ${spamass_milter_socket_mode="777"}

vi /etc/rc.conf

vi /usr/local/etc/postfix/
milter_connect_macros = b j _ {daemon_name} {if_name} {if_addr}
smtpd_milters =
milter_default_action = accept

Install Virtual Vacation
1. Create a local account
pw groupadd vacation -g 501
pw useradd vacation -u 501 -g 501 -s /sbin/nologin -d /nonexistent -c "Virtual Vacation"

2. Install
mkdir /var/spool/vacation
cp -rp /usr/local/share/postfixadmin/VIRTUAL_VACATION/ /var/spool/vacation/
#had to download from
chown -R root:vacation /var/spool/vacation
chmod -R 750 /var/spool/vacation

3. Setup the transport type
vi /usr/local/etc/postfix/
vacation  unix  -       n       n       -       -       pipe
flags=Rq user=vacation argv=/var/spool/vacation/ -f ${sender} -- ${recipient}

4. Setup the transport maps file
vi /usr/local/etc/postfix/
transport_maps = hash:/usr/local/etc/postfix/transport

vi /usr/local//etc/postfix/transport       vacation

/usr/local/sbin/postmap /usr/local/etc/postfix/transport

5. Configure
vi /var/spool/vacation/
#!/usr/bin/perl -X
#!/usr/local/bin/perl -X

our $db_type = 'mysql';
our $db_username = 'postfix';
our $db_password = 'postfix';
our $db_name     = 'postfix';
our $vacation_domain = '';

/usr/local/etc/rc.d/postfix reload

perl -MCPAN -e 'install DBI'

cd /usr/ports/databases/p5-DBD-mysql/
make install clean




Filed under: Errors No Comments

Send a test mail using Telnet

Telnet into the smtp server (postfix)

# telnet localhost 25

The server should reply with:

Connected to localhost.
Escape character is '^]'.
220 ESMTP Postfix

Great. Postfix is listening and wants us to speak SMTP:


Postfix appreciates the EHLO and tells us which features it provides:

250-SIZE 10240000
250 DSN

Hey, Postfix, we have a mail from [email protected]:

mail from:<[email protected]>

Looks like Postfix is happy with that because return codes that start with a '2' are good news:

250 2.1.0 Ok

Tell Postfix who the recipient of the mail is:

rcpt to:<[email protected]>

Postfix accepts that:

250 2.1.5 Ok

Then we are ready to send the actual mail:


Postfix agrees and tells us we can send the actual mail now and end our input with a dot on an empty line:

354 End data with <CR><LF>.<CR><LF>

Okay, type in the mail:

Hi John,

just wanted to drop you a note.

Postfix tells us it has received the mail and queued under a queue ID:

250 2.0.0 Ok: queued as AK782JKD4

Thanks, Postfix, we are done:

Filed under: Errors No Comments

ezjail / jail setup on freebsd 9.3

# cd /usr/ports/sysutils/ezjail
# make install clean

create a base jail

# ezjail-admin install -sp

update the basejail with freebsd-update
# ezjail-admin update -u

update the base jail's ports tree

# ezjail-admin update -P

rc.conf entry ensures our jail will be started at boot and gets the IP it needs.
# ifconfig em0 alias netmask 0xffffff00 broadcast
# echo 'ifconfig_em0_alias0="inet netmask 0xffffff00 broadcast"' >> /etc/rc.conf
# echo 'ezjail_enable="YES"' >> /etc/rc.conf

Create the jail
# ezjail-admin create
# cp /etc/resolv.conf /usr/jails/
# service ezjail start
to see running jail
# jls

to get console access to the jail
# ezjail-admin console
to stop a jail
# ezjail-admin stop

to archive for you to copy a jail
# ezjail-admin archive

The archived file should appear in /usr/jails/ezjail_archives. you can then duplicate it as many times as you want. If you install ports, don't bother copying the jail to another machine, there will be missing libraries...
# ezjail-admin create -a /usr/jails/ezjail_archives/
# ezjail-admin start
Filed under: Errors No Comments

freebsd 9.3 error warning: smtputf8_enable is true, but EAI support is not compiled in

if you are getting this error "warning: smtputf8_enable is true, but EAI support is not compiled in" on freebsd 9.3 (maybe freebsd 10) it's a bug in the port config. EASY way to fix it (well best way to fix it) is to compile postfix with EAI support.

# cd /usr/ports/mail/postfix-current/
# make config

Make sure you select EAI from the list

# make install clean
# make reinstall clean

Filed under: Errors No Comments

How to build php5 module into apache22 with freebsd

The Apache module is no longer inside ports/lang/php5. there is no option to build php with apache.

The fix:

use www/mod_php5 to get apache to run php

Also note that

The Apache PHP module has been separated from the main PHP port. If you had the APACHE OPTION selected, you have to perform the following steps:

1) update your lang/php* options (i.e. 'make config')

2) rebuild lang/php* port

3) install www/mod_php* port

It is mandatory to build both ports with the same DEBUG and ZTS options, so if you have a threaded Apache (i.e. worker or event MPM) you have to select the ZTS option in lang/php* port.


if you have this error

===>  mod_php55-5.5.17_1 is marked as broken: : Error from apache22 is installed (or APACHE_PORT is defined) and port requires apache.
*** [install] Error code 1

then make sure you add this line to /etc/make.conf

Filed under: Errors No Comments

5 Thrifty Tips for Driving Sales with Online Video

If a picture is worth a thousand words, a moving picture adds to that figure exponentially. As a dynamic and immediate marketing tool, video is the next best thing to greeting potential customers face-to-face. Plus, not only can videos help deliver traffic from search engines like Google, but video thumbnails tend to catch the eye in search results better than linked text.

But if a slick, professionally produced clip isn’t within your budget, that’s ok – today’s affordable web and phone cams make it inexpensive to create do-it-yourself videos that look and sound clean enough to get your message across in a professional way.

Online video powerhouse YouTube is the world’s second-largest search engine, reaching 4 billion views per day in January. And as a free service, you don’t need budget to upload videos. That means, all you need to start connecting with your audience is a camera, a story and a plan.

Don’t know where to start? Here are five tips for messaging and presentation of your video:

Tip 1 – Make it unique:  Because the chances are there are already countless videos out there related to your field, it’s important to make your message as unique as possible. Offer hard-to-find information that satisfies a real customer need. Don’t discuss what you want to. Focus on what they want to learn about.
Tip 2 – Make it friendly:  Keep your tone light and conversational. Try to make a personal connection. Present a real-life situation, and address it with a demo of your product. Entertain if you can.
Tip 3 – Make it quick:  Bear in mind the attention span of the viewer. Deliver the message as succinctly as possible – 5 minutes or less for best results. For in-depth or supplemental info, link to a blog post. (You have a blog, right? No? No worries – you can easily start one with the free WordPress blog capability we include in our discounted hosting packages.)
Tip 4 – Make a deal:  Share a video coupon, a special code for a discount or deal for those who watch the video to its end.
Tip 5 – Make it viral:  Upload it to YouTube; post it on Twitter and Facebook. Do whatever you can to make it spread. Craft your message with this in mind: Be funny, be poignant, be memorable.

For other tips, see our blog –

Filed under: Errors No Comments

eAccelerator freebsd error shmmax

Freebsd has an issue with eAccelerator right now where when you try to start it it gives you an error "eAccelerator: shmmax should be at least 2MBPHP Warning"

To fix this we're going to remove eAccelerator then reinstall it:

# cd /usr/ports/www/eaccelerator

# make deinstall

# vi MakeFile

The "CONFIGURE_ARGS=" line needs to be changed so it looks like this
CONFIGURE_ARGS= --with-eaccelerator-shared-memory \
--with-eaccelerator-userid=80 \

save the file and exit

# make install clean

restart apache

Filed under: Errors, FreeBSD No Comments

How To Design Your Site Like a Pro

Think of the websites you visit the most — chances are they are clean, professional, and easy to navigate. This is no coincidence: the human mind takes just a millisecond to make a definitive decision as to a site's credibility, and this initial judgment is based entirely on aesthetics which means a good web design is critical for retaining visitors.

This month we consulted with Netfirms's very own Director of Web and UI, he shared with us the best tips and tricks for designing the most visually appealing website:

1) Plan Your Site

    : Before turning on your computer, consider the layout, page structure and how users will move from one page to another (navigation). Then, sketch out your site on paper.

2) Understand Your Audience

    : Knowing your audience will help set the tone for your content and layout, as you identify what visitors will be looking for on your site. It's also important to consider popular web browsers and your visitor's screen resolution size so that they see your site the way you intended; you can find this data in most site analytics tools. If you're not sure how wide to make your site, 960px is a great place to start.

3) Allow for Easy Navigation

    : Since people often read from left to right and top to bottom, the upper left-hand corner of your site is the first place your visitors will look. Place your navigation buttons here and keep them consistent, along with font and color choices, for easy reading and fluid movement from page to page.

4) Choose a Color Scheme

      : If you're designing your site around an image, try uploading it to a color palette tool, like

    's, for example, for complementary color suggestions. Be sure to remain consistent with your design patterns, and stick to no more than three font types and colors.

5) Use High Quality Photos

      : In order to give your site a more professional appearance, all images should be clear, focused, and of high resolution. An alternative to taking your own photos is buying them from

    ; at about five-dollars an image it's an inexpensive way to visually take your website to the next level.

6) Test Your Site, Then Test Again

    : When you've created or redesigned your site, ask friends and family to test it for you. They may find things you've missed, or point out areas of confusion.

When paired with quality content, executing these tips appropriately will help retain first-time visitors and transform them into regulars.


Thanks for netfirms for the great tips

Filed under: Errors No Comments

Apache freebsd munin not working together

I came across an issue with Apache 2.2 on freebsd 8.2 where munin wasn't graphing the Apache data. (apache_accesses, apache_processes and apache_volume )

When I tailed the munin error log I found this error.

tail /var/log/munin/munin-node.log
munin UserAgent not found at apache_volume line 86.
With some researching on google I found this nice munin command to tell me more about how to setup munin. IE what we can install on freebsd.

/usr/local/sbin/munin-node-configure --suggest
The output gave us "LWP::UserAgent not found". which means, install Perl's libwww.

To fix this error we install p5-libwww

cd /usr/ports/www/p5-libwww/
make install clean

Restart munin and everything should now work.

# ./usr/local/etc/rc.d/munin-node restart



PHP MySQL injection

Here is an example of how an SQL injection that was found, monitored, and fixed!

When dealing with a large website with legacy code sometimes it's not possible to check every query after one starts.

One day while reviewing a new clients slow quries via MySQL. I started to notice these types of quries showing up.

# User@Host: test_db[test_db] @ []
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 48648
select * from serv_main where id = 71261 and 3=8 union select 1,2,3,concat(0x232425,ifnull(`idClient`,0x4E554C4C),char(9),ifnull(`username`,0x4E554C4C),char(9),ifnull(`pass`,0x4E554C4C),char(9),0x252423),5,6,7,8,9,10,11,12,13,14,15,16 from `test_db`.`private` where idClient>2119021 limit 16215,1 --;

# User@Host: test_db[test_db] @ []
# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 48654
select * from serv_main where id = 71261 and 3=8 union select 1,2,3,concat(0x232425,ifnull(`idClient`,0x4E554C4C),char(9),ifnull(`username`,0x4E554C4C),char(9),ifnull(`pass`,0x4E554C4C),char(9),0x252423),5,6,7,8,9,10,11,12,13,14,15,16 from `test_db`.`private` where idClient>2119021 limit 16217,1 --;

Knowing the table serv_main I knew the developer that wrote that code did not use a UNION.
After a few moments of thinking what was going on I tailed Apaches access log and confirmed what I thought. - - [1/Jan/2010:10:08:40 -0500] "GET /viewAd.php?id=71261%20and%203%3D8%20union%20select%201,2,3,concat%280x232425,ifnull%28%60idClient%60,0x4E554C4C%29,char%289%29,ifnull%28%60username%60,0x4E554C4C%29,char%289%29,ifnull%28%60pass%60,0x4E554C4C%29,char%289%29,0x252423%29,5,6,7,8,9,10,11,12,13,14,15,16%20from%20%60test_db%60.%60private%60%20where%20idClient%3E2119021%20limit%2034642,1%20-- HTTP/1.1" 200 1093 "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv: Gecko/20091221 Firefox/3.5.7 (.NET CLR 3.5.30729)"

It was an SQL Injection attack. so I did the next logical step (to me at least). Block the offender.

I did this by adding his IP to the .htaccess file along with a bigger subnet block.
Order allow,deny
deny from
deny from 120.2.194.
allow from all

Now that the Injections had stopped, the next step was to figure out where it was done, how it was done, and how to fix it.

WHERE was it done)
That was easy to find, it's right there in the access log viewAd.php

How was it done)
$sql = "select * from serv_main where id = " . $_GET['id'];
$result = mysql_query($sql);

That was the easy part, and the hard part.

The EASY part was just to just use mysql_real_escape_string.

we have 2 quick options here.

Option 1.

$_GET['id'] = (int) $_GET['id']; //cat whatever is passed into id as an integer.

Option 2.

$sql = "select * from serv_main where id = " . mysql_real_escape_string($_GET['id']); //escape user input
$result = mysql_query($sql);

The HARD part, fix the whole site...
Not having time to check 1000's of quries, here was what I did in a 30 mintue span.

1) include a function somewhere in a global include that can clean all input (or cast all input)
a good place was in our db_include.php file.

function sanitize_user_input(&$input) {

$int_keys = array ('id', 'item', 'item_id' );

foreach ( $int_keys as $key ) {
if ( isset($input[$key]) ) {
if ( !empty($input[$key]) && !is_numeric($input[$key]) )
$input[$key] = (int) $input[$key];

if (isset($_GET)) sanitize_user_input($_GET);

The hard part was easier than I thought.

But the truly hard part started, going though the 1000's of queries to fix them.

Some interesting points and notes.

# 0x232425 = #$%
# 0x252423 = %$#
# 0x4E554C4C = NULL
# 0x4E554C4C = NULL
# CHAR(9) = Tab
# CHAR(10) = Line feed
# CHAR(13) = Carriage return
select concat(0x232425,

Filed under: Errors, MySQL, PHP No Comments