Automatically update phpMyAdmin

I’m running phpMyAdmin to manage the MySQL databases for the hosting I manage, and I need to keep it up to date to avoid vulnerabilities, bugs, etc.

Or mainly because I want to see up to date in the version box.

I run this script weekly to keep my version up to date:

#!/usr/bin/php
<?php

$cmd = "cd /home/stephan/www/secret_folder/hidden; git clone --depth=1 --branch=STABLE git://github.com/phpmyadmin/phpmyadmin.git && cp -r phpmyadmin/* MyPHPMyAdmin/ && rm -rf phpmyadmin";
shell_exec($cmd);

I keep phpMyAdmin in an hidden folder, protected by a password, because a lot of scripts try to access it.

So, if the URL of your phpMyAdmin instance is

https://mywebsite.ch/secret_folder/hidden/MyPHPMyAdmin,

and is stored in

/home/stephan/www/secret_folder/hidden/MyPHPMyAdmin

the script above will fetch the latest stable release, and copy it OVER your existing version, to keep all your settings intact.

Run it via cron and you’re done. This script has been running for more than 1 year without any problem.

 

Check for new Dropbox folders on Linux

For a customer, I created a service on a remote server that processes files delivered via Dropbox. Problem is Dropbox on Linux will sync all the folders in its root folder, unless it’s excluded. You can exclude all folders except the one you’re interested in, but as soon as you add a folder to your Dropbox, it will appear on your Linux server.

This script will warn you if a new folder appears. It doesn’t exclude automatically new folders, but this feature could be added if you’re brave enough.

#!/usr/bin/perl
# When using Dropbox on Linux, the complete dropbox folder is 
# sync'ed by default, which can use precious disk space if 
# we only need some folders.
# Because we cant choose which folders will be sync'ed on
# Linux, we can only exclude folders we don't want. So this script
# reports when a new folder is added to the Dropbox top folder.
# A nice feature would be to be able to only allow some folders.
# Note: since we can't exclude files, they are not reported.
# Dont add a large file to the root of Dropbox, you can't exclude it from syncing.

# if this script finds folders not in the allowed list, it sends
# an email and a notification, in case the mail is flagged as spam.

# I choose not to exclude new folders directly in this script,
# in case something breaks. This script is run on a server used by
# a customer as a WebService endpoint, so better be safe.

# To exclude a folder from syncing, use the dropbox-cli script available at
# https://www.dropbox.com/download?dl=packages/dropbox.py
# then do
# ./dropbox.py exclude add "Folder to exclude"
#
# Coriolis Stephan Burlot, Apr 11, 2018

use strict;
use Data::Dumper;
use MIME::Lite;
use WebService::Prowl;

## the path to the Dropbox folder
my $dropbox_folder = '/home/stephan/Dropbox/';

## email settings
my $email_address = 'EMAIL_ADDRESS';

## I use Prowl (prowlapp.com) to send notifications to my phone.
## prowl settings
my $prowl_api_key = 'PROWL_API_KEY';

## Allowed folders
# famous last words:
# customer: "the folder is named TEST_Service, we'll change the
# name when we go in production."
my @allowed_folders = qw/TEST_Service/;

#################################
## sends a email with the message passed as parameter
sub send_email($) {
  my $content = shift @_;
  
  my $msg = MIME::Lite->new(
    From  => $email_address,
    To    => $email_address,
    Subject => 'Dropbox Bot',
    Data  => $content
  );
  $msg->send;
}

#################################
## sends a notification via Prowl
sub send_notification($$$) {
  my ($app, $event, $message) = @_;
  if ($event eq "") {
    $event = ' ';
  }
  
  # grab your API key from prowlapp.com
  my $ws = WebService::Prowl->new(apikey => $prowl_api_key);
  $ws->verify || die $ws->error();
  $ws->add(application => "$app",
       event     => "$event",
       description => "$message",
       url     => "");

}

#################################
## MAIN
#################################

# I dont use smartmatch, ie
# if ($file ~~ @allowed_folders)
# so I create a hash for simple matching.
my %allowed = map { $_ => 1 } @allowed_folders;

