Monthly Archives: February 2012

Deploying tips for Web Applications

I’ve seen the same error in too many projects. When we start a project normally we start with a paper, a white board or something similar. After the first drafts we start coding. In the early stage of the project we want to build a working prototype. It’s natural. It’s important to have a working prototype as fast as we can. The things are different in a browser. All works within a white board, but with a working alpha release we will feel “real” sensations.

Now the project is growing up. Maybe we need several weeks to going live yet. Maybe we haven’t even decide the hosting, but there is something we need to take into account even in the early stages of the project. We need to build an automate deploy system. The way we’re going to use to put our code in the production server. It’s mandatory to have an automated way to deploy our application. Deploy code in production must be something really trivial. Must be done with a few clicks. Hard to deploy means we are not agile, and that is not cool.

If the project is a “professional” one (someone pay/will pay for it), problems in the deploy means down times. Down times are not good. Our clients don’t pay us for those kind of problems. If the project is a personal project, a hard deploy system means that we’re going to be very lazy to improve our project. Deploy by hand is good idea only if we never forget anything and if we’re perfect. If not, it’s always better to have a build script.

It’s important to define different environments within our application. Modern frameworks such as symfony2 has a great way to define environments. It’s important to take into account that. Our code must be exactly the same in our development environment and at the production one. Exactly the same means exactly the same. If we need to change the code before deploy it into production server we’ve got a problem. A simple trick to define environments is create two ini files one with development data (database dsn, urls, paths) and another one to production. We can also use enviromnent variables, but keeping the source code identical.

So we need at least a build script to the source code, but we must remember that we also need to deploy database changes. Deploy database changes is a hard work, but source code can be trivial if we take into account a few details:

  • Source code must be the same in all environments. Differences must be placed in configuration files.
  • Never perform file-system operations directly with the console. We need to create scripts and execute the script to perform file-system operations. (folder creation, write-enables to log and caches, …)

If we follow those simple rules we can create a very simple build scrip with our scm (git, mercurial).

The idea is very simple. One mercurial repository on development server. Another one on production server.

// .hg/hgrc
[paths]
prod = ssh://user@host//path/to/app

[hooks]
changegroup = hg update

Now we can easily clone the development repository. A simple “hg push prod” will push code to the production server and update the working repository. If you don’t have ssh access to the server maybe you need to build a custom script. Please do it. “Waste” your time creating your build script. It must works like a charm. Your life will be better. Another tools that will help us to build deploy scripts:

http://capifony.org/
https://github.com/capistrano/capistrano
http://www.phing.info/trac/

And that’s all. Regards, Gonzalo

How to protect from SQL Injection with PHP

Security is a part of our work as developers. We need to ensure our applications against malicious attacks. SQL Injection is one of the most common possible attacks. Basically SQL Injection is one kind of attack that happens when someone injects SQL statements in our application. You can find a lot of info about SQL Injection attack. Basically you need to follow the security golden rule

Filter input
Escape output

If you work with PHP problably you work with PDO Database abstraction layer.
Let’s prepare our database for the examples (I work with PostgreSQL):

CREATE TABLE users
(
  uid integer NOT NULL,
  name character varying(50),
  surname character varying(50),
  CONSTRAINT pk_users PRIMARY KEY (uid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE users OWNER TO gonzalo;

INSERT INTO users(uid, name, surname) VALUES (0, 'Gonzalo', 'Ayuso');
INSERT INTO users(uid, name, surname) VALUES (1, 'Hans', 'Solo');
INSERT INTO users(uid, name, surname) VALUES (2, 'Luke', 'Skywalker');

OK our database is ready. Now let create a simple query

$dbh = new PDO('pgsql:dbname=db;host=localhost', 'gonzalo', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $dbh->prepare('select uid, name, surname from users where uid=:ID');
$stmt->execute(array('ID' => 0));
$data = $stmt->fetchAll();

It works. We are using bind parameters, so we need to prepare one statement, execute and fetch the recordset. The use of prepared statements is strongly recommended. We can also use query() function:

$dbh = new PDO('pgsql:dbname=db;host=localhost', 'gonzalo', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$uid = 0;
$stmt = $dbh->query("select uid, name, surname from users where uid={$uid}");
$data = $stmt->fetchAll();

But what happens if $id came from the request and it’s not propertly escaped

$dbh = new PDO('pgsql:dbname=db;host=localhost', 'gonzalo', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$uid = "0; drop table users;";
$stmt = $dbh->query("select uid, name, surname from users where uid={$uid}");
$data = $stmt->fetchAll();

basically nothing: SQLSTATE[42601]: Syntax error. That’s because is not allowed to use two prepared statements in a single statement.

If we use an insert:

$dbh = new PDO('pgsql:dbname=db;host=localhost', 'gonzalo', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$uid     = 20;
$name    = 'Gonzalo';
$surname = "Ayuso'); drop table users; select 1 where ('1' = '1";

$count = $dbh->exec("INSERT INTO users(uid, name, surname) VALUES ({$uid}, '{$name}', '{$surname}')");

Now we have a problem. Our user table will be deleted. Why? That’s because of the user we are using to connect to the database. It’s important especially at production servers.
It’s very important not to use a database superuser in production. Superusers are very comfortable in our development servers, because you don’t need to grant privileges to every tables but if you forget this issue in production you could have Sql-Injection problems. The solution is very simple:

GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE users TO gonzalo2;

And now

$dbh = new PDO('pgsql:dbname=db;host=localhost', 'gonzalo2', 'password');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$uid     = 20;
$name    = 'Gonzalo';
$surname = "Ayuso'); drop table users; select 1 where ('1' = '1";

$count = $dbh->exec("INSERT INTO users(uid, name, surname) VALUES ({$uid}, '{$name}', '{$surname}')");

Now we are safe, at least with this possible attack.
Sumarizing:

  • Filter input
  • Escape output
  • Take care about the database users. Don’t use one user that it allowed to perform “not-allowed” operations within our application. It sounds like a pun but is important.
Follow

Get every new post delivered to your Inbox.

Join 949 other followers