Libre Things

CV Diaspora
fr

Sunday 13 November 2011

Import CSV file to MySQL

Here is the little sh script I made to do that. The LOAD DATA INFILE command exists but is not capable of creating the table structure.

#!/bin/sh

MYSQL_ARGS="--defaults-file=/etc/mysql/debian.cnf"
DB="mbctest"
DELIM=";"

CSV="$1"
TABLE="$2"

[ "$CSV" = "" -o "$TABLE" = "" ] && echo "Syntax: $0 csvfile tablename" && exit 1

FIELDS=$(head -1 "$CSV" | sed -e 's/'$DELIM'/` varchar(255),\n`/g' -e 's/\r//g')
FIELDS='`'"$FIELDS"'` varchar(255)'

#echo "$FIELDS" && exit

mysql $MYSQL_ARGS $DB -e "
DROP TABLE IF EXISTS $TABLE;
CREATE TABLE $TABLE ($FIELDS);

LOAD DATA INFILE '$(pwd)/$CSV' INTO TABLE $TABLE
FIELDS TERMINATED BY '$DELIM'
IGNORE 1 LINES
;
"

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

(See comment: "Posted by John Swapceinski on September 5 2011 5:33am")

Tuesday 14 June 2011

PHP escapeshellarg function, UTF8 and locales

The PHP escapeshellarg function depends on your current locale. I think it's bad, but PHP developers made this choice. If like me your default locale is 'C' you lose all UTF8 characters.

They suggest you to call something like setlocale(LC_CTYPE, "en_US.UTF-8"). It doesn't work if the en_US.utf8 locale is not installed on your system. Of course maybe you have the fr_FR.utf8, or de_DE.utf8, but you will have to try all of them until you find one utf8 matching locale. And if there is not, you're screwed. It's also bad if you want code that runs everywhere.

Simply use that:

$escapedArg = "'".str_replace("'", "'\\''", $arg)."'";

It will do the same as the escapeshellarg function: replace yourstringthat'slong by

'yourstringthat'\''slong'

as described in the escapeshellarg manual (and I also looked into the PHP source code to be sure).

See also:

Sunday 13 March 2011

A very simple mysqldump script to backup your databases

Here are some lines I'm using to backup my MySQL databases on my Debian server:

#!/bin/sh
# This will dump all your databases

DATE=$(date +%Y%m%d%H%M)

for DB in $(echo "show databases" | mysql --defaults-file=/etc/mysql/debian.cnf -N)
do
        mysqldump --defaults-file=/etc/mysql/debian.cnf $DB > /backup/mysql/${DB}_${DATE}.sql

        gzip /backup/mysql/${DB}_${DATE}.sql
done

# purge old dumps
find /backup/mysql/ -name "*.sql*" -mtime +8 -exec rm -vf {} \;

You can run it in a cron:

11 1 * * * /usr/local/bin/mysqldump.sh > /tmp/mysqldump.log

This way any error displayed by the script will be sent by mail to the root user (mail address in /etc/aliases).

If you are not under Debian and there is no password file in /etc/mysql, you should create such file.

Thursday 13 January 2011

positon.42 and dnsmasq as a DNS proxy to resolve .42 domains

I am now the owner of a .42 domain !

http://positon.42

.42 are not official domains (yet). Official top level domains are managed by ICANN and served by root DNS servers.

To resolve .42 domains, you have to query a DNS server knowing .42 domains.

Here is my small contribution to the 42registry.org wiki, explaining a way to configure it with Ubuntu:

---

The following allows you to use Geeknode DNS only for .42 domains while keeping your regular DNS provider for other domains.

Install dnsmasq :

aptitude install dnsmasq

Edit /etc/dnsmasq.conf and add the line :

server=/42/81.93.248.69

Restart dnsmasq :

sudo /etc/init.d/dnsmasq restart

Edit /etc/dhcp3/dhclient.conf, then uncomment or add the line :

prepend domain-name-servers 127.0.0.1;

Now disconnect and reconnect to you local network, to refresh the /etc/resolv.conf file, then test it !

---

Links:

Wednesday 29 December 2010

Rsync command restriction over SSH

You have 2 systems and you want to set up a secure backup with rsync + SSH of one system to the other.

Very simply, you can use:

backup.example.com# rsync -avz --numeric-ids --delete root@myserver.example.com:/path/ /backup/myserver/

