DeutschEnglish

Submenu

 - - - By CrazyStat - - -

9. October 2012

Portuguese language file for CrazyStat

Filed under: CrazyStat,PHP — Tags: , , , , , — Christopher Kramer @ 14:42

I just noticed that I have not announced yet that CrazyStat has been translated into Portoguese by Pedro Cruz. Thanks a lot!

You can already download the file from SVN (click “Download this file”). Of course the file will be included in the next release.

Just upload it into your stat/src/lang-folder and select the language when logging in. You can also change the default language.

If you find any mistakes in this or any other language file, just let me know.

I hope you enjoy CrazyStat!

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

7. October 2012

SQLite: escaping table and column names correctly

Filed under: DBMS,PHP,phpLiteAdmin — Tags: , , , , , , , , , , — Christopher Kramer @ 21:44

Summary:

Use double quotes around column names, table names, trigger names etc.

SELECT "column name", "my column", FROM "table name"

Use single quotes around string values

SELECT * FROM "table name" WHERE "column name" = 'string value'

Use two double quotes if e.g. your column name contains a double quote (e.g. my”column)

SELECT "my""column" FROM "my""table"

Details:

I joined the development team of phpliteadmin some months ago and am currently fixing several issues. One problem is caused by SQLite’s flexibility of column- and table names. A column named “SELECT”, “my column”, “this, that”, or even “this contains ‘single quotes'” is completely legal in SQLite. And what is legal is being used. So phpliteadmin needs to be able to handle these types of names as well, which is not really the case at the moment.

Let’s assume you query a column called “select”, you are likely to get errors like that in PHP:

Warning: SQLiteDatabase::query() [sqlitedatabase.query]: near “select”: syntax error in …

Or using PDO:

HY000 / 1 / near “select”: syntax error

From MySQL I knew backticks around table or column identifiers like in this example:

SELECT `select`, `my column`, `this, that` FROM `group by table`

(By the way, you see my syntax highlighter has problems with reserved words as column names as well ;-))

I tried this in SQLite and it worked, so I went that way. But there are different versions of SQLite and for the old version that, in PHP, is implemented by SQLiteDatabase, would give the following error:

Warning: SQLiteDatabase::query() [sqlitedatabase.query]: unrecognized token: “`” in …

So I found out that instead of backticks, normal single quotes work:

SELECT * FROM 'group by table'

This works well, but there is a problem here with column names:

SELECT 'select' FROM 'group by table'

This will not work as expected. It will not return the values of the column ‘select’, but create a column filled with the text ‘select’. That is of course standard SQL behaviour and shows that it is a bad idea to use single quotes around column or table names. The bug report by maitredede on the phpliteadmin issue tracker made me aware of another way to escape column and table names:

SELECT [select], [my column], [this, that] FROM [group by table]

This works very well, both with SQLiteDatabase, SQLite3 and PDO when accessing SQLite from PHP. I have not tried it using the SQLite C library directly, but it should work as well.

So I’d say this is the best way to do this as it works the same way with all versions of SQLite / all PHP SQLite extensions.

 

Update:

Well, probably I should have read the manual first:

SQlite supports ` for compatibility with MySQL and [] for compatibility with MS Access and SQL Server. But it also supports double quotes, which is standard SQL. So I’d say this is the best way to do it:

SELECT "select", "my column", "this, that" FROM "group by table"

It works with all SQLite versions / extensions just like the [] syntax. (And my syntax highlighter supports it as well ;-).)

Update 2:

Another thing that might be of interest: What to do, if a column/table name contains double quotes? Well, in this case duplicate them and you are done. Let’s assume you have a column called like this:

my “column

Then you query it like this:

SELECT "my""column" FROM "my_table"

6. October 2012

Raspberry Pi as a digital webradio

Filed under: Linux,Raspberry Pi — Tags: , , , , , , , — Christopher Kramer @ 19:41

As my FM radio just sounded painfully because of so much noise, I looked at my Raspberry Pi lying next to it and thought “would it work as a digital webradio?”.

So I went ahead and installed VLC player:

apt-get install vlc

Then I went to the website of the local radio station and downloaded the m3u-file to the 128 kbit/s MP3-stream. Then I opened the file in VLC and clicked Play.

And it works great! CPU usage is at about 40%, RAM at 70MB. Sound can be digitally transferred using HDMI or using the stereo jack.

By the way, you can change the audio output to the stereo jack like this:

sudo amixer cset numid=3 1

And to HDMI:

sudo amixer cset numid=3 2

Compared to normal standalone webradios, this is not even an expensive solution. Using the IO pins, one could add buttons to control it, add an IR receiver via USB to add an IR remote control. You could even control it over the web using your smartphone.

Power consumption of the raspberry pi is also okay for a webradio, even if you do not shut it down because booting takes too long. Maybe the XBMC distribution for the raspberry pi is even better for a usage like this.

Oh, there are so many cool usages for this board. I really need to build something cool out of it.

4. October 2012

PDO / sqlite: database table is locked

Filed under: DBMS,PHP,phpLiteAdmin — Tags: , , , , , , , — Christopher Kramer @ 22:06

At the moment I am working again on phpliteadmin, a  php-based web GUI for database administration of sqlite databases. While debugging, I stumbled across a problem that only occurred with the PDO extension (not with SQLiteDatabase or SQLite3). I got the following error message while trying to drop a table:

HY000 / 6 / database table is locked

By the way, you can use PDO::errorInfo() to get these error messages. So as the error correctly explains, the table I tried to drop seemed to be in use. PDO documentation for PDO::query() also explains the problem (even though DROP TABLE statements are fired using PDO::exec()):

If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

So I hunted for the open cursor on a resultset of the table in question and could not find any. Finally, I found the SQL statement which still had an open cursor:

SELECT * FROM sqlite_master

Thinking about this, it is obvious: When querying sqlite_master, you request information about database tables. If one of the table gets altered or dropped, this might change the data listed in the resultset on which I still have a cursor.

Maybe this is a special case as usually you do not query sqlite_master a lot. But in case you do, this might be useful information.

To solve the problem, as the manual says, simply release the cursor using PDOStatement::closeCursor() before dropping/altering tables.

11. September 2012

Zimbra: Setting up a free (real) “commercial” SSL certificate

Filed under: Linux,Security,Server Administration — Tags: , , , , , — Christopher Kramer @ 20:40

I recently wrote about how to configure a new self-signed certificate for Zimbra.

Today I want to explain you how you can do even better and setup a real SSL certificate by Startcom which will make those annoying browser warning messages go away 😉 And the best: It is all for free, thanks to Startcom! It is also important to have a real SSL-certificate for use with most smartphones.

Please note that you have to respect Startcom’s certificate policy, which might require a non-free class 2 certificate for your commercial use. See the policy for details. Thanks to Thomas for this remark. With “commercial” certificate, I here mean a “real” CA-signed certificate, which Zimbra calls “commercial”, no matter whether used in a commercial context or not.

First, create a Certificate Request (CSR) in Zimbra. To do so, ssh into your server, login as root and issue a command like this:

/opt/zimbra/bin/zmcertmgr createcsr comm -new -keysize 2048 -digest sha256
-subject "/C=US/ST=CA/L=Sunnyvale/O=Yahoo/OU=Zimbra Collaboration Suite/CN=host.example.com"
-subjectAltNames host.example.com

This is one line. What is important here, is that you use the domain name for which the certificate should be issued at the end (instead of host.example.com). Startcom will ignore everything else anyway, so you can leave country, state, organization and so on as is or change it, doesn’t matter. What is also important is that you define a keysize of 2048 as Startcom won’t accept 1024 bit keys anymore. This parameter is not documented in the wiki yet, and seems to be available for newer versions of Zimbra only. With a little trick, it should also be also possible for older versions of zimbra such as 5 (but better update to the new version anyway…).

Next thing you do is register at startcom if you do not have an account yet. You’ll get an S/MIME certificate by Startcom for free which you need to login to their control panel. Your browser will generate the secret certificate and store it in its internal storage. I’d recommend you to backup this certificate – you will not be able to login into your startcom account if you loose it.

Then at startcom, you use the validation wizard to validate your domain. This will send a mail to postmaster/hostmaster/webmaster@host.example.com (you can choose which one) with host.example.com being your domain. So you need access to one of these mailboxes to prove that you own the domain.

Then use the certificate wizard at Startcom to create a new certificate. Skip the certificate creation step! Instead, past the CSR created by Zimbra ( /opt/zimbra/ssl/zimbra/commercial/commercial.csr ) into the webform. (Better always create private certificates yourself, never use certificate generators by somebody else, not even the CA.)

Once the certificate is created by Startcom (usually takes some minutes), install it as described in the zimbra wiki:

  1. Store the new (public) certificate you get from Startcom somewhere (e.g. /root/commercial.crt )
  2. Download the root CA certificate
    wget -O /root/ca.pem https://www.startssl.com/certs/ca.pem
  3. Download the intermediary certificate from startcom
    # If your certificate is class 1:
    wget -O /root/ca_intermediary.crt https://www.startssl.com/certs/class1/sha2/pem/sub.class1.server.sha2.ca.pem
    # If your certificate is class 2:
    wget -O /root/ca_intermediary.crt https://www.startssl.com/certs/class2/sha2/pem/sub.class2.server.sha2.ca.pem
  4. Combine them:
    cat /root/ca_intermediary.crt /root/ca.crt > /root/ca_chain.crt
  5. Verify your certificate:
    /opt/zimbra/bin/zmcertmgr verifycrt comm /opt/zimbra/ssl/zimbra/commercial/commercial.key /root/commercial.crt /root/ca_chain.crt
  6. Deploy your certificate:
    /opt/zimbra/bin/zmcertmgr deploycrt comm /root/commercial.crt /root/ca_chain.crt
  7. Check:
    /opt/zimbra/bin/zmcertmgr viewdeployedcrt
  8. Restart Zimbra
    su – zimbra
    zmcontrol stop
    zmcontrol start

I hope this post was useful so some of you. If you have problems with one of the steps, just ask in the comments.

Read here how to redirect http to https to enforce the use of https.

9. September 2012

Zimbra: Creating a new self-signed SSL certificate

Filed under: Linux,Server Administration — Tags: , , , , , , , , , — Christopher Kramer @ 10:04

I recently had to recreate the SSL certificate of a Zimbra server and surprisingly it was not as easy as the documentation looked like, so I’d like to document how it is done and make comments on some difficulties that might come up.

So this is how it is done (on a Ubuntu Server running Zimbra Network edition 6.0.16 GA):

  1. SSH into the server, login as root
  2. Switch to the zimbra-user using
    su - zimbra
  3. Then run the following commands:
     sudo /opt/zimbra/bin/zmcertmgr createca -new
     sudo /opt/zimbra/bin/zmcertmgr deployca
     sudo /opt/zimbra/bin/zmcertmgr deploycrt self
  4. Restart Zimbra. To do so, as user zimbra, issue these commands (no sudo here):
    /opt/zimbra/bin/zmcontrol stop
    /opt/zimbra/bin/zmcontrol start

So the difficulties I had and some remarks:

  • sudo kept asking me for a password when I typed in
    sudo zmcertmgr createca -new

    Seems I am not the only one with this problem. The zmcertmgr command is white-listed in /etc/sudoers so you should normally not be asked for a password. Run the following command to edit /etc/sudoers (do not edit it in any other way!)

    visudo

    So make sure in this file the following line is included:

    %zimbra ALL=NOPASSWD:/opt/zimbra/bin/zmcertmgr

    The % at the beginning seems to belong there. Note that the zimbra wiki has typo (zmvertmgr) in this line.
    But although I had this line in there, sudo kept asking me for the password. So what finally worked was invoking zmcertmgr with the complete path (as done above).
    Update: It seems I had a typo in here myself. Make sure it is “zmcertmgr”  and not “zmzertmgr” 😉
    Thanks to the comment by erolha!

  • In the Zimbra Release notes, the last command for updating the certificate is
    sudo zmcertmgr deploycrt self -new

    I got this error:

    Can't deploy cert for -new.  Unknown service.

    Without -new (and the complete path), it went through well.

  • No zimbra documentation I found mentions that a restart of zimbra is required, but without a restart, the old certificate was still used when opening the webmailer or the admin interface via https.

 

I hope I could help some of you that run into one of these problems.

22. August 2012

DHL / paket.de : Kundenservice nur noch Social?

Filed under: Uncategorized — Tags: , , , , , , — Christopher Kramer @ 12:59

Heute aus gegebenem Anlass mal ein Beitrag auf Deutsch und zu einem nicht so technischem Thema: Dem Kundenservice von DHL bzw. Kundenservice im Allgemeinen.

Vor 6 Wochen habe ich auf paket.de, dem Portal der DHL zum Paketversand und -empfang (Packstation etc.), versucht im Kundenbereich eine neue Anschrift zu hinterlegen. Dies scheiterte daran, dass stets die Fehlermeldung erschien, die Anschrift wäre ungültig. Ich versuchte “Straße” ausgeschrieben und abgekürzt, alles ohne Erfolg. Daraufhin kontaktierte ich DHL über das Kontaktformular  zu diesem Anliegen.

