MySQL in PHP

To quote the MySQL website: “The MySQL native driver for PHP is part of the official PHP source code repository at http://php.net. It is contained in the PHP source code as of PHP 5.3.”  This tells me that PHP accesses MySQL with a native c driver (i.e. very fast).

Quote from http://us3.php.net/manual/en/function.odbc-connect.php on 4 Sept 2009

Irrational fear of scriptlets

Just taking a note of a forum I found online which discusses scriptlets in JSP code.

http://forums.sun.com/thread.jspa?threadID=318105&start=30

Prepared Statements

MySQL included capability for a special type of SQL query called a prepared statement.  Instead of concatenating data into the SQL string you use question marks to indicate a parameter and then bind parameter values to the statement.

According to this article prepared statements automatically take into account characters that could be used to form a SQL injection attack.  At the same time they could increase performance where the query is executed more than once.

If I use prepared statements it will be solely for the security benefit since the majority of my queries are only executed once per page load.

Article found on 24 August 2009 at http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

SQL Injection

SQL injection is basically where someone takes advantage of the dynamic generation of SQL code to hack the system.  At a login form for example they might terminate the username with a ‘ character and append “or 1=1;–” which would instantly log them in without a password.

According to this site you should do the following to guard against sql injection:

• All queries should be parametrized.
• All dynamic data should be explicitly bound to parametrized queries.
• String concatenation should never be used to create dynamic SQL.

Article found on 24 August 2009 at http://www.owasp.org/index.php/Preventing_SQL_Injection_in_Java

Stored Procedures are EVIL

In searching for how to execute stored procedures on MySQL I encountered an interesting article by the title of this post.  The article was written by an experienced programmer about why they do now use stored procedures.  Some of the points they make are:

• Stored procedures are no longer needed to enforce security, user based security can be used along with views to allow or deny the needed access.
• Stored procedures are no longer more efficient than dynamic sql.
• Stored procedures are harder to maintain than sql in code.
• It is harder to maintain version control with logic divided between program and procedures.
• You are locked to the vendor of a specific DBMS.

The points made in the article are interesting and it would be interesting to have the efficiency and security comments tested.  There are however reverse arguments such as not having to write the same SQL multiple times and easier to restrict security.

Article found on 23 August 2009 at http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

A quick quote…

While reading about JDBC on Wikipedia I found the following paragraph:

“Typically, however, it would be rare for a seasoned Java programmer to code in such a fashion. The usual practice would be to abstract the database logic into an entirely different class and to pass preprocessed strings (perhaps derived themselves from a further abstracted class) containing SQL statements and the connection to the required methods. Abstracting the data model from the application code makes it more likely that changes to the application and data model can be made independently.”

I had already implemented an abstraction class to make it easier to perform comparable actions in each of the three languages.  The statement above provides support for my reasoning.

Found on 22 August 2009 at http://en.wikipedia.org/wiki/Java_Database_Connectivity

The statistics lie…

I found an interesting article pointing out that it is easy to make sweeping statements based on a requests per second value while ignoring the fact this value is strongly influenced by tiny differences in response time.  The makes me alert that if I present values as requests per second they should be qualified by the values that they are based on.

The article was found at http://www.therailsway.com/2009/1/6/requests-per-second on 21 August 2009 and was “Posted by Koz on Tuesday, 6 January 2009”.

Fair Tests (an article)

In looking for information on conducting fair tests I came across an article entitled “Fair tests: A do-it-yourself guide”.  While this article is looking from the perspective of scientific tests the same principles apply in what I am doing.

Article retrieved on 7 August 2009 from http://undsci.berkeley.edu/article/fair_tests_01

Site Analysis

To give me an idea of what I need to test it is worth taking a glance at existing sites.  Some important factors are as follows:

• A useful site must have a search function.  This is perhaps the most common way the site will be used so it is an important thing to build and test.
• Sometimes you access products by category instead of by search.  A page listing categories of some kind is found on most sites.
• Any e-commerce site is going to have a product display page.  This page lists product details, etc.  Often you find a customers who bought this also bought…
• The majority of sites work on a cart / checkout based system.
• Almost all sites have a home page of some kind.

Various sites include various other pages such as about us, terms and conditions, etc.  The core of the site is in the components listed above.  Most functioning sites also have some kind of login / details update page.

DBMS Selection

There are two major conditions to consider when choosing which DBMS to work with.  Firstly it is important to know what is available in terms of real world hosting.  Secondly it is important to consider the speed of the data access layers between the languages and the database.

A cursory search of real world host reveals that for JSP and PHP most hosts only provide the MySQL DBMS.  ASP.NET hosts tend to have some variant of SQL Server and some also have MySQL.

In terms of access technologies there are JDBC drivers for both MySQL and MSSQL.  PHP has functions to talk with both.  ASP.NET however is better adapted to work with SQL Server.

Since JSP and PHP both tend naturally towards MySQL I feel that it is a better comparison to work with this DBMS.  Time permitting it may be possible to also test against SQL Server later in the process.  For now I will assume that I am targeting the MySQL platform.