Making SQL Queries

This article is to summarize how do we make SQL queries using different languages and their methods. Making connection to the database will not be covered here.

In the examples, we will be querying “SELECT * FROM employees where eid =and dept =”

It is not recommended to use SQL statements without placeholders in order to reduce the risk of SQL injection.

Java

In Java, we can use JDBC, Hibernate, or some other database frameworks to interact with databases. Generally, I would prefer to use methods that allow me to insert “values” into an sql query. With JDBC, we can use PreparedStatement method. There is also createStatement method, where you insert user-supplied values into the query directly.

Basically the flow looks like this:

  1. Get a “Connection” object, with DriverManager
  2. From “Connection” object, we create a “Statement” object with sql statement.
  3. From the statement object, we generate “ResultSet”.
int employeeId = 512 // we will query for employee with id 512
int deptId = 206 // from department 206

Connection connection = DriverManager.getConnection(...); // please fill in
PreparedStatement statement = connection.prepareStatement("SELECT * FROM employees WHERE eid = ? AND dept = ?");
statement.setInt(1, employeeId); // use setString if the argument is a String
statement.setInt(2, deptId);
ResultSet rs = statement.executeQuery();
while (rs.next()) { // Iterate the result set
// ...
}

PHP

PHP has more methods to interact with database. It also depends on the module the php interpreter is built with. We can use MySQL extension, PostgreSQL extension, or ADODB or PDO as generic abstraction interfaces.

With mysqli extension

$db = new mysqli('localhost', 'user', 'password', 'world');
$statement = $db->prepare('SELECT * FROM employees WHERE eid = ? AND dept = ?');
$statement->bind_param('dd', $employeeId, $deptId); // types and variables

$employeeId = 512;
$deptId = 206;

$statement->execute();

NOTE: Why mysqli instead of mysql command? If we are using MySQL v4.1.3 and above, PHP manual recommends to use mysqli which is an improved version. Reference.

With PostgreSQL extension

$db = pg_connect("dbname=world");

$employeeId = 512;
$deptId = 206;

$result = pg_query_params($db, 'SELECT * FROM employees WHERE eid = ? AND dept = ?', array($employeeId, $deptId));

With ADODB

$db = NewADOConnection('mysql');
$db->Connect('localhost', 'user', 'password', 'world');
// Can also use $db = NewADOConnection('mysql://$user:[email protected]$server/$db?persist")
$rs = $db->execute('SELECT * FROM employees WHERE eid = ? AND dept = ?', array($employeeId, $deptId));
while($array = $rs->FetchRow()) {
//...
}

With PDO

$db = new PDO('mysql:host=$host;dbname=$dbname', $user, $pass);
$employeeId = 512;
$deptId = 206;

// We will use unamed placeholders.
$statement = $db->prepare('SELECT * FROM employees WHERE eid = ? AND dept = ?');
$statement->bindParam(1, $employeeId);
$statement->bindParam(2, $deptId);
$statement->execute();
// ...

Running WordPress with Nginx on ArchLinux

I just moved this blog over to Nginx server from Apache httpd server. I’m pretty satisfied with the overall result. I had to take some time to convert my current httpd configuration over to Nginx, since the new server does not support .htaccess or mod_redirects. This is my current requirements for move over:

  • The site is available on both HTTP and HTTPS.
  • “wp-admin” session is forced to use SSL.
  • I have “quicklook” (to check my server status) and “webalizer” directories under the blog, and they are protected by HTTP BasicAuth.
  • HTTP BasicAuth is to be carried out via SSL.
  • To enforce gzip compression on HTTP connection while disabling it on HTTPS.

Basically I followed the ArchLinux wiki for the implementation, and I will briefly describe what I did.

Nginx (pronounced “Engine X”) is a light-weight open-source http server. Its low resource consumption is the primary purpose for the moveover, and it’s suitable for my server on the cloud.

Firstly, I needed to install the package. And installed “php-cgi” package which is used to provide fastcgi interface to PHP.

