Farewell KMail2

    I've been testing the new akonadi based KMail2 and I got dissapointed. My last version tested was from KDE 4.7.0 and now, I decided to change to another software for productivity reasons.

    The main reason for this change is that KMail fails to mark messages as read and it fails to differentiate read messages from unread (for example, like previous KMail did, having different colours for read and unread ones).

    I've already posted a bug against it (KDE bug #276893), but I didn't receive any feedback from developers as of today.

    So I finally decided posting an image wich will show this problem: KMail does not mark ANY message as read, so it is hard to keep your messages up to date, since I have to remember which messages I have read and which I have not.

    My last hope is that they take seriously this severe blockstopper bug as soon as possible... In the mean while, I will be using another software for managing my emails, so.... After several years... farewell KMail2

About SQL injection

    Despite this kind of attack is very old, it is still the primary source of database and website attacks so it really deserves a little speak here and everywhere. In an ideal world, maybe this will not worth a cent, but unfortunatelly we are not in an ideal world.

    Many people say that this is due to malicious people and/or hackers fault, but the guilt comes from both sides: bad programmers and bad people.

    I'll explain this kind of attack with an example (in PHP, but it affects SQL independently of language used).

    Suppose we have a login validation this way:

$sentence="SELECT COUNT(*) FROM Users WHERE username='".$_GET['user']."' AND pass='".$_GET['password']."'";

    (And later in your code check if result>0 in order to check if login was successfull)

    The problem here comes from not checking user inputs, in a good case, sentence will not be a special one, for example: (user input in bold) 

SELECT COUNT(*) FROM Users WHERE username='bob' AND pass='foo';

    But there is a problem, if input is not sanitized, this SQL sentence could be transformed and executed into many SQL sentences allowing a user, for example this can happen because of not sanitizing _GET array:

(1) SELECT COUNT(*) FROM Users WHERE username='bob' OR 1=1;--' AND pass='foo';
or
(2) SELECT COUNT(*) FROM Users WHERE username='bob'; DROP TABLE Users;--' AND PASS='foo';
(3) SELECT COUNT(*) FROM Users WHERE username='bob'; SELECT creditcardnumber FROM Users;--' AND pass='foo';

    Those two sentences with user input marked as bold are self explanatory, but you can see how 1 sentence can be modified to allow you to login with an account which is not yours(1), to destroy data(2) or to gather other kind of sensitive information(3).

    Maybe you are thinking that if this problem is old, it does not worth to speak of it: ERROR!
From time to time, a huge company(Sony) or government agency (CIA) are attacked using this method. This leads me to think: How poor their hired programmers work, and how little do companies value our personal and sensitive data!

    So as this problem has been proved to be relevant and important, let's investigate some solutions.

• Prepared Statements and parametrized queries:

    This seems the best solution: A SQL sentence is no longer crafted by join two or more strings, and SQL keywords are splitted from data. (and also, real sentence is not sent over the network every time it is executed). An example of prepared statement could be: (in postgres)

SELECT COUNT(*) FROM Users WHERE username=$1 AND pass=$2

    This time, postgres already know what type are $1 and $2 and they are parsed accordingly, but the more important fact, $1 and $2 parameters will be treated as a complete string, eliminating the posibility of crafting another sentence from this one. So in this case a malicious input will be treated like:

SELECT COUNT(*) FROM Users WHERE username='bob''; DROP TABLE Users;--' AND pass='foo';

    In this case, as all username will be treated as a whole parameter, this sentence will likelly return 0 rows as a result instead of harming data.
Try to AVOID SQL crafted strings in your source code and use prepared statements!

• Sanitize inputs: While this approach is good enough, I recomment to apply in conjunction with previous one. It consists on checking and escaping any non standard character and convert them to avoid SQL sentence breakage. (In case you can't avoid crafting sentence)
  

$input=pg_escape_string($_GET['username']); and apply it to SQL crafted string, this way, any ' or " character that could break your SQL sentence will be quoted disallowing it to harm your sentence.

    To sum up: Even governments and big companies do not care enough their databases to spend a little time to check source code to avoid this kind of problems.

Dropping ReiserFS (v3)

    As the time goes by, Reiser FS v3 is becoming outdated, but this is not the reason I will drop it in the near future in favour of ext4.

    Last night, I was renaming a couple of files (14GiB ones), and my laptop's battery went out.
When I rebooted and after checking disk consistency with reiserfsck, one of those files became empty! (I must explain that that file was not in cache nor it had been written recently, it has been there since I booted)

    This is just unaceptable for a journaled and "modern" file system, and since Hans Reiser is in prison and namesys seems to have discontinued Reiser FS v4 support, I will DROP ReiserFS forever.

Rest in peace ReiserFS

PostgreSQL Inhertance and Constraints problem solved!

Warning: Using stored procedures and triggers in PostgreSQL is addictive!

    I've been playing around with stored procedures using PLpgSQL language, just to try performance improvements and they are just addictive!

    For example, PostgreSQL has the ability to inherit all data from one or more tables to other table, but unfortunately, any constraint will be inherited as of 9.0.4, and thus, you can not make a FOREIGN KEY constraint agains parent table.

    Fortunately, you can workaround this issue with a little performance impact by using stored procedures, triggers and a separate table for that constraint.

    For example, imagine this scenario:

CREATE TABLE account (
    id SERIAL,
    username VARCHAR(50) UNIQUE NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE employee (
    salary MONEY(6,2) NOT NULL
) INHERITS (account);

CREATE TABLE problematic (
    id SERIAL,
    account_id INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (account_id) REFERENCES account(id)
);

    Suppose we have some data:

INSERT INTO employee(username,salary) VALUES ('foo','1280.90'),('bar','3460.75');

    We will have account filled with those usernames as well as employee filed with the rest of values but the problem will come with a:

INSERT INTO problematic(account_id) VALUES(1);

    Since inheritance does not cover constraints, this last INSERT will fail because it will not find corresponding id on account table, despite it is really there.

    But there is NOT all lost! Fortunately we can place triggers on employee to fill and maintain other intermediary table values for simulating that constraint.

    Solution will be to create that table:

CREATE TABLE account_constraint (
    id INTEGER NOT NULL,
    PRIMARY KEY (id)
);

    And to change employee's FOREIGN KEY constraint this way:

FOREIGN KEY (account_id) REFERENCES account_constraint(id);

    And now... how to keep account_constraint table up to date always and in a safe way? The solution rather than code it by yourself in all clients is to use a procedure and place triggers which will fire BEFORE INSERT, UPDATE and DELETE.

    This could be an example of this procedure:

CREATE OR REPLACE FUNCTION account_id_constraint_handler() RETURNS trigger
AS $$
    BEGIN
       IF (TG_OP = 'INSERT') THEN
          INSERT INTO account_constraint(id) VALUES (NEW.id);
       ELSE
          IF (TG_OP = 'UPDATE') THEN
             UPDATE account_constraint SET id=NEW.id WHERE id=OLD.id;
          ELSE
             DELETE FROM account_constraint WHERE id=OLD.id;
             RETURN OLD;
          END IF;
       END IF;
       RETURN NULL;
    END;
$$
Language 'plpgsql';

    And the last thing to do is to set triggers this way:

CREATE TRIGGER insert_accountID
AFTER INSERT ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();
CREATE TRIGGER update_accountID
AFTER UPDATE ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();
CREATE TRIGGER delete_accountID
AFTER DELETE ON user_data
FOR EACH ROW
EXECUTE PROCEDURE account_id_constraint_handler();

    You can now alter any data safely maintaining FOREIGN KEY CONSTRAINT!
And the best: database will do all work needed with only a little impact, since PostgreSQL stores procedures in a precompiled state to improve performance a bit.

    Remember that, once you start using stores procedures (or functions) you will not stop as stated in the warning above!

Microsoft and its unfair selling techniques

    Microsoft, did it again: left its users alone with new versions of its programs.

    I am referring for example to Internet Explorer 9, new Version of Windows Live Messenger, etc....
Rather than make it compatible with its own older O.S., they just require you to upgrade (and BUY) another version of their O.S.

    This is TOTALLY UNFAIR! Specially taking into account the fact that ANY of its O.S. is FREE, then, the result is that you pay for an O.S. and be left alone in some time if you don't pay again (and thus, forced to pay another O.S.).

    What demonstrate this kind of behavior from Microsoft?

• They does not value its own customers: you pay, and whenever they say they won't support you, they won't and you can't complain (it is in their TOS).
• If for any cause, a security risk is discovered or an important upgrade in other program is made, if they don't release it for their O.S., you can't simply have it...

    What means this to me?

• It makes me happy to be a Linux ONLY user since 7 years now, without using any software from Microsoft.
• I don't get fooled by a company which requires me to pay for an limited product, full of security holes (in its long history), and full of bugs, and, of course, trapped to pay to them every X years forcedly!

Conclusion: Will you get fooled by Microsoft?

Dropping MySQL, and adopting PostgreSQL

    Finally I had the time to dig a bit this issue, and finally, decided to drop all MySQL support in favor of PostgreSQL.

    Despite PostgreSQL is not as known nor as used as MySQL, it has MANY advantages:

• Referential Integrity: MySQL's default engine (MyIsam) does NOT support any kind of referential integrity, which could lead to corruption and inconsistencies in DB.
• On the other hand, the other MySQL's engine which does support referential integrity (InnoDB) is very much slow. In my case, only to create a table with a referenced FOREIGN KEY to another table takes ~1 second! And cascade deleting/checking/updating also takes much more time then postgre takes.
• Extended Data Types: With PostgreSQL you don't have only a few data types like you have in MySQL. With PostgreSQL you have, cidr (for IP addresses), money, and other data types, as well as user defined data types.
• Prepared Statements: As stated in my previous post, MySQL had support for prepare statements, but you couldn't not give a name to them (unless you use my class, or code it by yourself), but with PostgreSQL, you can even give natively a string name! (even inside client).
• Performance: In my case, with my databases, and googling a bit, you can see that PostgreSQL has much better overall performance than MySQL.

    To sum up, I though that since Oracle bought MySQL, it would be improved a lot, but it seems only a commercial action rather than a really interest in MySQL from Oracle.

MySQL's Prepared Statements made really easy with PHP

    I've been programming a bit with php for first time, realizing that it is true what my friends told me: it is really close to C/C++.

    I managed to have an abstract class to handle and execute SQL's prepared statements, and a bit more that I want to share with world.

    But first, just an example of how I made prepared statements really easy to handle with only inheriting from my MySQLDatabase class! Specially if you program a singleton class for your database connection and let __construct and __destruct do its magic!

    You can really inherit from this class and see how easy is implementing prepared statements in your php project!

Example of usage

    And now... the code itself!

Code for MySQLDatabase class

    I hope someone find it usefull!