chdir $dropbox_folder;
if (opendir(my $dh, $dropbox_folder)) {
  my @folders = grep !/^\./, readdir($dh);
  closedir $dh;
  
  # array of bad folders
  my @bad = map { -f $_ || exists $allowed{$_} ? (): $_ } @folders;
  if (scalar(@bad) != 0) {
    print "New folders: " . join(", ", @bad) . "\n";
    send_notification('Linode_Small', 'Dropbox Bot', "There are new folders in Dropbox: you should exclude them.");
    send_email("Hello,\n\nI found these new folders in Dropbox:\n\n" . join("\n", @bad) . "\n\nThey should be excluded.\n");
  }
} else {
  send_notification('Linode_SMALL', 'Dropbox Bot', "I cant open Dropbox folder. Is it still there?");
  send_email("Hello,\n\nI can't opendir $dropbox_folder\n\nIs Dropbox still here?");
  die "Can't opendir $dropbox_folder: $!\n";
}

Enjoy.

Configuring Nginx for HTTPS access

If you manage nginx servers and get the error: SSL_ERROR_RX_UNEXPECTED_NEW_SESSION_TICKET in Firefox or ERR_SSL_PROTOCOL_ERROR in Chrome when connecting to your website:

Error when connecting via Firefox
Error when connecting via Firefox

 

Error when connecting via Chrome
Error when connecting via Chrome

Make sure your config has the following:

ssl_session_timeout 1d;
ssl_session_cache shared:SSL:10m;
ssl_session_tickets off;

To be sure, add these params to your http{} blocks, in nginx.conf.

I had these settings in all my virtual servers configuration file for https sites and it worked, but as soon as I added 1 certificate, I had this error. Adding the ssl_session settings to nginx.conf solved this.

curl* will report:

curl: (35) gnutls_handshake() failed: An unexpected TLS packet was received.

* Not all versions of curl will report this: on MacOS 10.13.3, curl v7.54.0 doesnt report an error. On Ubuntu 16.04, curl v7.47.0 reports this error.

source

Tweet Nest support for 280 chars

If you use Tweet Nest to keep an archive of all your tweets, you need a few changes to have all your long tweets stored.

I’ve made a quick hack to solve this temporarily:

– Change the text column of tn_tweets to varchar(512) (if twitter changes the limit again…)
– in the class.twitter.api.php file, replace (at the top):

public $dbMap = array(
  "id_str"       => "tweetid",
  "created_at"   => "time",
  "text"         => "text",
  "source"       => "source",
  "coordinates"  => "coordinates",
  "geo"          => "geo",
  "place"        => "place",
  "contributors" => "contributors",
  "user.id"      => "userid"
);

with

public $dbMap = array(
	"id_str"       => "tweetid",
	"created_at"   => "time",
	"full_text"    => "text",
	"text"         => "text",
	"source"       => "source",
	"coordinates"  => "coordinates",
	"geo"          => "geo",
	"place"        => "place",
	"contributors" => "contributors",
	"user.id"      => "userid"
);

I added a mapping between full_text to text because twitter returns the 280 chars tweets in the full_text field.

in the loadtweets.php file, line 127, add this line:

$params['tweet_mode'] = 'extended';

(before the

$data = $twitterApi->query('statuses/user_timeline', $params);

line)

so Twitter returns the extended tweets.

That’s all.

I posted this as a comment on the TweetNest repo : https://github.com/graulund/tweetnest/issues/91

Custom function in SQLite with fmdb

I’ve used Gus Mueller’s fmdb SQLite wrapper in most of my iOS projects and I’m in the process of migrating an app from Objective-C to Swift.

In this app, I needed a custom function for SQLite to compute the Haversine distance (giving great-circle distances between two points on a sphere from their longitudes and latitudes)

In all its glory*, here’s how I did it:

db.makeFunctionNamed("distance", arguments: 4) { context, argc, argv in
    guard db.valueType(argv[0]) == .float || db.valueType(argv[1]) == .float || db.valueType(argv[2]) == .float || db.valueType(argv[3]) == .float else {
        db.resultError("Expected double parameter", context: context)
        return
    }
    let lat1 = db.valueDouble(argv[0])
    let lon1 = db.valueDouble(argv[1])
    let lat2 = db.valueDouble(argv[2])
    let lon2 = db.valueDouble(argv[3])

    let lat1rad = DEG2RAD(lat1)
    let lat2rad = DEG2RAD(lat2)

    let distance = acos(sin(lat1rad) * sin(lat2rad) + cos(lat1rad) * cos(lat2rad) * cos(DEG2RAD(lon2) - DEG2RAD(lon1))) * 6378.1

    db.resultDouble(distance, context: context)
    
}

db is an FMDatabase, obviously.

This is how I use it:

