Welcome to the blog of Marcel Oelke from Frankfurt, Germany.

Archive for November, 2005

Dynamic Materialized Views in MySQL

12 Nov, 2005  3 Comments

In one of my latest postings I mentioned a way to create semi materialized views in MySQL. The problem was that the solution required a stored procedure for every materialized view. That's very annoying. So I looked for a more general and dynamic approach. The result is another stored procedure with 3 parameters:

  • the name for the source table / view
  • the primary key columns of the source table / view
  • the desired name for the materialized view

Basically this procedure does the following:

  • It drops the materialized view if it already exists
  • It creates the materialized view with the structure and data of the source table
  • It adds a primary key to the newly created materialized view

However the procedure does not create triggers on the table. This could be an improvement for a next version ;)
To call the procedure use:

PLAIN TEXT
SQL:
  1. CALL create_mview('big_table', 'id', 'view_m_big_table');

And here's the procedure:

PLAIN TEXT
SQL:
  1. DROP PROCEDURE IF EXISTS create_mview;
  2. DELIMITER //
  3. CREATE PROCEDURE create_mview (IN source_table VARCHAR(150), IN primary_key VARCHAR(150), IN target_table  VARCHAR(150))
  4. LANGUAGE SQL
  5. NOT DETERMINISTIC
  6. CONTAINS SQL
  7. SQL SECURITY DEFINER
  8. MODIFIES SQL DATA
  9. BEGIN
  10. -- declare some variables ...
  11. DECLARE m_view_name VARCHAR(150);
  12. DECLARE create_m_view_sql TEXT;
  13. DECLARE drop_m_view_sql TEXT;
  14. DECLARE add_primary_key_sql TEXT;
  15. --
  16. -- create the sql queries to drop the materialized view, create the tmaterialized view
  17. -- and add a primary key to the materialized view.
  18. SET m_view_name:=concat('view_m_', source_table);
  19. SET @drop_m_view_sql:=concat('DROP TABLE IF EXISTS ',target_table);
  20. SET @create_m_view_sql:=concat('CREATE TABLE ', target_table , ' SELECT * FROM ', source_table);
  21. SET @add_primary_key_sql:=concat('ALTER TABLE ', target_table , ' ADD PRIMARY KEY (', primary_key ,')');
  22. --
  23. -- drop the materialized view if it exists
  24. PREPARE stmt1 FROM @drop_m_view_sql;
  25. EXECUTE stmt1;
  26. --
  27. -- create the table
  28. PREPARE stmt2 FROM @create_m_view_sql;
  29. EXECUTE stmt2;
  30. --
  31. -- add the primary key
  32. PREPARE stmt3 FROM @add_primary_key_sql;
  33. EXECUTE stmt3;
  34. --
  35. DEALLOCATE PREPARE stmt1;
  36. DEALLOCATE PREPARE stmt2;
  37. DEALLOCATE PREPARE stmt3;
  38. --
  39. END; //
  40. DELIMITER ;
  41.  

You can download the procedure also.

Checking password strength using md5.rednoize.com and AJAX

10 Nov, 2005  7 Comments

With some inspiration from Ben Ramsey i created a little demo for using my md5 database during the signup process on a website.

This example demonstrates the use of the MD5 database at http://md5.rednoize.com and AJAX to check password strength during signup on a website. After supplying a username and a password, a md5 hash of the password is generated using Paul Johnston's md5 javascript library.

The hash of the password (not the password itself) is then sent to http://md5.rednoize.com. If the website returns a result for the given password (hence the hash password combination is stored in the md5 database) it can be regarded as "insecure". Because the md5 hash of the password, and not the password itself is transferred, no sensitive data will be saved at md5.rednoize.com.

For sure not every password that is not stored in the MD5 database can be considered secure. I recommend adding some extra checks (existence of upper and lowercase characters, numbers, special characters and so on) to increase the password strength.

You can see the example in action here: http://md5.rednoize.com/ajax/

Feel free to use the code in this example any play with it ;)
To implement this on your own webserver you would need some kind of proxy script that redirects the AJAX calls from your own server to the md5 database.

Update:

I recommend saving passwords using salted md5 hashes. Salting in short: "When the user sets a password, a short string called the salt is suffixed to the password before encrypting it; the salt is stored along with the encrypted password so that it can be used during verification. Since the salt is different for each user, the attacker can no longer use a single encrypted version of each candidate password. If the salt is long enough, the attacker must repeat the encryption of every guess for each user, and this can only be done after obtaining the encrypted password record for that user."

Here's a little example using salted passwords:

To authenticate users on your website (login) your probably using a SQL statement like this one:
SELECT user_id, username FROM users WHERE passsword = MD5('thepassword');

This is insecure. If someone would know the md5 hash of the password, and the password is weak, it could be "reversed" using the MD5 database.

Use salted passwords to avoid this:
SELECT user_id, username FROM users WHERE passsword = MD5(user_id || 'some_secret_string' || 'thepassword');

Its also possible to store the salt along the password in the database.

Hamachi - Secure Mediated peer to peer VPN

7 Nov, 2005  1 Comment

Hamachi Screenshot
Hamachi is a piece of software that belongs to the very rare category "Double-click to install and it works". If you ever wanted to build a "private" VPN, take Hamachi into consideration.
"Hamachi is fast, secure and simple. It is also free." The client is available for Windows and Linux.

The Hamachi website describes how it works:
"Hamachi is a UDP-based virtual private networking system. Its peers utilize the help of a 3rd node called mediation server to locate each other and to boot strap the connection between themselves. The connection itself is direct and once it's established no traffic flows through our servers."

The installation procedure is very easy:
In short: download, double-click, join or create a network, invite your friends, done.

After install you've got a new network adapter with a 5.x.x.x IP attached to it. Now you can create a "Network" (optionally with a password) and start inviting your friends to join your network. After inviting your friends your see their IP addresses in the "contact window". It’s now possible to ping them, browse their files or whatever. You can only ping the IP addresses that are in your network / contact list.

The great thing about Hamachi is that it works with almost any network configuration. It doesn’t matter if your behind a firewall or nated. Hamachi is also secure. The complete traffic between the peers is SSL encrypted.

Most people use Hamachi to play multiplayer games using the VPN.
I use Hamachi to grant my friends access to the Apache virtual hosts that are running on my development machine.

The only disadvantage of Hamachi is that the mediation-server is not available. That’s why I only use it for "private" things. To connect to my companies network I’m using OpenVPN which is also quite comfortable.