~$ sudo pacman -S nginx php-cgi

Then, I configured fastcgi daemon, and add it to rc.d. So the following script was needed to be added to /etc/rc.d as “fastcgi”

#!/bin/bash

. /etc/rc.conf
. /etc/rc.d/functions

case "$1" in
  start)
	stat_busy 'Starting Fastcgi Server'
	if /usr/bin/php-cgi -b 127.0.0.1:9000 &
	then
		add_daemon fastcgi
		stat_done
	else
		stat_fail	fi
	fi
	;;
  stop)
	stat_busy 'Stopping Fastcgi Server'
	[ -e /var/run/daemons/fastcgi ] && kill $(pidof php-cgi) &> /dev/null;
	if [ $? -gt 0 ]; then 
		stat_fail
	else
		rm_daemon fastcgi
		stat_done
	fi
	;;
  restart)
	$0 stop
	$0 start
	;;
  *)
	echo "Usage: $0 {start|stop|restart}"
sac

And I gave it an executable permission:

~$ sudo chmod +x /etc/rc.d/fastcgi

What that script does is to have php-cgi process to listen on port 9000. Now, we would be able to start/stop/restart the daemon with “sudo /etc/rc.d/fastcgi start”. But the script will not be automatically started when the unit is rebooted. It needs to be added to /etc/rc.conf. So I added fastcgi to the rc.conf. Here’s the snippet.

...
DAEMONS=(syslog-ng ... fastcgi nginx ...)
...

Then I edited the /etc/nginx/conf/nginx.conf file to point to my blog physical directory. We need to add two servers, one for HTTP and one for HTTPS. This is my sample configuration for server myfineblog.local

    server {
        listen       80;
        server_name  myfineblog.local;
        access_log      /var/log/httpd/myfineblog.local-access.log;
        error_log       /var/log/httpd/myfineblog.local-error.log;
        root            /srv/http/myfineblog;
        gzip            on;

        location ~ ^/(wp-admin|quicklook|webalizer)/* {
            rewrite ^/(.*) https://myfineblog.local/$1 permanent;
        }

        location / {
            index  index.html index.htm index.php;
            root                /srv/http/myfineblog;
            if (!-e $request_filename) {
                rewrite ^.+/?(/wp-.*) $1 last;
                rewrite &.+/?(/.*\.php)$ $1 last;
                rewrite ^(.+)$ /index.php?q=$1 last;
            }
        }

        location ~ \.php$ {
            fastcgi_pass   127.0.0.1:9000;
            fastcgi_index  index.php;
            fastcgi_param  SCRIPT_FILENAME  /srv/http/myfineblog/$fastcgi_script_name;
            include        fastcgi_params;
        }
    }

Line 3 defines the server name (so we can configure virtual hosts based on names).
Line 4-5 defines the access logs for this web site.
Line 6 is the physical location of the web site on local system.
Line 7 is used to turn on gzip.
Line 9-11 is redirect to SSL by sending HTTP redirect if the uri contains any of wp-admin or quicklook or webalizer)
Line 13-21 is the definition of website directory and an equivalent scripts for Apache’s mod_rewrite.
Line 23-29 is the connection to the fastcgi daemon we configured above. It is *important* to change the SCRIPT_FILENAME variable to suit the real physical path of the wordpress script.

To enable SSL server, I assume we already have the certificate and key for the website. The configuration looks the same but it will have SSL options enabled and Basic HTTPAuth section for a certain directories.

    server {
        listen          443;
        server_name     myfineblog.local;
        ssl                     on;
        ssl_certificate         /etc/ssl/certs/myfineblog.crt;
        ssl_certificate_key     /etc/ssl/private/myfineblog.key;
        ssl_session_timeout     5m;
        ssl_ciphers             HIGH:MEDIUM;
        ssl_prefer_server_ciphers       on;
        ssl_protocols           SSLv3 TLSv1;

        root                    /srv/http/myfineblog;
        access_log              /var/log/httpd/myfineblog.local-ssl_access.log;
        error_log               /var/log/httpd/myfineblog.local-ssl_error.log debug;
        gzip                    off;

        location ~ ^/(quicklook|webalizer)/* {
                auth_basic      "Private Section";
                auth_basic_user_file    /srv/http/myfineblog/.htpasswd;
        }
        location / {
                index   index.html index.htm index.php;
                root    /srv/http/myfineblog;
        }
        location ~ \.php$ {
            fastcgi_pass   127.0.0.1:9000;
            fastcgi_index  index.php;
            fastcgi_param  SCRIPT_FILENAME  /srv/http/myfineblog/$fastcgi_script_name;
            fastcgi_param  HTTPS on;
            include        fastcgi_params;
        }
    }

This configuration turned on “SSL”, disabling SSLv2 and weak ciphers. It enabled HTTP Basic Authentication for two directories. I disabled gzip on SSL stream. And it tells the fastcgi server to turn HTTPS on.

And started the daemons with “/etc/rc.d/fastcgi start” and “/etc/rc.d/nginx start”.

A Walk in the Clouds

I’ve moved this site over to the cloud servers, by Rackspace from my previous shared host. Actually I was looking for a cloud server and cloud space so that I can play with Hadoop. I found Amazon EC servers and S3, but their services charges are expensive for me. While searching for alternatives, CloudServers caught my attention.

It is cheaper than Amazon services, but at the moment I don’t think I can test Hadoop on CloudServer and with CloudSpace. I’m using it more like a virtual private server, that gives me “root” access. The good thing is you can modify the resources as you wish, so I would say it’s quite scalable. You are also charged by hours (uptime). Rackspace will also charge you even if you turn off the machine. They will not charge after we have deleted the server. If you want to test something for a project, you can just subscribe for desired amount of memory and disk space. And delete the server after it’s been used. We will only be charged for those period. That’s the flexibility that I prefer.

I’ll see what I can do with my server, and update the blog again.

Cake, Drake, Drupal and a Duck

I have been studying both Cake and CI for a while, and I found both are just indispensible. Both have their strengths and weaknesses. But according to the blog, whose owner tested them with a benchmarking tool, CI seemed to be able to carry out more requests in a second than Cake and Symfony for a simple “echo” function. Symfony wasn’t my choice, since it doesn’t support PHP4.

I like Cake for its ORM feature and Ajax helpers. I like CI for it’s flexibility. If CI had built-in support for ORM, I would have never used Cake again. Coding Cake is strict but it makes our life easier when we follow Cake’s naming convention. I don’t even have to write SQL even when working with tables in relationships. CI has an add-on called Rapyd to provide ORM. For the time being I’m feeling more comfortable with Cake’s built-in ORM.

I have already developed some projects, including Shwe Pyi Hein Monastry, using CI. It is less restrictive than Cake, and it is the one I studid first, I’m more familiar with that. But it doesn’t stop me from using Cake also 😉

Drupal is one of the best CMS I’ve ever seen. In addition to Drupal, I have used PHP-Nuke, Postnuke, e107, Mambo, and Joomla. But now I have removed PHP-Nuke for its overhead and bugs, Postnuke for slow development. e107, Mambo/Joomla have got a lot of built-in features compared to Drupal.

Although I have not tested yet, Cake can be placed as a plug-in/extension for Drupal (via project Drake), and Joomla. If I’m too lazy to learn Drupal API or Joomla API, it will be my saviour.

Joomla! Multiple Vulnerabilities

If you are using Joomla version 1.0.10 or less, it’s time to update again. According to many security advisories, it’s got high known and unknown impacts.

Description:

Some vulnerabilities have been reported in Joomla!, where some have unknown impacts, and others can be exploited by malicious people to conduct cross-site scripting attacks and bypass certain security restrictions.

1) Some input validation errors exists in the “mosMail()” and “JosIsValidEmail()” functions.

2) An unspecified error exists in PEAR.php.

3) An unspecified error exists due to globals.php not being included in administrator/index.php.

4) Insufficient access control checks exists due to missing defined( ‘_VALID_MOS’ ) checks and certain errors in the Admin “Upload Image”, Admin “Popups”, and “com_content” functionalities.

5) Certain unspecified errors in the “do_pdf” functionality and in the handling of the emailform com_content task can be exploited to bypass the user authentication process.

6) Some unspecified input passed via the Admin “Module Manager”, Admin “Help”, and Search functionalities isn’t properly sanitised before being returned to the user. This can be exploited to execute arbitrary HTML and script code in a user’s browser session in context of an affected site.

Some other security related issues have also been reported.

The vulnerabilities have been reported in versions prior to 1.0.11.

The solution is to update it ASAP.

Better Than Flowers On Valentine’s Day

Here comes Valentine’s Gift from Yahoo! For our everyday’s HTML and AJAX needs, Yahoo! has just given us Yahoo! User Interface Library, and all the components are released under Open Source License (BSD License). Better than flowers, huh?

Yeah, we’ve got libraries and that pushes us further into endless possibilities of creating useful applications. To make best use of those tools to create applications, there’s got to be some guidelines and documentations. Again, Yahoo! provides us Design Pattern Libarary. To go further, there’s a blog for everything we need to know about those libraries and design patterns.

Feeding RSS

RSS is just an XML file that follows XML 1.0 specification. RSS simply allows us to syndicate a website contents into another website. If you want to add RSS feeds into your website, I guess we will need the read the followings:

Feed Parsers parse the feeding XML into distinctive parts without sweat. Cool, isn’t it? Currently I’m into the following parsers.

Someday, I’ll post some tutorials about those API’s and Libraries.

Mambo, OpenSourceMatters and Joomla!

Mambo OpenSource is a good CMS software with which I have implemented half a dozen websites. It has already won “Best Open Source Solution” awards by LinuxWorld 2005. Mambo was born when an Australian company, Miro, put their codes on SourceForge and get it licensed under GPL. The Open Source community welcomed the project and continued developing the CMS.

The progress went well until Miro decided to establish a non-profit organization for Mambo, Mambo Foundation. This is the letter from Peter Lamont:

As you know, there have been several important and exciting changes in the Mambo community lately. The formation of the Mambo Foundation, a non-profit organisation whose mission is to manage the Mambo project, will ensure the security, longevity and success of Mambo and its community of users. Our next goal is to put together a strong organizational structure and encourage coordination between teams including core development, maintenance, documentation, translation, third-party development, membership, finance, administration and advocacy who will work under the Foundation umbrella.

The Mambo project has been extremely fortunate in having such a vital community that has advocated Mambo in many ways and helped it grow into the award-winning product it is today. We want to thank you for your continued support, which will continue to be critical moving forward.

We will be launching the Mambo Foundation website soon, where you will find general information about the Foundation, how to join as a member, FAQ’s, and much more.

But the core development team members didn’t like the idea and started to form OpenSourceMatters. The reason why they quit is described in their website:

The Mambo Foundation was formed without regard to the concerns of the core development teams. We, the community, have no voice in its government or the future direction of Mambo. The Mambo Steering Committee made up of development team and Miro representatives authorized incorporation of the Foundation and should form the first Board. Miro CEO Peter Lamont has taken it upon himself to incorporate the Foundation and appoint the Board without consulting the two development team representatives, Andrew Eddie and Brian Teeman.

Although Mr. Lamont through the MSC promised to transfer the Mambo copyright to the Foundation, Miro now refuses to do so.

Finally, the developers who coded Mambo will continue developing Mambo as Joomla , as the name Mambo is trademarked by Miro. It is also worth noting that all major Mambo sites like Mamboserver.com, Mamboforge.net, and Mambo-foundation.org are all owned by Miro and are no longer under the control of the recently departed Mambo developers.

Now, which side are you on?