DeutschEnglish

Submenu

 - - - By CrazyStat - - -

11. August 2013

RT Request Tracker: Migrate From SQlite to mySQL

Filed under: DBMS,Linux,Server Administration — Tags: , , , , , , — Christopher Kramer @ 23:07

I lately had to migrate an RT installation (version 4.0.4) from SQLite to MySQL. In case anyone else has to do this, here is a brief description of how it worked out.

  1. Setup a working MySQL server in case you have not already
  2. Create a MySQL user for RT (e.g. rt4)
  3. Configure RT to use MySQL using this username and a dbname (e.g. rt4) of a not yet existing db (See /etc/request-tracker4/RT_SiteConfig.pm and RT_SiteConfig.d/)
    I’d recommend to keep a copy of the SQlite-Config…
  4. Run
    rt-setup-database --action init

    to create a blank RT DB in MySQL. Check if it worked.

  5. Delete all rows from the MySQL DB, only keep the schema.
  6. Create a copy of your SQLite db
    cp rtdb mydbcopy
  7. Open the copy of the db in the sqlite shell
    sqlite3 mydbcopy
  8. For each table in the DB:
    1. Set the Output file to something like this (“Attachments” is the table name)

    .out data_Attachments

    2. Set the mode to insert (“Attachments” is again the table name)

    .mode insert Attachments

    3. Get all the data

    SELECT * FROM Attachments;

    4. Now you have a file with lots of INSERT statements for the Attachments table. Try to run it in mysql:

    mysql -u rt4 -p rt4 < data_Attachments

    (On a linux shell, not the sqlite shell of course. Here the first rt4 is the username and the second the dbname. data_Attachments is the dump file created before)
    5. In case mysql complains some NOT NULL constraints are violated:
    Go back to the sqlite shell and set these cells to the default-value (0):

    UPDATE Attachments SET someColumn=0 WHERE someColumn IS NULL;

    (Only do this on the copy, not the original db 😉 )
    Now recreate the dump and retry to insert this in mysql. Do it with all columns where NOT NULL constraints are violated.

This works table by table, there are no foreign keys that would get in the way. You can also do several tables in one output file, but you might run into problems when NOT NULL constraints are violated by one table. After all your tables are filled with your data, RT should work. Maybe restart Apache.

apache2ctl restart

This worked without problems so far for me. I first tried pumping the whole SQLite dump into MySQL (using this conversion script) but the schema that this ended up in was different, missed indexes and RT only liked it until I restarted Apache (which then refused to start). Better start with a schema created by rt-setup-database, not with one that originates from SQLite.

I hope this is of some help for somebody. Please let me know in case it helped you or if you have any comments or questions.

Recommendation

Try my Open Source PHP visitor analytics script CrazyStat.

7. February 2013

Icinga / Nagios: Notify a group of contacts about a group of hosts

In Nagios/Icinga, you can easily define which contacts or contact groups get notified for a certain service in the service definition:

 define service{
        host_name               linux-server
        service_description     check-disk-sda1
        check_command           check-disk!/dev/sda1
        max_check_attempts      5
        check_interval          5
        retry_interval          3
        check_period            24x7
        notification_interval   30
        notification_period     24x7
        notification_options    w,c,r
        contact_groups          linux-admins
        }

(Source of this example: Icinga documentation)

So only contacts of the contact group “linux-admins” would be informed about problems regarding this service.

You could also use the “contacts” directive to list individual contacts or list multiple contact groups.

But often, the responsibility of admins is not defined through services, but through hosts. Usually, there is a group of admins for linux servers and one for windows servers. Or a group for intranet servers and one for internet servers. As admins usually are annoyed if they get notifications about servers they are not responsible for, it is usually a good idea to only notify those admins that are responsible.

So you can also do this at the host-definition:

 define host{
        host_name                       bogus-router
        alias                           Bogus Router #1
        address                         192.168.1.254
        parents                         server-backbone
        check_command                   check-host-alive
        check_interval                  5
        retry_interval                  1
        max_check_attempts              5
        check_period                    24x7
        process_perf_data               0
        retain_nonstatus_information    0
        contact_groups                  router-admins
        notification_interval           30
        notification_period             24x7
        notification_options            d,u,r
        }

(Source of example: icinga documentation)

So only the contact_group “router_admins” would be notified for this host.