To do the backup, you have to be root on the remote server, because some files are only root readable.

Problem: you will allow backup.example.com to do anything on myserver.example.com, where just read only access on the directory is sufficient.

To solve it, you can use the command="" directive in the authorized_keys file to filter the command.

To find this command, start rsync adding the -e'ssh -v' option:

rsync -avz -e'ssh -v' --numeric-ids --delete root@myserver.example.com:/path/ /backup/myserver/ 2>&1 | grep "Sending command"

You get a result like:

debug1: Sending command: rsync --server --sender -vlogDtprze.iLsf --numeric-ids . /path/

Now, just add the command before the key in /root/.ssh/authorized_keys:

command="rsync --server --sender -vlogDtprze.iLsf --numeric-ids . /path/" ssh-rsa AAAAB3NzaC1in2EAAAABIwAAABio......

And for even more security, you can add an IP filter, and other options:

from="backup.example.com",command="rsync --server --sender -vlogDtprze.iLsf --numeric-ids . /path/",no-agent-forwarding,no-port-forwarding,no-pty,no-user-rc,no-X11-forwarding ssh-rsa AAAAB3NzaC1in2EAAAABIwAAABio......

Now try to open a ssh shell on the remote server.. and try some unauthorized rsync commands...

Notes:

  • Beware that if you change rsync command options, change also the authorized_keys file.
  • No need for complex chroot anymore. Forget my previous article: /post/SFTP-chroot-rsync

See also:

  • man ssh #/AUTHORIZED_KEYS FILE FORMAT
  • man rsync
  • view /usr/share/doc/rsync/scripts/rrsync.gz (restricted rsync, allows you to manage allowed options precisely)

Sunday 19 December 2010

I2P proxy configuration via PAC file

With the new french Hadopi law... I just tested the I2P nework, which works fairly well.

Installation is very easy. Just download the .exe file. Then launch it with the java -jar command, like explained on the official site (and it works on Linux, yes !).

Now, if you want to access .i2p sites, like http://forum.i2p, you must configure your browser to use the I2P proxy: localhost:4444

If you don't want to use the proxy when connecting to standard non-i2p sites, you can use the FoxyProxy Firefox extension.

You can also use a PAC (Proxy Auto-Config) file:

function FindProxyForURL(url, host) {
    if (dnsDomainIs(host, ".i2p")) {
        return "PROXY localhost:4444";
    } else {
        return "DIRECT";
    }
}

Then configure Firefox, or proxy configuration in Gnome to use the file:///path/to/proxy.pac file as configuration.

Warning: a bad eepsite (.i2p site), can detect your real IP address with that.

Links:

Tuesday 30 November 2010

Clementine: a good music player

I discovered Clementine some time ago.

There is not any package for Ubuntu or Debian, but the official site gives you a .deb for your favorite Ubuntu version. And you'll also find Mac OS X and Windows versions...

A small click on the good .deb file for your Ubuntu distro and it should install.

I tested tons of players (Amarok, Rythmbox, old XMMS, Exaile, Listen, Totem...).

Clementine is based on Amarok, it's far simpler and integrates very well in Gnome desktop using Qt4 librairies.

I'm happy to know this software and I recommend it to you.

Tuesday 9 November 2010

Exim Router: recipient filter

In a previous post, I explained how to setup a catchall with Exim. This to create multiple addresses all redirecting to the same destination:

catchall_pub:
  driver = redirect
  domains = ads.mydomain.com
  data = user@mydomain.com

Then, I wrote that on destination address we could setup a Sieve or Exim filter with a .forward file.

If you want to do that directly in Exim, to have all config in one place, or simply if your destination mail box doesn't supports filters...

Just add a local_parts option:

catchall_pub:
  driver = redirect
  domains = ads.mydomain.com
  local_parts = !/etc/exim4/ads.mydomain.com.blacklist
  data = user@mydomain.com

In /etc/exim4/ads.mydomain.com.blacklist we put all rejected addresses, one per line:

foo1
bar2

This way foo1@ads.mydomain.com and bar2@ads.mydomain.com will be rejected.

Exim4 Doc:

Saturday 30 October 2010

Preload web content using link prefetch or javascript

For the Bizou php gallery, I looked for different ways of preloading next image in "view" mode (example).

