[dba-Tech] The dark side of programming craftmanship

Hi Arthur,

Awesome.

Well, one of the first things I'd point out from a code maintainability point of view is that I'd try to separate out the business logic and the presentation in the code (to separate files, for instance). A common design pattern is MVC (model view controller) and it makes reading and updating code so much nicer. You could go and use a framework for this, but it is not absolutely necessary. If you want an example of what MVC style code looks like, check out nokenode.com . This is a sample project I wrote recently for a job interview (got me the job :) ) and there is a source code download link at the top. I am using the zend framework on top of PHP in that example, but it should be easy enough to grep.

The other thing I'd highly recommend from a security practice point of view is to not permit your code to execute complete SQL statements. Normally, I would use an ORM or OO abstraction of some sort, like Zend_db_table, but you can do just fine with the regular MySQL db adaptor if you make sure your queries are prepare with named parameters (kinda like placeholders) and then you just pass the actual inputs in when you execute the query. The database adaptor will sanitise your inputs for you to make your query safe to execute.

To give you an example:

$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';

$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));

$sth->execute(array(':calories' => 150, ':colour' => 'red'));

You can read more about it here:
http://php.net/manual/en/pdo.prepare.php

The reason you want to do this is a matter of security practices and avoiding nasty SQL injections. If you let people execute any sort of query, then they can pretty much do anything they want, as XKCD wonderfully exemplifies: http://xkcd.com/327/

One other thing I'd point out is that you mention that JavaScript can't call php code directly. This isn't strictly true anymore - we have Ajax to make asynchronous calls to the backend, which can return the results in JSON format for instance. This would greatly simplify the backend PHP code, since the PHP code no longer needs to generate much HTML and the user doesn't have to reload the entire page when making a query. You are probably familiar with jQuery by now, I imagine. JQ makes this dead simple to do with great cross browser support.

Lastly, I would highly recommend looking into some of the more popular frameworks out there. There is nothing wrong with doing things your own way, like pagination, but if someone else has done the work for you already, it makes life so much easier. :)

Other than that, congrats on your code. It looks pretty solid. Just want to say again, please don't take my advice as a form of criticism. It is not intended that way!

- Hans


On 2013-01-29, at 3:35 AM, Arthur Fuller <fuller.artful at gmail.com> wrote:

> Hans,
> 
> No offence taken, and any polish you'd care to add would be much
> appreciated.
> 
> A.
> _______________________________________________
> dba-Tech mailing list
> dba-Tech at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-tech
> Website: http://www.databaseadvisors.com