Development

This is where the software is developed – it’s the working environment for individual developers or small teams. The purpose of this environment is for the developer to work on local host, separate from the rest of the team, allowing them to make various changes without worrying that it may alter the work of the other members of the team.

Staging

It is used to assemble, test and review the application before it goes into production. Usually the staging environment tries to simulate as much as possible the production environment (hardware and software-wise). Normally, before releasing an update version of the application on the production environment, the update must be tested on the staging environment. This environment can also be used as a demonstration/training environment.

Production

It is the “live” environment, where the final application goes out to the world and becomes active.

To switch from one environment to another use the Subversion source code.

Using SVN on Aptana is an article that explains how to set your development environment on your local computer and then to change it on your staging environment.

To better understand the development of an application using environments, check this helpful article http://dltj.org/article/software-development-practice/

• 31 – captcha errors try catch
• 32 – pagination issue
• 33 – Admin wrong link
• 34 – Acunetix results July 24th ( notices and one fatal error)
• 35 – update copyright line in files

For more details see ChangeLog 1.2.2. To get only the changed files from 1.2.1 to 1.2.2, download the upgrade file

Note*: because of the Bug 35, all php files have changed. To see what else has changed, check the DotKernel Tracker or the DotKernel WebSVN .

P.S. On July 22, 2010 we released DotKernel 1.2.1. You can check the ChangeLog 1.2.1 or download the upgrade 1.2.1 zip file.

NOTE:This release marks the end of the active support for PHP 5.2. Following this release the PHP 5.2 series will receive no further active bug maintenance. Security fixes for PHP 5.2 might be published on a case by cases basis. All users of PHP 5.2 are encouraged to upgrade to PHP 5.3

PHP 5.3.3 is just released ,  so is time to upgrade every project  to PHP 5.3.x branch, and also upgrade all servers to 5.3.x

Since the previous released 1.1.2, some changes have been made.

• On database, we changed the names and structure of tables to respect database naming convention. http://www.dotkernel.com/dotkernel/dotkernel-database-naming-conventions-for-mysql/

• A new word came into our DotKernel discussions: dots. We use this term when talking about a submodule and all its component files. For example, “user” is a submodule of frontend module. Note that one dots can be part of multiple modules. (For example, “user” dots belong to frontend and admin module). For each dots, the configurations values have been added to xml files which are stored in configs/dots folder. In the preview versions, this values where hard-coded in the php files.

• Another change made in configs folder is resource.xml, which contains the configuration values for the controllers of each module.

To be easier to start an application from DotKernel, in admin module, there are now the following dots:  admin, user and system.

New library classes have been implemented: Dot_Geoip and Dot_Seo, and some of the existing ones have been updated: Dot_Curl and Dot_Session (each module has his own session).

In DotKernel, all SQL queries are written as prepared statements.  We strongly encourage this  practice: http://www.dotkernel.com/php-development/protection-against-sql-injection-using-pdo-and-zend-framework/

For more details, see  ChangeLog 1.2.0

Why?

Because when inserting a new row in the table for the date and time field there is no need to specifically add its value, either by creating it from PHP code with the Date/ Time functions or with MySQL function NOW()

ALTER TABLE `user` CHANGE `dateCreated` `dateCreated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP is also a solution for  updating date and time fields. Use ON UPDATE CURRENT_TIMESTAMP clause, if you want the value of the field to be changed automatically each time the row is updated.

ALTER TABLE `user` CHANGE `dateLogin` `dateLogin` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

DEFAULT and ON UPDATE clauses can be used together or separately, depending on your needs:

• With both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses, the column has the current timestamp for its default value and is automatically updated.
• With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. (Only for the first TIMESTAMP field from the table)
• With a DEFAULT CURRENT_TIMESTAMP clause and no ON UPDATE clause, the column has the current timestamp for its default value but is not automatically updated.
• With no DEFAULT clause and with an ON UPDATE CURRENT_TIMESTAMP clause, the column has a default of 0 and is automatically updated.
• With a constant DEFAULT value, the column has the given default and is not automatically initialized to the current timestamp. If the column also has an ON UPDATE CURRENT_TIMESTAMP clause, it is automatically updated; otherwise, it has a constant default and is not automatically updated.

For more details check out MySQL Manual

Note*: Only one timestamp field can be DEFAULT CURRENT_TIMESTAMP in a table.

• query (mixed $sql, [mixed $bind = array()])
  • use prepare statements internally
  • but SQL Injection is still possible if $sql is dynamically created
• fetchAll (string|Zend_Db_Select $sql, [mixed $bind = array()], [mixed $fetchMode = null])
  • all the fetch methods are using prepared statements internally
  • but SQL Injection is still possible if $sql is dynamically created
• insert (mixed $table,  $bind)
  • use prepare statements internally
  • so, SQL Injection is not possible
• update (mixed $table,  $bind, [mixed $where = ''])
  • use prepare statements internally
  • but SQL Injection may be possible if $where is created dynamically
• delete (mixed $table, [mixed $where = ''])
  • SQL Injection may be possible if $where is created dynamically

Note*: even if you use prepared statements using Zend_Db methods, SQL Injection is still possible if WHERE and ORDER BY clause are wrongly written, so pay attention to them.

For more details see Stefan Esser slides.

PS. A short tip, you can use cast type to avoid SQL Injection in WHERE clause where is possible.

$sql= 'SELECT * FROM table WHERE id = ' . (int)$_POST['id'];

PDO – PHP Data Objects – is a database access layer providing a standardized method of access to multiple databases.  PDO provides a data-access abstraction layer, meaning that depending on what database you’re using, you will have apply the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn’t rewrite SQL or emulate missing features. Among  PDO benefits there are:
-    Access methods allow complete control over how attributes are read and written
-    Validation on a per-record and per-attribute level
-    Easier fetching of objects from related table
-    Reusable logic – means that the same codebase is much easier to maintain
-    Cleaner code by using object oriented code
-    Less errors from SQL query generation
-    Last but not least : Protection against SQL injection

In Zend Framework for database access, methods usually support prepared statements. Dynamic SQL queries are allowed, but you must escape all the parameter, otherwise you have SQL injection.  Because of this, prepared statements are encouraged to be used. They can handle escaping parameters for you.
Most people believe that using prepared statements they are 100% protected from SQL injection. But this is by far true. Input data should always be validated and sanitized, and PDO should be seen as another line of defense. PDO is not protecting you from other security vulnerabilities like XSS(cross-site scripting), but helps protect your application against SQL injection.

It may also occur a problem in Zend Framework when you have SQL injection in your application while you are using PDO_MySQL. PDO_MySQL is a more dangerous application than any other traditional MySQL applications. Traditional MySQL allows only a single SQL query. In PDO_MySQL there is no such limitation, but you risk to be injected with multiple queries. To avoid this you should try to use the correct prepared statements from Zend Framework. You should also pay attention when you have in your SQL query WHERE IN and ORDER BY; they cannot be handled by prepare statements normally. In this case you should escape your data.

Zend_Db has two escaping methods which can be used: quote() and quoteIdentifier(). Note that these two methods are handling strings by putting them between single quotes.

For more details see:

http://ezinearticles.com/?SQL-Injection-Protection-in-PHP-With-PDO&id=1815110

http://www.zend.com/webinar/Framework/70170000000bEs9-webinar-secure-application-development-with-the-ZF-20100505.flv

• INSERT – Inserting new rows into database tables.
• UPDATE – Updating existing rows in database tables .
• DELETE – Deleting existing rows from database tables.

Note*:

$db = Zend_Db::factory('Pdo_Mysql', $dbConnect);

INSERT

INSERT INTO user(email, password, firstName, lastName, active)
	   VALUES ('$email', '$password', '$firstName', '$lastName', 1);

The above SQL INSERT statement is translated in Zend_Db as follow:

$data = array( 'email' => $email,
		    'password' => $password,
		    'firstName' => $firstName,
		    'lastName' => $lastName,
		    'active' => '1');
$db->insert('user', $data);

UPDATE

UPDATE user
   SET password = '$password',
       firstName = '$firstName',
       lastName = '$lastName',
       accountUpdate = (accountUpdate +1)
 WHERE id = '$id'

The above SQL UPDATE statemnet is translated in Zend_Db as follow:

$data = array('password' => $password,
              'firstName' => $firstName,
              'lastName' => $vlastname,
              'accountUpdate' => new Zend_Db_Expr('accountUpdate+1'));
$db->update('user', $data, 'id = '.$id);

DELETE

DELETE FROM user WHERE id = '$id'

The above SQL DELETE statemnet is translated in Zend_Db as follow:

$db->delete('user', 'id = '.$id);

SELECT a.id,
	   a.title, 
       (SELECT COUNT(c.track_id)
        FROM track_files AS c 
        WHERE c.track_id = a.id
       ) AS `count_files`,
       COUNT(b.track_id) AS count_courses
FROM tracks AS a 
LEFT JOIN track_courses AS b ON (a.id = b.track_id)
GROUP BY a.id

Initialize the connection to our MySql database:

$db = Zend_Db::factory('Pdo_Mysql', $dbConnect);

$db->select()
   ->from(array('a'=>'tracks'),
	     array('id',
	 	     'title',
		     'count_files' => new Zend_Db_Expr(
			 		   '('.$db->select()
						   ->from(array('c'=>'track_files'),
						             array(new Zend_Db_Expr('COUNT(c.track_id)')))
						   ->where('c.track_id = a.id').')' )
			   )
		  )
   ->joinLeft(array('b'=>'track_courses'),
		 'a.id = b.track_id',
		 array('count_courses' => 'COUNT(b.track_id)')
		 )
   ->group('a.id');

array  fetchAll  (string|Zend_Db_Select $sql, [mixed $bind = array()], [mixed $fetchMode  = null])
array fetchAssoc (string|Zend_Db_Select $sql, [mixed $bind = array()])
array fetchCol (string|Zend_Db_Select $sql, [mixed $bind = array()])
string fetchOne (string|Zend_Db_Select $sql, [mixed $bind = array()])
array fetchPairs (string|Zend_Db_Select $sql, [mixed $bind = array()])
array fetchRow (string|Zend_Db_Select $sql, [mixed $bind = array()], [mixed $fetchMode = null])

To be more easily to follow, in green box is the classical SQL statement, and in blue box is the query written in Zend_Db style.


Lets start.
Initialize the connection to our MySql database:

$db = Zend_Db::factory('Pdo_Mysql', $dbConnect);

Here is a SQL query, that we want to fetch:

$sql = "SELECT id, title FROM files";
$db->query($sql)

$select = $db->select()
             ->from('files', array('id', 'title'))

Note*: for the old style of fetching we used an old class. What you need to know is:

- query() method is similar with mysqli_query() from Mysqli PHP extension

- next_record() method is similar with mysqli_next_result() from Mysqli PHP extension

- f() method retrieve the value of the column specified as parameter


fetchAll

while($db->next_record())
{
    $a[] = array(
				 'id' => $db->f('id'),
				 'title' => $db->f('title')
		     	);
}

$a = $db->fetchAll($select);

fetchAssoc

while($db->next_record())
{
	$a[$db->f('id')] = array(
							 'id' => $db->f('id'),
							 'title' => $db->f('title')
							);
}

$a = $db->fetchAssoc($select);

fetchCol

while($db->next_record())
{
    $a[] = $db->f('id');
}

$a = $db->fetchCol($select);

fetchOne

$db->next_record();
$a = $db->f('id');

$a = $db->fetchOne($select);

fetchPairs

while($db->next_record())
{
    $a[$db->f('id')] = $db->f('title');
}

$a = $db->fetchPairs($select);

fetchRow

$db->next_record();
$a = array(
		   'id' => $db->f('id'),
		   'title' => $db->f('title')
	      );

$a = $db->fetchRow($select);