let rs: FMResultSet? = db.executeQuery("SELECT ID, NO_BH, LAT, LONG, distance(?, ?, LAT, LONG) as distance FROM bh ORDER BY distance LIMIT 50", withArgumentsIn: [location.coordinate.latitude, location.coordinate.longitude])

Which gives me the 50 nearest points from location.

*Any improvement, remark greatly appreciated!

Hide Xcode 8 console garbage when running the simulator

Since Xcode 8, a lot of debug info appear in the console when using the iOS simulator:

2016-10-24 15:07:11.051609 sosasthma[19813:6302216] subsystem: com.apple.siri, category: Intents, enable_level: 1, persist_level: 1, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 0, privacy_setting: 0, enable_private_data: 0

2016-10-24 15:07:11.070089 sosasthma[19813:6302540] subsystem: com.apple.UIKit, category: HIDEventFiltered, enable_level: 0, persist_level: 0, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 1, privacy_setting: 2, enable_private_data: 0

2016-10-24 15:07:11.080159 sosasthma[19813:6302540] subsystem: com.apple.UIKit, category: HIDEventIncoming, enable_level: 0, persist_level: 0, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 1, privacy_setting: 2, enable_private_data: 0

2016-10-24 15:07:11.089886 sosasthma[19813:6302537] subsystem: com.apple.BaseBoard, category: MachPort, enable_level: 1, persist_level: 0, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 0, privacy_setting: 0, enable_private_data: 0

2016-10-24 15:07:11.101244 sosasthma[19813:6302216] subsystem: com.apple.UIKit, category: StatusBar, enable_level: 0, persist_level: 0, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 1, privacy_setting: 2, enable_private_data: 0

2016-10-24 15:07:11.134 sosasthma[19813:6302216] [Crashlytics] Version 3.7.2 (112)

2016-10-24 15:07:11.174840 sosasthma[19813:6302537] subsystem: com.apple.libsqlite3, category: logging, enable_level: 0, persist_level: 0, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 1, privacy_setting: 2, enable_private_data: 0

2016-10-24 15:07:11.185172 sosasthma[19813:6302549] subsystem: com.apple.network, category: , enable_level: 0, persist_level: 0, default_ttl: 0, info_ttl: 0, debug_ttl: 0, generate_symptoms: 0, enable_oversize: 0, privacy_setting: 2, enable_private_data: 0

To avoid having the console filled with info about siri, UIKIt, etc., just add

OS_ACTIVITY_MODE = disable

to your scheme in Product->Scheme->Edit Scheme
screen-shot-2016-10-24-at-15-06-45

Now you’ll have only your NSLog infos in your console. That’s enough garbage for a developer.

Source: Stack Overflow

Apache htaccess file for .ipa files

To allow my customers to download my iOS apps signed with AdHoc or Entreprise certificates, I use this htaccess file:

<FilesMatch "\.(ipa|plist)$">
 FileETag None
 <ifModule mod_headers.c>
 Header unset ETag
 Header set Cache-Control "max-age=0, no-cache, no-store, must-revalidate"
 Header set Pragma "no-cache"
 Header set Expires "Wed, 11 Jan 1984 05:00:00 GMT"
 </ifModule>
</FilesMatch>

AddType application/octet-stream .ipa
<Files *.ipa>
 Header set Content-Disposition attachment
</Files>

The “Caches” directives are not mandatory, but large customers are usually behind a reverse proxy and I want to avoid side effects, if possible.

So far, so good.

Nouvelles Prestations

Au cours de mes projets, je créé et j’administre des serveurs web pour fournir des contenus aux applications mobiles iOS (iPhone, iPad). Parallèlement à mon activité de développeur freelance, je gère également les serveurs d’une cinquantaine de site de PME et d’indépendants pour une société de web design partenaire.

J’ai décidé d’officialiser cette activité d’administrateur de sites et j’ai créé la structure Service Web qui permet aux clients qui produisent du contenu avec une grande facilité – sur WordPress notamment – mais qui sont complètement perdus avec les notions de DNS, de boîtes IMAP, de mise à jour ou de backup, de se concentrer sur leur activité sans avoir à se préoccuper des détails techniques. Ces services ne sont jamais inclus dans les offres d’hébergement, car ils requièrent un minimum d’implication dans la structure d’un site web.

Ma solide expérience d’administrateur web me permet aujourd’hui de créer cette nouvelle structure qui propose de fournir ces prestations professionnelles en terme d’hébergement et de maintenance sous la forme d’offres « clés en mains » accessibles à tous: Service Web