With Firefox it's very simple. Just use the following element and the browser will preload your contents. Contents are preloaded in background, once the whole current page is loaded.

<link rel="prefetch" href="/images/nextimage.jpg" />

Problem: only Firefox supports this currently. Note: a ticket is opened about this in the Chromium project.

For other browsers, use some Javascript triggered by the window.onload event:

<script type="text/javascript">
window.onload = function() {
    // for images
    var im = new Image();
    im.src = '/images/nextimage.jpg';
    // and for other content
    var req = new XMLHttpRequest();
    req.open('GET', 'nextpage.php', false);
    req.send(null);
};
</script>

Beware of HTTP cache headers sent by the server to the browser. To preload correctly PHP pages, make your script send an Expires header:

header('Expires: '.gmdate('D, d M Y H:i:s \G\M\T', time() + 3600));

Then, for a simple browser detection from your PHP script:

<?php if (strpos($_SERVER['HTTP_USER_AGENT'], 'Firefox') !== false) { ?>
<link rel="prefetch" href="nextpage.php" />
 
<?php } else { ?>
<script type="text/javascript">
window.onload = function() {
    var req = new XMLHttpRequest();
    req.open('GET', 'nextpage.php', false);
    req.send(null);
};
</script>
<?php } ?>

Links :

Sunday 24 October 2010

Bizou - a (french) KISS php image gallery

I spent hours on the Web looking for THE PHP software I need with no success.

Required features:

  • Free-libre software
  • No database, just images in a directory, that's all.
  • Easy to understand source code, easy to patch for my needs
  • Variable number of thumbs fitting the browser width.

And I spent another weekend happy coding something that may already exist. But my gallery is simple and does what I want:

http://www.positon.org/bizou/en.html

Demo here

Friday 22 October 2010

A solution to the umask problem: inotify to force permissions

Finding a good solution for sharing files between Linux users is a nightmare.

If using a unique UID is not a problem, it's the most simple solution. All clients access files with the same UID. This way you cannot know who does what, and users cannot fine tune access rights.

The problem: default umask is ALWAYS 0022, so that any created file will get rw– r–– r–– permissions. Only the owner can write. Nobody else. To share files, a group must have write access.

You can change the umask. For command line, you set it in .bashrc or .profile, or /etc/profile for all users. For a SFTP share, you can set it with a trick. For Apache HTTP server, you can set it with /etc/apache2/envvars under Debian.

If file sharing is only done via on service, changing umask is simple, otherwise it's not that easy. And even if you change umask for all services, nothing is perfect: for example it doesn't work with Nautilus and SFTP. Some clients drop files and issue a chmod right after: the hell. You can also try the power of POSIX ACL to force permissions. But problems still remain with some clients.

And for the umask, maybe you don't want all files to be dropped group writable. Maybe you want more granularity on permissions.

So I abandonned the idea of fixing the problem at the source in favor of some trick AFTER file creation. The most simple solution is the cron task: every X minutes, run chmod -R g+w on the directory. This way permissions are not fixed immediately, but asynchronously. And it adds a (very) little more load to your system.

My solution uses inotify to listen for file changes and force permissions when files are created:

aptitude install inotify-tools

And the magical command:

inotifywait -mrq -e CREATE --format %w%f /tmp/mytest/ | while read FILE; do chmod g=u "$FILE"; done

UPDATE 2010-10-30 To support spaces at the end of filenames, and backslashes, use:

inotifywait -mrq -e CREATE --format %w%f /tmp/mytest/ | while IFS= read -r FILE; do chmod g=u "$FILE"; done

Thanks to vitoreiji (see comments)

inotifywait listens for events in the /tmp/mytest directory. When a file is created, it's displayed on standard output. Then each fileline is read by the while loop and permissions are changed. g=u gives the group the user's permissions (with g+w, if the user drops a file with rw– ––– –––, permissions will be rw– –w– –––).

You can now test file/directory creation and copy. mkdir -p a/b/c/d/e shoud also work.

Finally, add it in a boot script:

vi /usr/local/bin/inotifywait.sh && chmod +x /usr/local/bin/inotifywait.sh
#!/bin/sh
# Take the directory name as argument

inotifywait -mrq -e CREATE --format %w%f "$1" | while read FILE
do
	chmod g=u "$FILE"
