Possible injection from date string Select query

Question

Hi all.

I have a problem wich is a little strange. My page contains a html link which refreshes the page and calls a PHP variable. This variable appends a date string to the url string which is fed into a MySQL query, which grabs records matching this date. I think this is causing an injection as it sometimes deletes the user from the database!

I know there may be security issues using the '#' in the hyperlink, but I'd like to know whats going on. Also would this have different effects on different browsers seeing as how it uses javascript. The users being deleted seems to happen only on some peoples computers.

The PHP code calculates a timestamp three days from now and then puts it into a SQL format:

$ts_threeDays   = mktime(1,0,0,date('m'), date('d')+3-date('w'), date('y'));     
$threeDaysAhead = date('y-m-d', $ts_second_day);

The script then listens for the 'day' variable in the url string passed by the hyperlink on the page:

$date = mysql_real_escape_string($_GET['day']);

The JavaScript and hyperlink is:

<a href='#' onClick="document.location.href='planner.php?day=<?php echo $threeDaysAhead; ?>'"> 3 Days Later</a>

The MySQL query is bigger but the only input it takes from user action is the above date string. Query basically looks like this (uses another select statement to access users table):

SELECT planner.details FROM planner 
WHERE  planner.date = '$date' AND users.`user_id` = '$id' // Logged in Id superglobal

If anyone can help me out and explain my problem I will be most grateful. Many thanks

Answer 1

As you're passing $date through mysql_real_escape_string, suspicion has to fall on either $id, or something we can't see.

A SELECT statement isn't going to delete stuff from your db. What else do you have in your PHP file that's responsible for deleting users, and could you have some broken if/else logic that ends up passing through a function to delete users when they really shouldn't be?

Answer 2

Previous responses explain very well your problem.

And you can validate your 'day' get var like this way:

$day = '';
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $_GET['day'])
{
    $day = $_GET['day'];
} else {
    die("bye bye");
}

Answer 3

Thanks for your replies. The page that this code is on contains no DELETE queries, just the one SELECT. I read a post here on stack overflow that said injections can happen with SELECT statments. As for the ID variable, when they login it the code sets it to a session:

$_SESSION['user_id'] = $row['user_id'];  // row is from a sql query

Then on each page I call a security file which checks if the session is set and if it is it will set it to a global variable:

if(isset($_SESSION['user_id']))
{   
$id = $_SESSION['user_id'];
}

Still confusing me this one