Nachdem 2 Wochen später noch keine Antwort kam, sendete ich erneut eine Anfrage über das Kontaktformular. Keine Antwort.

Nun, gestern versuchte ich dann mein Glück über Twitter: Ich sendet gestern Abend einen Tweet an @DHLPaket mit meinem Anliegen. Heute morgen erhielt ich die Antwort:

Bitte senden Sie uns Ihre neue Anschrift an twitter@dhl.com zu. Wir kümmern uns darum.

Also sendete ich mein Anliegen mit neuer Anschrift und Postnummer an genannte Adresse. Und siehe da: Nur eine Stunde später erhielt ich eine E-Mail einer Mitarbeiterin des Packstation-Teams, dass meine Adresse geändert wäre, sowie eine automatische Mail des Systems, dass meine Daten verändert wurden.

Perfekt, aber warum nicht gleich so? Wird Kundenservice heute nur noch über Soziale Netzwerke ernst genommen, da man hier öffentlicher agiert und riskiert, vor vielen Twitter- oder Facebook-Nutzern als wenig kundenfreundlich dazustehen? Es mag sein, dass es sich hier um einen Einzelfall handelte, und das Kontaktformular normalerweise genauso schnell beantwortet wird. Ich habe nicht genug Erfahrung um mir darüber ein umfassendes Urteil zu erlauben. Trotzdem habe ich den Eindruck, dass Unternehmen immer mehr Mitarbeiter in die Social Media Abteilungen setzen und den traditionellen Kundenservice etwas vernachlässigen.

Hat jemand ähnliche Erfahrungen gemacht? Wie kontaktiert ihr den Kundenservice großer Unternehmen bei Problemen? Telefon, Mail oder soziale Netzwerke?

Ich würde mich sehr über weitere Erfahrungen freuen. Ich werde auf jeden Fall demnächst früher versuchen, den Kundenservice per Twitter zu kontaktieren, wenn auf anderem Weg keine Antwort kommt.

Edit: Ich habe auf Elschy’s Blog noch interessante andere Erfahrungen mit Kundenservice über Twitter gefunden.

Edit: Es gibt offenbar eine kostenlose Beschwerdenummer, über die man auch etwas erreicht: 0800 1 888 444 Vielen Dank an Thomas für das Kommentar.

19. August 2012

MythTV: DVD playback does not work

Filed under: Linux — Tags: , , , , , , — Christopher Kramer @ 00:09

A colleague of mine who is currently setting up Mythbuntu (MythTV) as a media center PC contacted me today because he had a problem with DVD playback in MythTV. When selecting DVD-playback, just nothing happend.

I first asked him to make sure the DVD drive is working and the DVD can be mounted and he confirmed it works (you should check as well).

Then I asked him to launch MythTV from the console (e.g. in Gnome Terminal, run “mythfrontend”) and try to play the dvd and see whether an error message is logged in the terminal window. And in fact MythTV complained:

faild to open device at /dev/dvd

I asked him to send me the list of his devices using:

ls /dev

This showed up no device “dvd” (the dvbt-stick created a “dvb”-device, so not get confused with that). The dvd-device in fact had the name “dvd1”. So with this knowledge, the problem is easy to fix:

ln -s /dev/dvd1 /dev/dvd

So this creates a symbolic link so /dev/dvd1 can be accessed using /dev/dvd as well so MythTV is pleased.

Hope this helps some of you.

 

14. August 2012

MySQL: How to do a (simple/complex) CHECK CONSTRAINT?

Filed under: DBMS — Tags: , , , , , , , , , — Christopher Kramer @ 02:27

Another “badly-missing-feature”-post. I should create a category for that I guess…

Usually, you can add a constraint to a table in SQL with something like:

ALTER TABLE foo ADD CONSTRAINT my_constraint CHECK (bla<>0)

Some sites like w3school even tell you this is the syntax for it in MySQL. Well, I guess they have never tried this and not even read the manual. Well, you can execute it in MySQL without an error. But also without any effect whatsoever. I tried it in InnoDB (MySQL 5.5) and it did not check the constraint at all.

The MySQL manual says:

The CHECK clause is parsed but ignored by all storage engines

Hallelujah, MySQL is great. A bug request for that exists since 2004, so don’t expect it to be fixed soon…