Liste des appels Swisscom

(English version available here)

Ici en Suisse, mon opérateur téléphonique (Swisscom) a une option qui me permet de voir la liste des 20 derniers appels reçus ou manqués sur la page web de mon compte.

Ne désirant pas me connecter à cette page a chaque fois que je veux consulter cette liste afin de vérifier qui m’a appelé, j’ai écris quelques scripts qui récupèrent le contenu de cette page et l’affiche sur mon iPhone via une application iOS. Les noms sont récupérés depuis le carnet d’adresse et une notification est envoyée (via Prowl) quand un appel est reçu mais personne ne répond.

screen

Cette application iOS ne peut être distribuée en l’état sur l’AppStore car:

  • J’ai besoin des identifiants et mot de passe du compte Swisscom pour accéder à ces données et je n’aurai pas confiance dans un service qui me demanderait ces infos. Comme vous, je suppose.
  • La méthode utilisée pour récupérer la liste des appels n’est pas une méthode officielle et peut (comme cela est déjà arrivé) ne plus fonctionner si Swisscom change l’aspect de la page web.
  • L’application iOS peut rechercher le nom d’après un numéro inconnu via un appel à Local.ch (qui est plus ou moins l’annuaire suisse en ligne officiel). Bien sûr, il n’y a pas d’API officielle pour ce service et ils peuvent couper l’accès à tout moment. Soyez sympa et n’abusez pas de service.

J’ai mis le source à disposition sur GitHub: https://github.com/sburlot/phonecalls

Pour Swisscom

Si vous travaillez pour Swisscom, ou connaissez quelqu’un qui y travaille, demandez une méthode publique pour accéder à ces données, en proposant un login authentifié. Ca serait pratique et permettrait à cette app (et à d’autres!) d’être disponible pour le grand public.

Ou Swisscom pourrait ajouter cette fonctionnalité à leur application officielle,

Ou Swisscom pourrait m’embaucher pour implémenter cette fonctionnalité dans leur application. Je suis développeur freelance et disponible!

Quelques détails techniques

Sur le serveur, un script Perl va récupérer les données (via cron) du portail Swisscom et les enregistre dans une base MySQL (n’importe quelle base fonctionnerait).

L’application iOS appelle un script PHP sur le serveur: le serveur va récupérer les données de la base et renvoie un objet JSON.

Amusez-vous bien!

PS: bien sûr, je peux attendre d’être chez moi pour consulter les appels en absence sur mon téléphone, mais c’est moins geek et drôle. 😉

(Ceci est une version plus longue du post précédent en anglais)

Swisscom Phonecalls

(Version française disponible ici)

Here in Switzerland the major telco (Swisscom) has an option to let you see your last 20 answered and missed calls on your account webpage.

Not wanting to log in every time I want to check if I missed a call, I wrote some scripts and that fetches the content of my account web page and display them on an iOS app.

The names are fetched from the iPhone address book, and the iPhone receives a notification via Prowl when a call is missed.

screen

Or perhaps all this was an excuse to write some code during the holidays. Who knows.

I can’t make this app an official app because:

  • I need the Swisscom credentials to access the portal and I wouldn’t trust a stranger and give them access to my account. As you do.
  • The process of fetching the list of calls is not an officially approved method and may break (and did) when Swisscom changes the layout of the page.
  • The iOS app can also retrieve the owner of an unknown number via a call to the local.ch (more or less the official swiss white pages site). Of course, the API endpoint is not official nor public. Please be nice and don’t abuse this unofficial endpoint.

So I made the source available on GitHub: https://github.com/sburlot/phonecalls

For Swisscom

Oh, if you work for Swisscom, or know someone who does, ask for a method to fetch this data with a secure login option. That would be great, because I could make this app available via the AppStore.

Or Swisscom could add this feature to their official app. PLEASE.

Or Swisscom could hire me to implement this feature in their official app. I’m a freelance developer and I’m available!

Some tech details

On the server a Perl script fetches the data (via cron) from the Swisscom and stores the numbers in a MySQL database (any DB would work).
The iOS app calls a PHP script on the server to access the data: the PHP scripts fetches the data from the database and returns a JSON object.

Have fun!

PS: of course I can wait to return home and check my calls on my phone, but where’s the fun? 😉

I wrote this app 2 years ago and I still use it.

(this is a longer version of the original post)