None
Specifies the path of the
folder where you store the
migration files. Migration files
are PHP scripts where those
queries located that define
the necessary database
changes. Ensure that you
have set the migrations
folder to be writable.
Be sure to load the migration library in your controller with the following line: $this->load->library('migration');
In this recipe, we're going to create a simple users table and use a migration library to add and then remove a column from it. Enter the following SQL into your database: CREATE TABLE IF NOT EXISTS ùsers` (
ùser_idìnt(11) NOT NULL AUTO_INCREMENT,
ùser_first_namè varchar(125) NOT NULL,
ùser_last_namè varchar(125) NOT NULL,
ùser_email` varchar(255) NOT NULL,
PRIMARY KEY (ùser_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
158
Chapter 6
How to do it...
First off, all your database migration files should be placed in the migrations folder at /
path/to/codeigniter/application/migrations/.
If the folder does not already exist, you'll need to create it in the /path/to/codeigniter/
application/ folder—be sure to give write permissions to it.
1. We're going to create the following two files:
/path/to/codeigniter/application/migrations/001_add_
icon.php
/path/to/codeigniter/application/controllers/migrate.php
Notice the filename 001_add_icon.php. The first part (001) is
the migration number; this will increment every time you add a new migration file. The second part (add_icon) is a descriptive indication of the purpose of the migration file. Add the following code into the file 001_add_icon.php. This migration file defines the queries to
be run to effect the migration change or to roll back from that change.
<?php defined('BASEPATH') OR exit('No direct script access
allowed');
class Migration_Add_icon extends CI_Migration {
public function up() {
$this->db->query("ALTER TABLE ùsersÀDD COLUMN ùser_icon`
TEXT NULL AFTER ùser_email`;");
}
public function down() {
$this->db->query("ALTER TABLE ùsers` DROP COLUMN
ùser_icon`;");
}
}
2. Add the following code into /path/to/codeigniter/application/
controllers/migrate.php; the migrate controller gives us access to CodeIgniter's migration functions.
<?php if (!defined('BASEPATH')) exit('No direct script access
allowed');
class Migrate extends CI_Controller {
function __construct() {
parent::__construct();
if ( ! $this->input->is_cli_request() ) {
echo 'Only access via command line.';
159
Working with Databases
exit;
}
$this->load->library('migration');
}
public function index() {
echo 'Config: ' . $config['migration_version'];
}
public function current() {
if ( ! $this->migration->current()) {
show_error($this->migration->error_string());
}
}
public function latest() {
if ( ! $this->migration->latest()) {
show_error($this->migration->error_string());
}
}
public function version() {
if ( $this->uri->segment(3) == '') {
echo 'You must specify a migration version number';
} else {
if ( ! $this->migration->version($this->uri->segment(3)) ) {
show_error($this->migration->error_string());
}
}
}
}
Okay, so what have we done so far? We've configured migrations to run in CodeIgniter, we've created our first migration file (taking care to name it properly), and we have two files: the controller migrate.php and the migration file 001_add_icon.php.
In the migration file 001_add_icon.php, there are 222 functions; out of these, up() and down() are functions where you would define SQL to go with your code changes. The function down() is where you would define SQL for removing your changes should someone (perhaps another developer) wish to revert a code change you might have made; therefore, it supports SQL.
In the controller migrate.php, we've created several functions for us to work with migrations, such as current() and latest(). The following two recipes will show you some basic usage of these migrations.
160
Chapter 6
Moving to the current version with current()
To simply alter your database so that it corresponds with the version number in $config['migration_version'], you should use the current() function.
Getting ready
Ensure that you have followed the preceding recipe, Using CodeIgniter database migrations.
How to do it...
1. Using your command line (terminal application), navigate to the root of your CodeIgniter installation (where the index.php file is) and type the following: php index.php migrate current
How it works...
Consider the following command line:
php index.php migrate current
The first thing we should bear in mind is the constructor in the migrate controller.
The constructor is looking at how the migrate controller is accessed; it'll deny access to the migrate controller if it is accessed via anything other than the command line—a useful security measure.
By typing the command we just saw, you'll run public function current().
The function accepts no parameter. CodeIgniter will look into the migrations folder for the file whose number corresponds with the value set in $config['migration_
version'] in the configuration file /path/to/codeigniter/application/config/
migration.php.
161
Working with Databases
Rolling back/stepping forward with version()
You may wish to deliberately alter the database by pointing it to a specific migration number.
This can be achieved by use of the version() function within CodeIgniter.
Getting ready
Ensure that you have followed the preceding recipe, Using CodeIgniter database migrations.
How to do it...
1. Using your command line (terminal application), navigate to the root of your CodeIgniter installation (where the index.php file is) and type the following: php index.php migrate version 1
How it works...
Consider the following command line:
php index.php migrate version number
number is highlighted as it specifies the migration file number to move to, that is, 1, 2, 3, and so on.
The first thing we should bear in mind is the constructor in the migrate controller.
The constructor is looking at how the migrate controller is accessed; it'll deny access to the migrate controller if it is accessed via anything other than the command line—a useful security measure.
By typing the preceding command, you'll run public function version(), passing to it the third parameter (which has the value of 1). CodeIgniter will look into the migrations folder for the file whose number corresponds with the third parameter (1), which by amazing coincidence is the number of the migration files we created—who would have known this?
CodeIgniter will load the migration file 001_add_icon.php and immediately run public function up(), which will add the column user_icon to the database table 'users'.
We can undo the creation of the user_icon column by entering the following in the command line:
php index.php migrate version 0
CodeIgniter will then run the public function down() in the migration file, which will remove the user_icon column.
162
Chapter 6
Generating an XML from a database result
Generating an XML from a database may be useful in several ways, perhaps you wish to send data from a query across a network using a SOAP request, or perhaps you're using it to build some data for a web service. Whatever your purpose, this is how to do it—also we'll look at some real-world uses—for example, we'll generate the XML output from a database query.
Getting ready
Firstly, we need to create a table and enter some example data so that you'll see some data in the CSV format, so with that in mind, copy the following code into SQL: CREATE TABLE IF NOT EXISTS ùsers` (
ùser_idìnt(11) NOT NULL AUTO_INCREMENT,
ùser_first_namè varchar(125) NOT NULL,
ùser_last_namè varchar(125) NOT NULL,
ùser_email` varchar(255) NOT NULL,
ùser_created_dateìnt(11) NOT NULL COMMENT 'unix timestamp',
ùser_is_activè varchar(3) NOT NULL COMMENT 'yes or no',
PRIMARY KEY (ùser_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO ùsers` (ùser_first_namè, ùser_last_namè, ùser_
email`, ùser_created_datè, ùser_is_activè) VALUES
('Chloe', 'Graves', 'cgraves@domain.com', 1366114115, 'yes'),
('Mark', 'Brookes', 'mbrookes@domain.com', 1366114115, 'yes');
How to do it...
We're going to create the following file:
f
/path/to/codeigniter/application/controllers/export.php
2. Create the controller export.php. This controller will load the CodeIgniter dbutil (database utility) class, which will provide support for various database-specific operations and generate the XML. Add the following code into your export.php controller:
<?php if (!defined('BASEPATH')) exit('No direct script access
allowed');
class Export extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->helper('url');
$this->load->dbutil();
163
Working with Databases
}
public function index() {
redirect('export/xml');
}
public function xml() {
$config = array ('root' => 'root',
'element' => 'element',
'newline' => "\n",
'tab' => "\t"
);
$query = $this->db->query("SELECT * FROM users");
echo $this->dbutil->xml_from_result($query, $config);
}
}
How it works...
Okay, take a look at the line in bold—we're loading the database utilities class in the controllers constructor. This utilities class contains some excellent functions for working with databases. We're using it to provide access to the function xml_from_result().
The export.php controller function index() redirects us to public function xml(), which runs a database query. You could, of course, have any source of data here, but we're calling a database and storing the result in the array $query. This is passed to the CodeIgniter function xml_from_result(). The xml_from_result() function takes the following two parameters:
f
$query: This is the data for XML; in this case, the output of our database query.
f
$config: This is the configuration parameter; in this case, the XML
formatting options.
We then echo the result of xml_from_result() to the screen—the result of which can be seen by viewing the page source code in your browser. You don't have to echo it out; you can store it in a variable if you require the XML for other purposes.
Be sure to separate a database query into its own model—the query is shown in the preceding controller for explanatory purposes.
164
Chapter 6
Generating a CSV from a database result
Perhaps one of the most common things you'll be asked to do, especially if you are building a complex application that may have users, products, orders, and various other metrics is to provide some sort of reporting of that data. Perhaps you'll be asked to generate a CSV file, and the following sections show how you do it.
Getting ready
Firstly, we need to create a table and enter some example data so that you'll see some data in the CSV format, so with that in mind, copy the following code into SQL: CREATE TABLE IF NOT EXISTS ùsers` (
ùser_idìnt(11) NOT NULL AUTO_INCREMENT,
ùser_first_namè varchar(125) NOT NULL,
ùser_last_namè varchar(125) NOT NULL,
ùser_email` varchar(255) NOT NULL,
ùser_created_dateìnt(11) NOT NULL COMMENT 'unix timestamp',
ùser_is_activè varchar(3) NOT NULL COMMENT 'yes or no',
PRIMARY KEY (ùser_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
INSERT INTO ùsers` (ùser_first_namè, ùser_last_namè, ùser_
email`, ùser_created_datè, ùser_is_activè) VALUES
('Chloe', 'Graves', 'cgraves@domain.com', 1366114115, 'yes'),
('Mark', 'Brookes', 'mbrookes@domain.com', 1366114115, 'yes');
Now that the database is ready we'll need to ensure that you're calling the database utility class; make sure that you call it with the following line:
$this->load->dbutil();
You can either put this line in the constructor of your controller or call it in your controller function.
Also, as we're going to be creating a file, we need the support of the 'file' helper, so make sure you're calling the helper with the following line:
$this->load->helper('file');
How to do it...
We're going to create the following file:
f
/path/to/codeigniter/application/controllers/export.php
165
Working with Databases
Forcing download
Add the following code into your export.php controller:
<?php if (!defined('BASEPATH')) exit('No direct script access
allowed');
class Export extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->helper('download');
$this->load->dbutil();
$this->load->helper('url');
}
public function index() {
redirect('export/csv');
}
public function csv() {
$query = $this->db->query("SELECT * FROM users");
$delimiter = ",";
$newline = "\r\n";
force_download('myfile.csv', $this->dbutil->csv_from_
result($query, $delimiter, $newline));
}
How it works...
Okay, take a look at the lines in bold—we're loading the CodeIgniter helper 'download' and the database utilities class in the controller constructor. This'll help us with this recipe.
The export.php controller function index() redirects us to public function csv(), which runs a database query. You could, of course, have any source of data here, but we're calling a database and storing the result in the array $query. This is passed to the CodeIgniter function force_download(), which accepts the following two parameters: f
The name and extension of the file to be created (or in this case, downloaded) f
The data that will go into the file; in this case, we're using the CodeIgniter function csv_from_result() that will take a row of data from a database query and convert it into a delimiter-separated string of text. csv_from_result() takes the following three parameters:
$query: This is the data for the CSV; in this case, the output of our database query
166
Chapter 6
$delimiter: This is the data delimiter, that is, it specifies how we are separating each cell worth of data; this is usually a comma (,).
$newline: This is the new line character; it is usually '\n\n'
If all goes according to plan, force_download() will, as the name says, force a download of the CSV file.
Saving to file
Add the following code into your export.php controller:
<?php if (!defined('BASEPATH')) exit('No direct script access
allowed');
class Export extends CI_Controller {
function __construct() {
parent::__construct();
$this->load->helper('url');
$this->load->helper('file');
$this->load->dbutil();
}
public function index() {
redirect('export/csv');
}
public function csv() {
$query = $this->db->query("SELECT * FROM users");
$delimiter = ",";
$newline = "\r\n";
$data = $this->dbutil->csv_from_result($query, $delimiter,
$newline);
$path = '/path/to/write/to/myfile.csv';
if ( ! write_file($path, $data)) {
echo 'Cannot write file - permissions maybe?';
} else {
echo 'File write OK';
}
}
167
Working with Databases
How it works...
Okay, take a look at the lines in bold; we're loading the CodeIgniter helpers 'download', and 'file' and the database utilities class in the controller constructor. This'll help us with this recipe. We're also adding CodeIgniter-specific syntax to write a file to a disk.
The export.php controller function index() redirects us to public function csv(), which runs a database query. You could, of course, have any source of data here, but we're calling a database query and storing the result in the $query array. We then call the CodeIgniter csv_from_result() function where csv_from_result() takes the following three parameters:
f
$query: The data for the CSV; in this case, the output of our database query f
$delimiter: The data delimiter, that is, it specifies how we are separating each cell worth of data
f
$newline: The new line character; it is usually set to '\n\n'
The csv_from_result() function will store its output in the variable $data. We then try to run the CodeIgniter function write_file(), which accepts the following two parameters: f
The path to write the file, including the filename and extension; remember that this path should be writeable
f
The data to write to the file
Should all go as per the plan, the recipe will return the message File write OK—of course, you should replace it with your own code as you see fit. Should it fail, it'll return an error message and again replace it with your own code where necessary.
There's more...
The chances are that if the file isn't being written, you don't have the necessary permissions to write to the desired destination folder. You will need to amend the permissions for the destination folder so that they are at a level high enough to allow CodeIgniter to write to it.
For example, in Linux/Mac, you would use the chmod command in the terminal.
168
7
Creating a Secure
User Environment
In this chapter, we will cover:
f
Escaping user input
f
Preventing cross-site request forgery
f
Escaping data – for a database
f
Using HTTPS with CodeIgniter
Introduction
Firstly, a disclaimer: no method or system can ever be entirely foolproof and secure all the time, and you should be aware of the correct security measures that you should apply for the programming task or context in which you are coding. I will put some links to other information resources at the end of this chapter. Having said that, CodeIgniter offers some useful techniques for reducing the chance that something can go wrong, for example, in this chapter are several recipes that can help reduce the chances of something untoward--however, you should always remain vigilant and ensure that you're building securely.
Escaping user input
The CodeIgniter security class function, xss_clean(), attempts to clean input from the POST
or COOKIE data to mitigate against techniques that can allow for the injection of code into a website. For example, it would seek to prevent JavaScript code from being executed if it is included in a blog post submitted by a user, or look at the data submitted in a text input field and escape disallowed characters.
Creating a Secure User Environment
Getting ready
You can apply this to any controller you're creating, or if you've extended using MY_Controller, you can add it to that if you wish. You can also autoload the security helper by adding it to $autoload['helper'] = array() in the /path/to/codeigniter/application/
config/autoload.php file. To be explicitly clear, here we're loading the security helper in the constructor of the controller (that is, any controller you have):
function __construct() {
parent::__construct();
$this->load->helper('security');
}
How to do it...
There are two ways to do this, globally (CodeIgniter does it every time it encounters the POST
or COOKIE data), and individually (CodeIgniter lets you define when to call the clean COOKIE
or POST data).
Globally
1. CodeIgniter can call xss_clean() automatically each time it encounters the POST or COOKIE data without you needing to explicitly call xss_clean(). To do this, you'll need to amend the following file:
/path/to/codeigniter/application/config/config.php
2. Change the value of $config['global_xss_filtering'] to TRUE, as follows: $config['global_xss_filtering'] = TRUE;
However, be aware that there is a computational overhead in doing so and it may not always be necessary for you to run this all the time.
Individually
Ensure that $config['global_xss_filtering'] is set to FALSE, as follows: $config['global_xss_filtering'] = FALSE
This will turn off global XSS filtering. When you wish to use xss_cean(), enter the following code into your controller or model:
$cleaned_data = $this->security->xss_clean($data_to_be_cleaned); 170
Chapter 7
How it works...
In either example, you're calling the same CodeIgniter method; one is being called automatically and the other is calling it on a case-by-case basis. The code in question can be found at /path/
to/codeigniter/system/core/Security.php (find the function, xss_clean()).
Preventing cross-site request forgery
A cross-site request forgery is where an attacker pretends to be a user that the website recognizes (such as a logged-in user), and the attacker is then able to access a logged-in user's profile as though they were the genuine user. There is a wealth of technical information available, such as websites, books, and so on, on how that happens, which is why we're not going to look into that here. Instead, we're going to look at how CodeIgniter mitigates against cross-site request forgeries.
How to do it...
We're going to amend one file and create two files by performing the following steps: 1. First, we need to amend some configuration items. To do that, we'll need to open the following file: /path/to/codeigniter/application/config/config.php Find the following configuration options and make the amendments as listed in the table:
Configuration Item
Default Value
Change to/Description
$config['csrf_
TRUE
Specifies whether to turn request
protection']
forgery protection on or off
$config['csrf_token_
csrf_test_name
Specifies the name of the hidden
name']
form element used in a form (see
the How it works... section)
$config['csrf_
csrf_cookie_name
Specifies the name of the cookie
cookie_name']
that is set on the user's machine
$config['csrf_
7200
The number of seconds that a
expire']
single token is allowed to exist
for; after this time, if a form is
submitted, CodeIgniter will throw
an error
171
Creating a Secure User Environment
2. Next, we create the following two files:
/path/to/codeigniter/application/controllers/csrf.php
/path/to/codeigniter/application/views/csrf/csrf.php
3. Add the following code into the, csrf.php controller. This controller will load the required helpers and display the simple form in the views/csrf/csrf.php file:
<?php if (! defined('BASEPATH'))