How is my website vulnerable for sql injections?

Question

I am running a small website where I have published some articles. I am also very interesed in security topics and tried sqlmap on my website. It found the database name, so my question is how it could find it.

The website is very basic and does just contain a very small number of php files and I have just tested on the index.php file:
sqlmap.py -u http://myurl.com/index.php?id=5 --dbs
In the index.php I have used the php functions mysql_real_escape_string and stripslashes to clean the string before using it in the SQL question and if the visitor tries to mess with the id in the URL, then I just reload the index.php:

$id = $_GET['id'];
if (get_magic_quotes_gpc()) {
    $id = stripslashes($id);
}
$id = mysql_real_escape_string($id);

$sql = "SELECT * , COUNT( * ) AS amount
            FROM articles
            WHERE id = $id
            GROUP BY id";
$result = mysql_query($sql) or die(header("Location: index.php"));

So how on earth could sqlmap.py get the database name out of the above information?

Could you please explain how it is possible and also provide an example?

Answer 1

Always use parameterized queries, string concatenation leaves room for error, stop leaving that room (they also look a lot nicer).

Also, because id is an int, I don't need quotes to inject:

$id = '0; Drop Table myTable;--';

$sql = "SELECT * , COUNT( * ) AS amount
        FROM articles
        WHERE id = 0; Drop Table myTable;--
        GROUP BY id";

I like to use is_numeric to check if it's a number and kick them out of it isn't.

Edit:

PDO provides preparation of SQL queries: http://php.net/manual/en/pdo.prepare.php

So for example:

$sql = "SELECT * , COUNT( * ) AS amount
    FROM articles
    WHERE id = :id
    GROUP BY id";
$sth = $dbh->prepare($sql);
$sth->execute(array(':id' => $id));

Answer 2

First off, you're taking the right steps by not just assuming that your code is secure, but by actually testing it.

Secondly, Sqlmap will give you some levels of details as to exactly how it arrived at an SQLi point. Try some of the following when you run it:

sqlmap.py -v 3 --dbs -u http://yoursite.com/index.php?id=5

The -v 3 switch will cause it to print out the exact payload injected. You should also be able to see this in your web server's access and error logs as well.

Third, looking at your code snippet, you might want to try:

(int)$_GET['id'] 

But if you can maybe give us some more detail on what sqlmap is saying it's injection point is, such as timed, blind, union, boolean, etc. we could get into more detail.

Answer 3

All the answers are ok, but in your particular case you escaped the number as a string (mysql_real_escape_string()), but you didn't enclose it in single quotes as a string should be.

If, instead of:

$sql = "SELECT * , COUNT( * ) AS amount
        FROM articles
        WHERE id = $id
        GROUP BY id";

you used:

$sql = "SELECT * , COUNT( * ) AS amount
        FROM articles
        WHERE id = '$id'
        GROUP BY id";

there would be no SQL injection a decreased risk of SQL injection. But it's always better to:

• use prepared statements
• use strict input validation - in your case you should be casting id to integer.

Answer 4

The short answer in this particular situation, and because the $id is always going to be a number, is as Neil stated, to filter the input to make sure it is always going to be an integer no matter what.

Example concept:

$id = ( false !== ( int )$_GET[ 'id' ] >= 0 ) ?
        ( int )$_GET[ 'id' ] :
        die( header( "Location: ./index.php" ) );

Something like that would make sure that $id can only ever be an integer and not less than 0 (i.e. not -1). What you do with a failed id is up to you, in this example the page is redirected to the index.php.

In this example above, the following request of

id=55+union+select+1,2,3,4,5,6,7,8,9,10,11,12,13

Would result in $sql being:

SELECT * , COUNT( * ) AS amount FROM articles
WHERE id = 55 GROUP BY id

instead of what the current code would return which is:

SELECT * , COUNT( * ) AS amount FROM articles
WHERE id = 55 union select 1,2,3,4,5,6,7,8,9,10,11,12,13 GROUP BY id