Protecting against SQL injection attacks

SQL Injection

SQL injection is a serious concern for web developers, as an attacker can use this simple hacking technique to gain access to sensitive data and/or potentially cripple your database. If you haven’t secured your applications, get familiar with the following method and grind it into your coding routine!

I’ve read a lot of guides, and they seem to tend to overcomplicate this, so I’ll be as straight forward as possible. In PHP the easiest way is to pass your data through the mysql_real_escape_string function. By escaping special characters on fields where the user can manipulate the database, you will avoid being vulnerable. Take a look below at the example of what to do and what not to do.

// This is a vulnerable query.
$query = “SELECT * FROM products WHERE name=’$productname’”;
mysql_query($query);
// This query is more secure.
$query = sprintf(”SELECT * FROM products WHERE name=’%s’”,
mysql_real_escape_string($productname));
mysql_query($query);

Another safe way of performing MySQL queries in PHP is to use the included mysqli library (you can read up on all the functions at http://php.net/mysqli):

$name = $_GET[’product’];
$db = new mysqli(”localhost”, “user”, “pass”, “database”);
if ($stmt = $db->prepare(’SELECT price FROM products WHERE name = ?’)) {
$stmt->bind_param(’s’, $name);
$stmt->execute();
$stmt->bind_result($price);
$stmt->fetch();
echo ‘The product costs $’, $price, ‘.’;
$stmt->close();
}
$db->close();

The most important part of protecting yourself is stopping users from being able to pass unaltered database manipulative special characters, like single quotes.

MSDN - SQL Injection Article
Wikipedia - SQL Injection
SecuriTeam - SQL Injection Walkthrough
SitePoint - SQL Injection Attacks, Are You safe?

Share and Enjoy: