php security

Today I visited an irc channel where some core developers are having a chat. They hang out, they chit chat and sometimes even talk about php.

Was approached by a developer who stated he has the next big cool thing. A framework/objectbase that enables you to do anything (a very true statement). Somewhere in this talk it mentioned security. I’ve been out of this for a long time but it still has my private focus. I asked a few questions and the original coder asked me to audit it.

There are a few things I ask before  I start auditing.

1. Is that your site?
2. Is your code security aware?
3. Do you mind a full disclosure after I have given you the time to fix it.

I still need the answer to item number 1. It took me a few minutes to create a username that only was visible for someone using the mysql commandline tool to look in the database. The admin interface did not show it.

If item 1 is answered and item 3 is answered positive a fix in my name might be posted by the creators.

MySQL binlog’s

Had van de week een klusje waarbij ik flink wat MySQL dumps moest importeren in een andere server. Op zich geen enkel probleem. Op die nieuwe server had ik een mooie grote data schijf en nog een dikke 2 Gig vrij op de var.

Helaas liep de /var schijf met zo’n snelheid vol door het importeren van de MySQL data dat de vrije ruimte met een paar minuten op was. Een goede les is dus om ook de binlog op een andere locatie te mounten waar je genoeg ruimte hebt.

Is het bovenstaande niet mogelijk dan kun je wel wat omwegen gebruiken. Onderandere het purgen van je binlog tijdens een import. Let wel, maak voor je dit soort acties gaat uitvoeren een goede backup van al je databases en hou er rekening mee dat dit niet zomaar op een replicated server gaat.

Purgen kan op twee manieren. De eerste is het purgen naar een bestand.

PURGE BINARY LOGS TO 'mysql-bin.010';

Aangezien ik te weinig tijd had om uit te zoeken of ik dat bestand ook direct op een andere locatie kon gooien heb ik uiteindelijk gekozen voor het purgen tot aan een bepaalde datum:

PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

Let er op dat je de datum en tijd in het volgende formaat mee geeft ( YYYY-MM-DD hh:mm:ss ).

tcms, basis werkt nu op postgresql

Zo, na een paar avondjes klussen lijkt het erop dat de basis van tcms nu werkt op postgres. Ik kan inloggen, pagina’s aanmaken, updaten, menu items toevoegen, automatisch boomstructuren parsen. Tijd voor de planning naar versie 0.1. Nog best wel een hoop te doen maar het lijkt de goede kant op te gaan.

Het overzetten van MySQL naar Postgres vergde nog best wel wat uitdagingen maar als je eenmaal bezig bent dan kom je er achter dat het best mee valt. Met MySQL ging ik vooral de OO manier van werken gebruiken en ik merk nu dat de PHP functies voor PostgreSQL daar niet echt op inspringen. Neem alleen al het feit dat ik het niet zomaar voor elkaar krijg om exceptions op te pakken als er dingen mis gaan. Ook is het best wennen om van een systeem af te stappen waarbij je veel met error numbers kon werken. PostgreSQL vraagt daar toch een beetje een andere aanpak.

De volgende lijst moet nog gedaan worden voor ik versie 0.1 uitbreng

• Algemene security in alle interfaces.
• Aanmaken/bewerken gebruikers en rechten op groepen geven.
• Uploaden en linken van afbeeldingen.
• Macro systeem waarbij ik zoiets als de WebGUI oplossing wil gebruiken.
• Opschonen van overbodige code.
• Css interface waabij ik vooral niet de WebGUI oplossing wil gebruiken.

tcms, connecting to postgres ….

This evening I rewrote the first class (database connection and query handling) to use postgreSQL. It seems to work fine….

$this->conn = pg_connect(
"host='" . $this->tcmshost .
"' port='" . $this->tcmsport .
"' dbname= '" . $this->tcmsdb .
"' user= '" . $this->tcmsuser .
"' password= '" . $this->tcmspwd ."' ");

The next step will be to change the login code, menu code (I love to see how pg will parse my treebuilder with only one query) etc…

First steps in converting MySQL data to PostgreSQL

Yesterday I installed PostgreSQL using Fink. I could have chosen to download and install the .dmg from Enterprisedb but that one gave me some troubles (might be the machine I am using for development).

Today I started to look in converting my MySQL data to PostgreSQL. It seems there is a tool called mysql2pgsql. It converts a dump file into a format postgres can read. Once run you will have translated auto_increment fields for a primary key to sequences:

CREATE SEQUENCE “tcms_content_id_seq”  START WITH 786 ;

CREATE TABLE  “tcms_content” (
“id” integer DEFAULT nextval(‘”tcms_content_id_seq”‘) NOT NULL,
“content”   text NOT NULL,
“page_id” int CHECK (“page_id” >= 0) NOT NULL,
primary key (“id”)
)   ;