So we go with a trigger instead. For constraints, we use a BEFORE trigger. So next thing I tried looked something like this:

CREATE TRIGGER check_my_constraint BEFORE UPDATE ON `foo`
     FOR EACH ROW 
     BEGIN 
     IF (NEW.bla=0)
     THEN 
     ROLLBACK;
     END IF; 
     END

Should work, but doesn’t. Problem is that MySQL doesn’t like ROLLBACK here. Hallelujah, MySQL is great. So what worked in the end is sending an error signal:

CREATE TRIGGER check_my_constraint BEFORE UPDATE ON `foo`
     FOR EACH ROW 
     BEGIN 
     DECLARE msg varchar(255);
     IF (NEW.bla=0)
     THEN 
        SET msg = concat('Constraint my_constraint violated: bla must not be zero ', cast(new.bla as char));
        SIGNAL sqlstate '45000' SET message_text = msg;
     END IF; 
     END

Thanks to Rui Da-Costa for the solution with the SIGNAL.

The cool thing about triggers is that you can do complex checks here. So I just used it for something like:

 IF (NEW.ref_id IS NOT NULL AND
     NEW.ref2_id<>(SELECT `ref2_id` FROM `ref` WHERE `ref`.id=NEW.`ref_id`))

So that makes sure that ref_id is either null or it refers to a row in table `ref` with this id that has the same ref2_id as the table we are checking. So a more complex type of foreign key relationship.

Hope this helped somebody to get things solved more quickly. Don’t forget to add an INSERT trigger as well.

Another remark: Make sure you do not use semicolon as delimiter when using SQL/PL syntax. phpMyAdmin has an extra field to change the delimiter (below the textarea where you enter your SQL) for example.

7. August 2012

Flash on the Raspberry Pi

Filed under: Linux,Raspberry Pi — Tags: , , , , , , , , — Christopher Kramer @ 17:14

The Raspberry Pi is a cool tiny board with a ARM CPU and lots of interfaces like HDMI, USB and Ethernet that runs Linux and costs only about 30€. If you have not heard if it, you should have a look at it, it’s just a very cool gadget to play around with or build your own devices. I got mine about a month ago but unfortunately did not have a lot of time to play with it, but am doing a bit from time to time, so you might hear some more stuff about it soon.

That’s what (mine) looks like:

Lots of people say having flash on the raspberry pi would be cool but then others claim it’s impossible to realize. In the Raspberry Pi forum, user “Aux” writes:

There is absolutely no way of getting Flash for RasPi. Adobe is a closed-source developer and the only version they made for ARM CPUs is Android one which requires ARMv7 CPU with NEON extension. That makes RasPi unable to run it even if someone ports Android.

That’s a reasonable thought and I thought the same way. But then I talked to a friend of mine and he said “well, what about Gnash? Does it run on the raspberry Pi?”

And so I went straight home and installed Gnash on my Raspberry Pi. And it works 🙂

Opening Adobe’s Flash test page looks like this:

The red ball indicates that Flash is working.

So what is gnash? Well, this is what the Debian repository description says:

GNU Gnash is a free GPL’d SWF player. It runs as a standalone application on either the desktop or embedded devices, or may be used as a plugin for several popular browsers. It supports playing media from a disk or streaming over a network connection.

GNU Gnash is based on GameSWF and supports most SWF v7 features and some SWF v8 and v9. SWF v10 is not supported by GNU Gnash.

Included in the Gnash is an XML based messaging system, as specified in the SWF specification. This lets a SWF animation communicate over a TCP/IP socket, and parse the incoming XML message. This lets an animation be a remote control for other devices or applications.

As there is an ARM package of Gnash, it installs fine on Debian for the Raspberry Pi.

Here is how you install Gnash for Flash support in the browser:

sudo apt-get install iceweasel browser-plugin-gnash

So this will get you a Mozilla-based browser called iceweasel (just like Firefox) that plays Flash.

Well, this is the theory. Reality is that going to youtube with that and trying to play a movie is just a pain. It eats up the whole CPU and hangs for hours until the 5 minutes movie is through.

But simple Flash animations should work fine. Have a try and enjoy your raspberry Pi!

Note that I still used Debian Squeeze and not yet the special Raspbian distro that is reported to run faster. I will have a try with the new version soon to see whether Gnash works reasonably fast on the new version.

 

 

« Newer PostsOlder Posts »