SQL Injection

SQL injection is a simple technique that can facilitate identity theft, data loss and fraud. The technique is platform and database agnostic, meaning it doesn't matter if you're running MySQL on Linux or MS SQL Server on Windows Server. It requires no specialised tools, only some basic knowledge of databases and a web browser. Many high-profile websites have succumbed to SQL injection attacks, such as NASA's JPL website, computer security firm Kasperky's site, the website of the UK's Durham police and many more.

I've long despaired over reports of websites falling victim to SQL injection attacks. SQL injection is currently the 2nd most dangerous programming vulnerability. To be blunt, it is caused by sloppy programming, usually by programmers that simply don't know any better.

PHP developers in particular often leave their websites open to SQL injection attack, largely due to the vast number of terrible examples of database interaction on the web.

It's so easy to make your code immune to SQL injection attempts. EasyPDO makes it even easier.

Parameterised SQL

You might hear some people talking about "sanitising user input" as a way of defending against SQL injection. Whilst this can be an effective technique if done properly, it is often an onerous tasks and is not the best solution. The key to ensuring you never fall victim to an SQL injection attack is to use parameterised SQL statements. This simply means that wherever you need to include a value in an SQL statement, instead of hard-coding it you create a parameter instead. For example, instead of:

$sql = <<<eot
SELECT
  emailaddress
FROM
  users
WHERE
  username = '{$_REQUEST['username']}'
AND
  password = '{$_REQUEST['password']}'
eot;

You would instead write:

$sql = 'SELECT emailaddress FROM users WHERE username = ? AND password = ?';
$query = $db->prepare($sql);
$query->bindParam(1, $_REQUEST['username']);
$query->bindParam(2, $_REQUEST['password']);

Parameterised SQL provides a number of advantages:

Security
A parameterised query is not suceptible to SQL injection attacks.
Performance
Most database engines will compile SQL, generate a query plan and cache that plan in case it's needed again. Parameterised queries can easily be cached and re-used. Non parameterised queries generally cannot.
Best Practise
Separation of logic (the SQL) and data (the parameter)