done
vi /etc/init.d/inotifywait.sh && chmod +x /etc/init.d/inotifywait.sh
#! /bin/sh

case "$1" in
  start|"")

	rm -f /tmp/inotifywait.log
	/usr/local/bin/inotifywait.sh /path/to/dir/ >/tmp/inotifywait.log 2>&1 &
	
	;;
  restart|reload|force-reload)
	echo "Error: argument '$1' not supported" >&2
	exit 3
	;;
  stop)
	# killall inotifywait ???
	;;
  *)
	echo "Usage: inotifywait.sh [start|stop]" >&2
	exit 3
	;;
esac

:

(Debian way)

update-rc.d inotifywait.sh defaults

Note: a drawback: there is a limit on the number of tracked files. See -r option in man inotifywait.

Then the final touch in order for the new files to be created with the same group as their parent: setgid bit for all directories.

find /path/to/dir -type d -exec chmod g+s {} \;

Links:

Monday 11 October 2010

ProFTPD and AuthUserFile for password file

Default in Debian: ProFTPD only uses the /etc/passwd system user base.

To add "virtual" users :

In /etc/proftpd/proftpd.conf:

DefaultRoot ~
AuthUserFile /etc/proftpd/ftpd.passwd
RequireValidShell off

Then restart proftpd: /etc/init.d/proftpd restart

Create users file:

vi /etc/proftpd/ftpd.passwd
username:HASH:1011:1011:MyUserName:/home/ftp/directory:/bin/true

You can also generate the user lines with the ftpasswd command, but I think it's simpler to generate passwords with the command:

mkpasswd --hash=md5

Then paste the HASH in the passwd file.

Correct passwd file permissions:

chown proftpd /etc/proftpd/ftpd.passwd
chmod go-r /etc/proftpd/ftpd.passwd

And just test it to finish.

Links:

Tuesday 21 September 2010

Amarok problem under Ubuntu Lucid

If Amarok doesn't work on Ubuntu Lucid. Playing any song fails.

Simple solution: install apt://libxine1-ffmpeg

sudo aptitude install libxine1-ffmpeg

Possibly restart Amarok.

Source :

Monday 5 July 2010

Change NetHack tiles (xnethack)

NetHack: one of the best games ever.

I find xnethack default tileset rather ugly. Under Debian/Ubuntu (nethack-x11 package), the tileset file must be in XPM format. It's configuration is in the /etc/X11/app-defaults/NetHack file with the default xpm: /usr/share/pixmaps/nethack/x11tiles.

The file must be in XPM format, with a limited size and different color number, otherwise Nethack loading time explodes (several minutes).

Here is how to change it on Debian / Ubuntu:

  1. Example: on http://www.multifoliate.com/nh/, get the chozo32b.zip file (link: NetHack 3.4.2 (32x32) (modified by me)). Extract it to get chozo32b.bmp.
  2. Open it with Gimp, cut it to 1280x864 (to delete the black at the bottom). You can then convert it to 256 colors, then save it as XPM. It works but it's ugly.
  3. To get a better result, save it to PNG.
  4. Install imagemagick package, then convert -depth 5 chozo32b.png chozo5.png
  5. Open chozo5.png with Gimp. Then save it to XPM format.
  6. Edit /etc/X11/app-defaults/NetHack and replace /usr/share/pixmaps/nethack/x11tiles with the path to chozo5.xpm.
  7. Then, reload X ressources: xrdb -merge /etc/X11/app-defaults/NetHack
  8. Start your favorite game: xnethack

The final gzipped file: chozo5.xpm.gz

Notes:

  • convert always saves xpms in 256 colors. I don't know why.
  • head image.xpm gives you the first lines of the XPM file, with number of different colors.
  • Abigada tileset is by default in NetHack iPhone version.

Links:

Sunday 20 June 2010

Reverse SSH Tunnel for SSH connection to a NATed machine

This is how to open a SSH connexion to a serveur hidden behind a NAT gateway.

We use a reverse SSH tunnel:

nated-host$ ssh -R 2222:localhost:22 anyuser@public-host
anyuser@public-host$

This command opens 2222 port on public-host, forwarding it to local 22 port on nated-host.

Finally, from public-host we connect to 2222 local port with SSH, to end on nated-host:

public-host$ ssh -p2222 localhost
nated-host$

References:

Windows XP broken after Ubuntu Lucid upgrade

