New Website

I've made a new website, as lynx.io is dead. You can find it here: http://macr.ae/—it's similar in format to lynx.io, but has better articles, and they're all written by me.

JavaScript disabled

While it will still mostly work, a lot of this site's functionality relies on JavaScript - please enable it for the best experience.

Security: SQL Injections

SQL injection is a common, although becoming less so, type of vulnerability that is occasionally found in websites that use SQL (Server Query Language) to access data at the server-side. If the code used to query the database doesn't correctly escape the data passed to it, it may be vulnerable. This article will explain the vulnerability, and then explain how to secure your code against it.

The vulnerability

To understand this concept, you need to have a basic concept of the SQL language. I will not be explaining that here: I'm teaching you how to write secure code, not to exploit existing code, and so am assuming that if you don't know how to write SQL then you are not using it and so don't need to secure it.

It's easiest to explain the vulnerability through an example or two. We'll start off with some vulnerable code:

<?php

// VULNERABLE CODE

include('database_connect.php'); // Connect to the database
$sql = "SELECT *
    FROM app_users
    WHERE username='" . $_POST['user'] . "'
        AND user_password='" . $_POST['pass'] . "'";
$query = mysql_query($sql);

I'm using the old mysql_* library for these examples as PDO isn't usually vulnerable; it supports prepared statements (although if used incorrectly, it can still be vulnerable), which I will explain later in the article.

As you can see, the code is checking whether the user credentials sent from a form on the previous page are valid. If the credentials are valid, it will return the user details. If they're invalid, it won't return anything. However, the code is vulnerable: if the user enters a string like ' OR ''=' into the password field, the following SQL will be generated:

SELECT *
FROM app_users
WHERE username='Callum'
    AND user_password='' OR ''=''

That will return true whether the password is correct or not, allowing the attacker to log in as any user they want to without knowing the password.

This, obviously, is a problem. That was just a basic injection, other injections are possible - for example, we can attempt to guess table names, the username of the user that is currently running MySQL (using USER_NAME() - MySQL only), and with more complex injections, view entire dumps of tables (although not with the previous code example).

The fix

The easiest fix for this is to stop using the deprecated mysql_* library. There are far better alternatives available, such as PDO and MySQLi. Both of those examples support prepared statements, which also mean that you don't have to escape your data. For more information and a few examples, see my article on PDO: PDO Basics.

If you cannot stop using the mysql_* library, then there are a few functions available to secure your code from SQL injections. The recommended way is to use mysql_real_escape_string(), which simply returns an escaped version of the same string:

<?php

include('database_connect.php'); // Connect to the database

$username = mysql_real_escape_string($_POST['user']);
$password = mysql_real_escape_string($_POST['pass']);
$sql = "SELECT *
    FROM app_users
    WHERE username='$username'
        AND user_password='$password'";
$query = mysql_query($sql);

That code is secure.

If you're using integers, then simply calling mysql_real_escape_string() won't do the trick - as 0 OR 0=0 doesn't contain any quotes, nothing will be changed; the following code is still vulnerable:

<?php

include('database_connect.php'); // Connect to the database

$userid = mysql_real_escape_string($_POST['id']);
$sql = "SELECT *
    FROM app_users
    WHERE user_id=$userid";
$query = mysql_query($sql);

Passing 0 OR 0=0 to it will generate the following SQL:

SELECT *
FROM app_users
WHERE user_id=0 OR 0=0

In order to combat this, we must cast the variable to integer, like this:

<?php

include('database_connect.php'); // Connect to the database

$userid = (int) $_POST['id'];
$sql = "SELECT *
    FROM app_users
    WHERE user_id=$userid";
$query = mysql_query($sql);

 

In summary, SQL injections are a very real and potentially very dangerous type of exploit, that could lead to a lot of damage being caused, such as data being deleted or a table containing sensitive user information such as passwords, emails or credit card information being released. They are moderately easily prevented by using prepared statements in a better library such as PDO, or using a function like mysql_real_escape_string() to escape input.

About Callum Macrae:

Callum Macrae is the founder of lynx.io and a JavaScript developer from the United Kingdom. He is currently writing his first book, to be published by O'Reilly Media.

You can view more articles by this author here.

Tags: security

Comments

Miles Wilson says:

Cool.

A lot of people are using the mysql_ library still, and it is worth noting that mysql_real_escape_string() is not always enough.

Consider:

$userid = isset($_GET['id']) ? $_GET['id'] : 0;
$userid = mysql_real_escape_string($userid);
RunQuery(&quot;SELECT userid, username FROM sql_injection_test 
WHERE userid=$userid&quot;);

Because the code is expecting a numeric value, I can still inject

0 OR 1=1 

as my value, and have it execute. Nothing is escaped, and the query will return true.

The example I present is unlikely, but proof that prepared statements are 100% the way to go.

Cheers

M

Callum Macrae says:

True. I've haven't actually used SQL without prepared statements that much, so I don't think of these things.

I guess in that case, you would just want to cast it to int, like so?

$userid = isset($_GET['id']) ? (int) $_GET['id'] : 0;
Keshav nair says:

You can use php PDO as they provide more functionalities and create inbuilt prepared statements and i heard somewhere that mysql_query will not be supported in future versions of php

Callum Macrae says:

Yeah, they're very slowly deprecating the mysql_* libraries: http://news.php.net/php.internals/53799

Callum Macrae says:

I've added the information about preventing SQL injections with integers.

says:

Add comment

 

You can use markdown in comments (press "m" for a cheatsheet).

Enable JavaScript to post a comment

Markdown Cheat Sheet

# This is an <h1> tag
## This an <h2> tag
###### This is an <h6> tag

Inline markup: _this text is italic_, **this is bold**, and `code()`.

[Link text](link URL "Optional title")
[Google](http://google.com/ "Google!")

![Alt text](image URL)

![This is a fish](images/fish.jpg)

1. Ordered list item 1
2. Ordered list item 2

* Unordered list item 1
* Unordered list item 2
* Item 2a
* Item 2b

And some code:

// Code is indented by one tab
echo 'Hello world!';

Horizontal rules are done using four or more hyphens:

----

> This is a blockquote

This is an <h1> tag

This an <h2> tag

This is an <h6> tag

Inline markup: this text is italic, this is bold, and code().

Link text Google

This is a fish

  1. Ordered list item 1
  2. Ordered list item 2
  • Unordered list item 1
  • Unordered list item 2
    • Item 2a
    • Item 2b

And some code:

// Code is indented by one tab
echo 'Hello world!';

Horizontal rules are done using four or more hyphens:


This is a blockquote

Toggle MarkDown / HTML (t), full reference or close this