I was able to import it into PostgreSQL and using pgAdmin it shows the constraints, tables, etc..

The next time I will work on tcms I will start translating the php code to connect to PostgreSQL and see what will need to be altered.

tcms, changing to postgresql

Before the season holidays I decided to do some more development on tcms. I must say I was a bit lazy and was searching an excuse for myself to not do any development. Maybe there was a reason for it if I look back at the last couple of weeks.

On Januari the 4th I posted about Monty who seems to spam all users, even those addresses he seems to have sold to Sun. I replied on the page where he points to in his e-mail stating I think he is not entitled to those addresses. He never responded but I did catch up reading about his plans. He seems to look for a way where someone can own the database he sold to Sun for a lot of money on a dual license.

This made me decide to not select MySQL as a base development database and switch to PostgreSQL. I must be honest, it’s not the only reason. PostgreSQL has more features that I would like to use and seems to be more stable.

It might set back tcms development for a short while but I hope it will be a more robust solution without doubting over the support in the future.

Is Monty spamming?

Since I do a lot of business using MySQL I am subscribed to a few newsletters from mysql.com. I used to be subscribed at my home e-mail address but since I am working for a company for more then a few years now I also subscribed with my business e-mail address.

Today I received an email form helpmysql.org stating the following:

Hi!

I am contacting you because you have in the past shown interest in
MySQL and from that I assume you are interested in the future
well-being of MySQL.

Now you have a unique opportunity to make a difference.  By signing
the petition at http://www.helpmysql.org you can help affect the
future of MySQL as an Open Source database.

You can find more information of this on my latest blog post at:
http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html

Help us spread the world about this petition!
http://www.helpmysql.org is available in 18 languages and every vote
is important, independent of from where in the world it comes!
If you know people that are using MySQL, please contact them and
ensure they also sign the petition!

Regards,
Monty
Creator of MySQL

PS: If you already have signed the petition or know about it, sorry for
reminding you about this! Because of the importance of this issue,
I am trying to contact every person that I have ever communicated
with regarding MySQL.

Although I am very thankful for the free software there are some limits in how data is supposed to be handled once someone sells his product and company to another company. Currently it looks like he is using everyone who ever subscribed to the list in the past to spam his cry for help. Is he allowed to use this data?

As far as I know the owners sold MySQL to Sun and they sold it to Oracle. I do not think he is entitled to use people who are subsribed (and not at all if it is used to send massive unsolicited email). What will he do if the users are not responding? Sell the data to another company?

Why you should not use OSX for Perl development

Today I found the time to work on an import module for tcms. The plan was to write some simple modules in perl te browse the webgui users on database foo and chain the user and group id into a seperate table on database bar.

This could be done using functions and procedures in MySQL but I decided to use some perl to do this. I wrote some 20 lines of code to create a connection to both of the databases, get some results and loop through them.

Bingo, error one…

Can’t locate DBI.pm in @INC

No problem, I am being able to use apt-get on this macmini, and installed it. Then the next error showed up:

Can’t locate DBD/mysql.pm in @INC

Again, should not be a problem. Apt-get should be my friend… I used apt-cache to look for the correct module name but then found that MySQL isn’t supported..

dbd-pg-pm581 – upgrade package for dbd-pg-pm581
dbd-pg-pm581-ssl – upgrade package for dbd-pg-pm581-ssl
dbd-pg-pm586 – upgrade package for dbd-pg-pm586
dbd-pg-pm586-ssl – upgrade package for dbd-pg-pm586-ssl
dbd-pg-unified-pm581 – PostgreSQL Perl DBI module
dbd-pg-unified-pm586 – PostgreSQL Perl DBI module
dbd-sqlite-pm581 – Self Contained RDBMS (based on SQLite3)
dbd-sqlite-pm586 – Self Contained RDBMS (based on SQLite3)
dbi-pm581 – Perl Database Interface by Tim Bunce
dbi-pm586 – Perl Database Interface by Tim Bunce
libdbi – Database Independent Abstraction Layer for C
libdbi-dev – Development package for libdbi
libdbi-shlibs – Shared libraries for libdbi

The above modules are the only ones supported by Fink it seems. Browsing for the lib on Google I did find Package dbd-mysql-pm588-4.005-11. So, why isn’t it in Fink?

I’m not giving up that easy so I tried to update Fink itself.

# fink selfupdate
fink needs you to choose a SelfUpdateMethod.
(1) rsync
(2) cvs
(3) Stick to point releases

Choose an update method [1]
Failed: selfupdate method ‘rsync’ requires the package ‘dev-tools’

Aaaargh

I should not only switch to PostgreSQL but I should also upgrade Tiger to a good debian machine. Even Debian Stable does not have these problems…

Frustrating.