A friend of mine has a PC with Windows XP / Ubuntu in dual boot. After the Lucid upgrade, Windox fails to boot (strange lines on the screen after Grub...).

TestDisk saved us:

sudo aptitude install testdisk
sudo testdisk
  1. Choose [ Create ] Create a new log file
  2. Choose windows physical disk, then [Proceed ]
  3. Choose [ Continue ] Continue even if there are hidden data
  4. Choose [Intel ] Intel/PC partition
  5. [ Advanced ] Filesystem Utils
  6. On Windows XP partition, boot sector is generally bad or non identical to backup sector (Sectors are not identical.).
  7. Choose [ Backup BS ] to copy the backup sector to the master sector.
  8. After that, quit, reboot and test.

Sources:

Tuesday 8 June 2010

Laptops for Languedoc french students in 2011: letter to Georges Frêche

In 2011, my region plans to distribute laptops for lycée students (lycée ~= secondary school).

I just posted a mail to Georges Frêche, the leader of our Languedoc-Roussillon region.

The letter suggests the choice of Linux and Free Software as a software platform for the laptops.

The letter in my French article.

Thursday 3 June 2010

3D performance problems with KMS

With the last Ubuntu Lucid 10.04 come a new graphical module: KMS or Kernel-based Mode-Setting.

If you have performance problems in some games like Quake 3 or TCE / Enemy Territory, try to disable KMS. The problem can also be with the mouse pointer (slowness, lag, delay or inacuracy).

Grub 2

Add nomodeset in /etc/default/grub then issue the command update-grub.

vi /etc/default/grub
GRUB_CMDLINE_LINUX="nomodeset"
update-grub

Grub 1

Add nomodeset at the end of the # kopt line, then issue command update-grub.

vi /boot/grub/menu.lst
# kopt=root=/dev/sda1 ro nomodeset
update-grub

Someone also told me that disabling KMS solved his Firefox javascript speed problem in Yahoo Mail.

Monday 24 May 2010

Catch-all mail with Exim

The main idea is to have a set of anything@ads.mydomain.com addresses, all redirecting to a single mailbox. Then I can use any address @ads.mydomain.com as an alias to my primary mailbox. This way, I give ebay@ads.mydomain.com to Ebay, amazon@ads.mydomain.com to Amazon and so on.

This solutions allows me to disable an alias if unsubscribing on the site doesn't work. I can also use this to track sites selling my address to other sites.

First, Exim has to listen on the network, and accept mail to the ads.mydomain.com domain. Under Debian, put this in /etc/exim4/update-exim4.conf.conf:

dc_local_interfaces='0.0.0.0'
dc_relay_domains='ads.mydomain.com'

Note under Debian: each time Exim starts the /etc/exim4/exim4.conf.template file is filled with variables set in /etc/exim4/update-exim4.conf.conf, générating the /var/lib/exim4/config.autogenerated file. This is to ease Exim configuration in standard cases.

Then edit the /etc/exim4/exim4.conf.template file to add a new router, in first position just bellow begin routers:

catchall_pub:
  driver = redirect
  domains = ads.mydomain.com
  data = user@mydomain.com

The router will be called for any mail @ads.mydomain.com. It give all mails to the redirect transport sending them to the address set in data.

Finally, you can then use a /home/user/.forward file to disable aliases with a Sieve or Exim filter.

UPDATE 2010-11-10: another way of disabling aliases

References:

Thursday 20 May 2010

Brasero Bug Copying Audio CD in Ubuntu Lucid

Audio CD copy is actually not possible with Brasero on Ubuntu Lucid. All details in the bug report.

As a workaround, you must install cdrdao version 1.2.3, which is not yet available as a package.

To install it, you can use CheckInstall which is a bit cleaner than the old make install:

tar -xjvf cdrdao-1.2.3.tar.bz2
cd cdrdao-1.2.3/
./configure
make
sudo checkinstall make install

Note: the make command can fail because of missing dev packages you'll have to install.

CheckInstall builds and installs a nice Debian package.

You can also use cdrdao directly. ps axf shows us the command Brasero is using:

cdrdao read-cd --device /dev/sr0 --read-raw --datafile /home/dooblem/brasero.toc.bin -v 2 /home/dooblem/brasero.toc

Here we go!

Links :

- page 1 of 3