I got an error on a booking system for a conference I was trying to book into this week. I happen to live in a town that has an apostrophe in the name and it crashed the booking system and worse gave the query in the error on screen.

Oops
The error message made it clear that one function is being used to execute SQL statements, so all database queries are vulnerable. Oops
What was clear is that the single quotes were being used around form variables passed to the SQL statement. Oops.
And form input wasn’t being filtered or escaped.
So one could change a form field to 'DROP TABLE table; and trash that table. The table name was the obvious choice, so at a guess there would also be a bookings table, users table, payments table and so on.

A hacker could do all sorts of really bad mischief. I let the site know and they said they would remove the apostrophe from my town name in the drop down. Oops.

That doesn’t fix the vulnerability! So I sent them a more detailed explanation. The site is still live. Oops.

I learnt years ago to escape all data in queries, including data that came from the database in the first place – for WordPress plugins esc_sql() is your friend! PHP has mysqli_real_escape_string() – they add slashes where needed. Don’t use addslashes, because it would add a slash to
\’ making it \\’, so dangerous again!

After the email exchange where I recommended Chris Schiflett’s “Essential Php Security” on Amazon.co.uk* and Amazon.com*. I grabbed my copy off the shelf to re-read about SQL injections.

I’m guessing logins are also vulnerable.
For the username field one could enter user' or 'foo' = 'foo' --
The — is the start of a comment field, so a typical login SQL query would become

$sql="SELECT * FROM users WHERE username = 'user' or 'foo' = 'foo' -- AND password='hcehvchevchevchew';

The query is terminated at the -- so one may have logged in without a username or password. If a username is known, then that account can be logged into without a password. Oops.

The fix is pretty simple…

<?php 
$form=array(); 
$mysql=array(); 
//just in case magic quotes are still on 
foreach($_POST AS $key=>$value)$form[$key]=stripslashes($value);
//assuming $db is your database link identifer
foreach($form AS $key=>$value)$mysql[$key]=mysqli_real_escape_string($db,$value);

$query='SELECT * FROM table WHERE column="'.$mysql['column'].'"';
$result=$db->query($query);
?>

I’ve not checked the input is as expected, which is called filtering, but at least SQL injection attacks are stopped dead.