But one thing where the “contacts” and “contact_groups” directive is missing, is the hostgroups definition. It is not possible to directly assign a contact group  or list of contacts to a hostgroup or the other way round. So here is how it can be done with another type of definition.

Group your hosts

First, define a group of hosts for each group of admins. So for example, group all intranet servers in one and all internet servers in another group. You probably already did this.

define hostgroup{
        hostgroup_name          intranet-servers
        alias                   Intranet Servers
        members                 intra1, intra2, intra3
}
define hostgroup{
        hostgroup_name          internet-servers
        alias                   Internet Servers
        members                 inter1, inter2, inter3
}

See the icinga documentation for details. Note to use the shortnames in “members”.

You can also define things the other way round: When defining a host, say which hostgroup it belongs to:

define host{
        use                     generic-host
        host_name               intra1
        alias                   intra1.local
        address                 192.168.10.1
        hostgroups              intranet-servers
        }

See documentation for details.

Group your contacts

Next, group your contacts. So create a contact-group for each group of admins so we can later assign this contact group to the corresponding group of hosts.

Example:

define contactgroup{
        contactgroup_name       intranet-admins
        alias                   Intranet Administrators
        members                 alice, bob
        }
define contactgroup{
        contactgroup_name       internet-admins
        alias                   Internet Administrators
        members                 charley
        }

See documentation. Again, you can also define it the other way round (list the contact groups at the contact-definition).

Assign contact groups to host groups

Now comes the interesting part. To do this, we use a “Hostescalation definition“.

Example:

 define hostescalation{
        hostgroup_name          intranet-servers
        first_notification      1
        last_notification       0
        notification_interval   60
        contact_groups          intranet-admins
        }

 define hostescalation{
        hostgroup_name          internet-servers
        first_notification      1
        last_notification       0
        notification_interval   60
        contact_groups          internet-admins
        }

This will make sure internet-admins get informed about internet-servers and intranet-admins about intranet-servers. “last-notification 0” means that all notifications will get sent to this group of contacts. You can adjust the notification_interval (in minutes) if you want.

The cool thing here is that you can also define that if the problem still occurs after 5 notifications, the other team of admins gets notified:

define hostescalation{
        hostgroup_name          intranet-servers
        first_notification      1
        last_notification       3
        notification_interval   30
        contact_groups          intranet-admins
        }
define hostescalation{
        hostgroup_name          intranet-servers
        first_notification      4
        last_notification       0
        notification_interval   60
        contact_groups          internet-admins, intranet-admins
        }

This would notify “intranet-admins” 3 times (every 30 minutes) about problems with “intranet-servers”. If the problem is still not solved, “internet-admins” will get notified as well. So the internet-admins won’t get bothered with short problems that the intranet-admins can fix, but will still get informed if the problem is not solved for some time.

More information on hostescalation and serviceescalation in the documentation here, here and here.

I hope this helped somebody.

30. January 2013

Nagios / Icinga: Monitor (local) memory usage

Filed under: Linux,Server Administration — Tags: , , , , , , , , , , , , , — Christopher Kramer @ 17:55

Nagios and its fork icinga are great monitoring tools. They come with a bundle of plugins to monitor standard services such as HTTP, SMTP, POP3, load and stuff like that. And there are lots of 3rd party plugins available for almost everything else you can think of.

But one standard thing that is missing in the official nagios-plugins package is a plugin to check memory usage (of the local machine).

So here is how to install one. I assume a Debian system with Icinga running – you might want to adjust paths for other distros or nagios.

  1. Download the plugin here
    e.g. from the shell:

    wget https://exchange.icinga.com/exchange/check_memory/files/784/check_memory.pl
  2. Then move the file to the other plugins
    mv check_memory.pl /usr/lib/nagios/plugins/check_memory.pl
  3. Make it executable
    chmod +x /usr/lib/nagios/plugins/check_memory.pl
  4. Try to run it:
    perl /usr/lib/nagios/plugins/check_memory.pl -w 50% -c 25%
  5. This should give something like “CHECK_MEMORY OK – […] free […]”. If an error occurs, you probably need to install the perl module Nagios::Plugin. On Debian, the easiest way is:
    apt-get install libnagios-plugin-perl

    On other distros, you might use CPAN:

    perl -MCPAN -e 'install Nagios::Plugin'

    This will ask you lots of questions and install lots of dependencies (where you should say “yes”).

  6. Configure the check_memory command. To do this, create a file /etc/nagios-plugins/config/memory.cfg with this content:
    # 'check_memory' command definition
    define command{
            command_name    check_memory
            command_line    perl /usr/lib/nagios/plugins/check_memory.pl -w $ARG1$ -c $ARG2$
            }
  7. Now you can use the check_memory command to define a service. For example, add this to /etc/icinga/objects/localhost_icinga.cfg (assuming you define localhost-services there):
    define service{
            use                             generic-service
            host_name                       localhost
            service_description             Memory
            check_command                   check_memory!50%!25%
            }

    This will send you a warning when memory usage is 50% and critical when only 25% is free. You might want to adjust these values of course depending on what is normal on your system and how early you want to be notified.

  8. Check your configuration:
    /usr/local/icinga/bin/icinga -v /etc/icinga/icinga.cfg
  9. Restart Icinga / Nagios if the preflight-check was okay:
    /etc/init.d/icinga restart

This should be it.

I hope this helped somebody.

To monitor memory usage of a remote server, you’ll need SNMP for example. Maybe I’ll post another blog post on this soon.

26. January 2013

Funny pseudo-exploit for phpLiteAdmin

It seems people really got interested in the security of phpLiteAdmin. That’s cool, lots of people searching for security issues will give us the opportunity to fix a lot of things in a short period of time. Go on searching. We’ll go on fixing.

But some of these exploiters only publish an “exploit” that contains no real issue at all. Probably only to get some publicity, or maybe because they don’t even realize that what they “found” is not an issue at all. Or meant as a joke?

I recently found a new “security exploit” listing several “vulnerabilities”, which in fact are no bugs of phpLiteAdmin at all but misconfiguration or even features. So here I want to have a look at an “exploit” released by “KedAns-Dz”:

1-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=0
0     _                   __           __       __                     1
1   /' \            __  /'__`\        /\ \__  /'__`\                   0
0  /\_, \    ___   /\_\/\_\ \ \    ___\ \ ,_\/\ \/\ \  _ ___           1
1  \/_/\ \ /' _ `\ \/\ \/_/_\_<_  /'___\ \ \/\ \ \ \ \/\`'__\          0
0     \ \ \/\ \/\ \ \ \ \/\ \ \ \/\ \__/\ \ \_\ \ \_\ \ \ \/           1
1      \ \_\ \_\ \_\_\ \ \ \____/\ \____\\ \__\\ \____/\ \_\           0
0       \/_/\/_/\/_/\ \_\ \/___/  \/____/ \/__/ \/___/  \/_/           1
1                  \ \____/ >> Exploit database separated by exploit   0
0                   \/___/          type (local, remote, DoS, etc.)    1
1                                                                      1
0  [+] Site            : 1337day.com                                   0
1  [+] Support e-mail  : submit[at]1337day.com                         1
0                                                                      0
1               #########################################              1
0               I'm KedAns-Dz member from Inj3ct0r Team                1
1               #########################################              0
0-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-1
Okay yeah. We know exploiters love ASCII art. Kind of cool, agreed.
###
# Title : phpLiteAdmin v1.8.x->1.9.x (SQLi/FD) <= Multiple Vulnerabilities
# Author : KedAns-Dz
# E-mail : ked-h (@hotmail.com / @1337day.com)
# Home : Hassi.Messaoud (30500) - Algeria -(00213555248701)
# Web Site : www.1337day.com .net .org
# FaCeb0ok : http://fb.me/Inj3ct0rK3d
# TwiTter : @kedans
# Friendly Sites : www.r00tw0rm.com * www.exploit-id.com
# Type : proof of concept - webapp 0day - remote - php
# Tested on : Windows7
###
# <3 <3 Greetings t0 Palestine <3 <3
# F-ck HaCking, Lov3 Explo8ting !
As I said. I like exploits as well. Even if they just make me laugh 😉
######## [ Proof / Exploit ] ################|=>
# Google Dork :
# allintext:"Powered by phpLiteAdmin | "
##################
# [!] Description:
------------------
phpLiteAdmin is suffer from multiple vulnerabilities / bugs in
v1.8.x to-> 1.9.x , the attacker can use some bug in the Script
to inject some remote SQL command/code , and Disclosure the Full Path.
Interesting to say 1.9.x when there are still 1.9.x versions to be released in the future. So you are sure we won’t fix your “bugs”? Probably you are right 😉
# Bugs :
#-------
# Authentication Bypass
# SQL Injection/Exec
# Full Path Disclosure
#######################
#### (1) Authentication Bypass :
--------------------------------
[!] php-code :
line 38->39 :::::::::::::::::
//password to gain access
$password = "admin";
:::::::::::::::::::::::::::::
- not affected on all targets, just change the password to fix it
LOL
[+] http://[target]/[path]/phpliteadmin.php
[*] password : admin
I didn’t know we call it an “Authentication Bypass” if we use the authentication system by entering the correct password. Yeah, phpLiteAdmin has a default password, which is “admin”. No secret here. I mean, anybody keeping the default password on a publicly accessible installation should know that other people could get access. Current version of phpLiteAdmin even shows you a warning if you still use the default pw.
No “bug” or “vulnerability” at all.
#### (2) Full Path Disclosure :
-------------------------------
[+] http://[target]/[path]/phpliteadmin.php?view=import
[!] & Import File with (NULL/Bad) Content =>
- you get some sql error msg with the full path of phpliteadmin.php
ex: '-------------
Warning: PDO::exec(): SQLSTATE[HY000]: General error:
trying to execute an empty query in C:\Program Files\EasyPHP-12.1\www\phpliteadmin.php on line 987
____________________________________
Warning: SQLiteDatabase::queryExec() [sqlitedatabase.queryexec]: Cannot execute empty query.
in /homepages/20/d421371141/htdocs/pauleschoen.com/cgi-bin/phpliteadmin.php on line 646
------------------'
proof image (http://i46.tinypic.com/ddmek5.png) # in local test
proof image (http://i49.tinypic.com/juepet.png) # in remote test
LOL. That’s one of the most difficult ways to make phpLiteAdmin produce a PHP error message 😉
Probably you should configure your webserver correctly. Everybody who enables php-ini directive “display_errors” on a public server effectively provokes a “full path disclosure” somewhere. Maybe you should better publish an exploit for php itself 😉
Okay, seriously: We could use ini_set to make sure phpLiteAdmin won’t show any php errors. We probably will. But nevertheless, on about any php-server, you’ll find another script where you can provoke a php-error.
I am not saying it is good that these errors can happen at all. Of course it’d be better to improve checking of input and catch errors properly. This would be a real issue. But not a “vulnerability”. If these errors get displayed, your webserver is configured in a vulnerable way, which is not the fault of phpLiteAdmin.
#### (3) SQL Injection :
------------------------
php-code ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
$query = "SELECT * FROM ".$db->quote_id($_GET['table'])." WHERE ROWID = ".$pks[$j];
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
[+] http://[target]/[path]/phpliteadmin.php?action=row_view&table='
[&] http://[target]/[path]/phpliteadmin.php?action=row_view&table=' [ SQLi ]
If you publish an SQL injection exploit, why don’t you insert some real SQL as a proof of concept? Maybe because what you “found” here in fact is no SQL injection at all?
The function quote_id() will make sure you cannot inject some other SQL command. Of course you can “inject” some invalid table-name, which will make phpLiteAdmin show up some errors, as your “proof image” shows. So another complicated way of producing an error message. Congratulations! But you cannot inject a “DROP Table” here or something like that.
Moreover, it is ridiculous to even search for an SQL injection in phpLiteAdmin. If you are logged into phpLiteAdmin, you can of course perform any SQL command. That is what phpLiteAdmin is made for. It even gives you a GUI to perform SQL queries in a comfortable way. No need to inject them via parameters.
As long as you cannot inject an SQL query without authenticating properly, any “SQL injection” in phpLiteAdmin is not a security vulnerability but a normal issue. If some table has a special name, e.g. containing quotes, we need to make sure the name is escaped properly so phpLiteAdmin works as expected. But this is not the case here: quote_id() will make sure every table-name will be escaped properly. No issue here at all.
#### (4) Exec SQL code :
------------------------
Go to :
[*] http://[target]/[path]/phpliteadmin.php?view=sql
-& put the SQL Code in the text-area (Run SQL query/queries :)
and click 'GO' to exec ;) .
LOL. That’s a really funny one. You might call this a vulnerability, I’d call it a feature.
Don’t you show yourself here that issue 3 is ridiculous?
So I am not sure if this exploit really was meant seriously or if it was meant as a joke. It made me laugh anyway.
I hope you enjoyed it as well. Keep on exploiting!

 

3. November 2012

phpLiteAdmin 1.9.3 released (security-update)

Filed under: DBMS,PHP,phpLiteAdmin,Security,Server Administration — Tags: , , , , , , , , , , — Christopher Kramer @ 00:45
Screenshot of phpLiteAdmin 1.9.3

Screenshot of phpLiteAdmin 1.9.3

Some minutes ago, I released the new version of phpLiteAdmin, a web management GUI for SQLite databases written in PHP. You can download it from our project site.

The new version addresses and mostly fixes lots of issues. Among these, one security issue has been fixed. Therefore, I’d recommend anybody using phpLiteAdmin to update.

A lot of work has gone into this release, fixing lots of bugs to make phpLiteAdmin more robust. For example, you can now have tables or columns containing special characters. The ALTER TABLE features have been partly rewritten so they now work a lot more reliable. And lots of other issues have been fixed. Thanks to anybody who reported bugs to the bug tracker.

If you still have any problems or suggestions, please let us know on our issue tracker.

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.

9. June 2012

Typo3 and other charsets than UTF-8 (latin1 / ISO-8859-1, …)

Filed under: PHP,Server Administration,Typo3 — Tags: , , , , , , , — Christopher Kramer @ 12:30

When updating a Typo3 installation to Typo3 4.5.x, I had problems with charsets and explained the solution here.

Now updating an installation of Typo3 to 4.6.x, I ran into another charset problem: The backend now was completely UTF-8 and therefore, changing texts in the backend caused them to be stored as UTF-8. As the frontend was still ISO-8859-1, special characters (Umlaute) over there got messed up. Maybe there is a way out of this as well ($TYPO3_CONF_VARS['BE']['forceCharset'] I guess), but this clearly shows that Typo3-developers drop support for other charsets slowly and that it might be easier to switch to UTF-8.

In the release notes of Typo3 4.5, I found the following passage:

UTF8 by default: New installations will use UTF8 automatically. Keep in mind that we will be deprecating all other charsets in the release of 4.5, but still support those charsets. 4.7 or maybe even 4.6 will be the first “UTF-8 only” release. When upgrading from older releases to 4.5, you will have to specifically set $TYPO3_CONF_VARS['BE']['forceCharset'] and $TYPO3_CONF_VARS['BE']['setDBinit'] in your localconf.php. An Upgrade Wizard will help you with that.

In the release notes of Typo3 4.6, I could not find a word about UTF-8, but in the release notes of 4.7, it is clearly stated:

check you database if it is utf-8 encoded – TYPO3 4.7 only will work with utf-8.
[…]
The forceCharset option has been deprecated in version 4.5. UTF-8 is now enforced. Even though other values than “utf-8” have not been possible anymore for some time, the option’s value has been queried at plenty of places within the whole core. These references, the option in the Install Tool, as well as many defaults with charset “iso-8859-1” in several classes have been changed, so TYPO3 now works UTF-8-only internally.

So it is clearly time to make the switch.

It is not that complicated – everything is described very well over here.

As the official wiki is very long and explains lots of stuff you might just not care, here are the basic steps:

  • Backup Database and Files
  • Set the charset in your webserver (e.g. “AddDefaultCharset utf-8” in a .htaccess)
  • Adjust some settings in localconf.php:
    // For backend charset
     $TYPO3_CONF_VARS['BE']['forceCharset'] = 'utf-8';
     $TYPO3_CONF_VARS['SYS']['setDBinit'] = 'SET NAMES utf8;'; 
    
     // For GIFBUILDER support
     // Set it to 'iconv' or 'mbstring'
     $TYPO3_CONF_VARS['SYS']['t3lib_cs_convMethod'] = 'mbstring';
     // For 'iconv' support you need at least PHP 5.
     $TYPO3_CONF_VARS['SYS']['t3lib_cs_utils'] = 'mbstring';
  • Adjust your typoScript (change language to your needs):
    config.locale_all = de_DE.utf-8
  • Convert your templatefiles to UTF-8 (and remap them if you use TemplaVoila) – usually in fileadmin/templates
  • Convert your DB to UTF-8
    1. Backup it first if you have not yet (believe me!)
    2. Paste this tool into fileadmin
    3. Run it by opening it in the browser (http://example.com/fileadmin/db_utf8_fix.php)
    4. If everything says “OK”, change the constant “SIMULATE” to false
    5. Run it again
    6. Clean cache of Typo3
    7. Check your site (esp. special characters). If the content is messed up or parts are missing, do the following:
      1. Restore the backup of the database (yes, I told you!)
      2. Uncomment lines 108 – 123 in db_utf8_fix.php
      3. Run it in browser againClean cache in Typo3
    8. Clean all cache in Typo3 Backend

You can find more detailed information here. There are also lots of other ways described how to convert the database.

Happy converting!

 

Update 2014-05-05: Changed link to db_utf8_fix-script as the original site is reported to be attacked and does not host the script anymore. I cannot check if the script at snipplr is exactly the same, but it looks so.

30. May 2012

Released: phpLiteAdmin 1.9.2 includes CSV import/export

Filed under: DBMS,PHP,phpLiteAdmin,Server Administration — Tags: , , , , , , — Christopher Kramer @ 15:54

As I wrote in March, I implemented CSV import and fixed export issues of phpLiteAdmin. This fixed phpLiteAdmin bug #71. I also wrote a small fix for bug #75. Today, new version 1.9.2 of phpLiteAdmin was released including both fixes. You can download it here.

I’d like to thank the phpLiteAdmin team for including my work and allowing me to join the team. I plan to address more issues of phpLiteAdmin in the future to push phpLiteAdmin a little further. There is still some more work to be done which I will have a look at once I find the time.

I recommend the new version to anybody using phpLiteAdmin (and also everybody who doesn’t yet ;-)). Please use the bugtracker in case you find any issues.

I hope some of you find the new features useful or are happy to see those bugs fixed.

Thanks again to the phpLiteAdmin team for the great tool and the opportunity to contribute to the project. Fortunately, I do not have to create a fork to improve the tool.

 

10. March 2012

phpliteadmin: CSV import and export

Filed under: DBMS,PHP,phpLiteAdmin,Server Administration — Tags: , , , , , , , , — Christopher Kramer @ 12:24

In case you use sqlite, a serverless, transactional SQL-complete database engine, you might also know phpliteadmin. It is a web-based DB admin tool just like phpMyAdmin is for MySQL. It is small and does not require complex installation just like sqlite. It is great to do most simple operations on a sqlite database, but in my opinion it still has some important limitations and bugs.

One thing I recently missed was the possibility to import CSV files into a SQLite database. The SQLite shell makes this very easy (if the file has the correct syntax). But this has also some limitations and you have to leave the phpLiteAdmin GUI and start a SQLite shell. So I thought it should be part of phpLiteAdmin. It already had some GUI options for CSV import, but the actual import implementation was missing, so I implemented CSV import myself. On the way testing the exported csv and importing it again I noticed some export bugs of phpliteadmin like this one and fixed them on the way.

So those are the changes I did to phpLiteAdmin:

Import:

  • implemented CSV import with some features like:
  • you can define Enclosure, Escaper, Null and first-row-field-description just like when exporting
  • using PHP’s fgetcsv()
  • can import files that were exported with phpliteadmin using the same settings!
  • can even import files exported with phpliteadmin without my corrections and improvements (see below), although they are not “standard CSV”.

Export:

  • removed line terminator option because:
    • this was not implemented
    • in my opinion anything else than a linebreak does not make sense, so useless feature
  • Fix: Tables with indexes were exported multiple times
  • Fix: Last column was terminated which is not usual in CSV
  • NULLs are not enclosed anymore to allow distinction between “NULL”-text
  • FIX: Removing CRLF was not implemented
  • FIX: Tables that were not exported caused additional linebreaks in output

You can find anything related to these changes in phpLiteAdmin’s bug tracker.

You can also get my version with the improvements from the bugtracker or download it here.

I really hope this will make it into the next release of phpLiteAdmin, but unfortunately I did not get any feedback by the developers of phpLiteAdmin yet. Hopefully this is not getting a fork 😉

I hope you like these features and bugfixes. You can give me feedback here or in the phpliteadmin bug tracker.

I really think a good database management tool for SQLite is needed and phpLiteAdmin has the potential to become one. We will see.

You can also tell me if you use some other management tool for SQLite which you think is way better 😉

Update 2012/05/13: The phpliteadmin developers just announced that an official new version including these improvements will come up soon. Great news, I am looking forward to this new version!

« Newer PostsOlder Posts »