SQL Select not retrieving data based on query

Question

I'm trying to push a file to browser from SQL via the use of the file token which is created and assigned to the file during the upload process. But my SELECT SQL is not working for anything other then file id field which is the only one that seems to fire up SELECT request here is the code

$item = $_GET['item'];

$sql = 'SELECT * FROM `files` WHERE file_token = '.$item.'';
            $result = mysql_query($sql);

            if(!$result) {

                echo '<div style="padding:8px;background-color:#fae3e3;border:2px solid #b25959;color:#313131;">Error!</div>';

             } else {

                  while($obj = mysql_fetch_array($result)) {

                                     $file_type = $obj['file_type'];
                                     $file_size = $obj['file_size'];
                                     $file_name = $obj['file_name'];
                                     $file_hash = $obj['file_hash'];

                                     $name = 'encrypted/'.$file_hash;

if (file_exists($name)) {
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename='.basename($file_name));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($name));
    ob_clean();
    flush();
    readfile($name);
    exit;
}

                   }
            }
mysql_query("UPDATE `files` SET file_views = file_views+1 WHERE file_token = '.$item.'");

mysql_close();

Is there something wrong with my SELECT statement? And tokens look like this in SQL

Example: 3ed3:3ba6:eb24:5816:6d8b:be06:79e1:b20b

Please discontinue use of the mysql_* functions as they are deprecated. Look into using PDO or mysqli instead. Additionally look in to SQL Injection. What if $GET['item'] contained a malicious bit of SQL? — jcbwlkr
– jcbwlkr, Commented Jul 8, 2013 at 13:06
@JoachimIsaksson ye im aware, this is localhost at moment. Im going to wrap majority of things into functions with sql escape and so on. — user2293381
– user2293381, Commented Jul 8, 2013 at 13:07
@jacobwalker0814 I did try PDO, sadly i can't get it to work and do basic things like that very same SELECT, i went over Stack and PHP examples yet when I try it, it does not work. — user2293381
– user2293381, Commented Jul 8, 2013 at 13:11

xlecoustillier · Accepted Answer · 2013-07-08 13:02:46Z

1

Try:

$sql = 'SELECT * FROM `files` WHERE file_token = \''.$item.'\'';

answered Jul 8, 2013 at 13:02

xlecoustillier

16.4k15 gold badges64 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user · Accepted Answer · 2013-07-08 13:02:21Z

0

try to put quotes arround the $item

$sql = 'SELECT * FROM files WHERE file_token = "'.$item.'"';

answered Jul 8, 2013 at 13:02

user

5451 gold badge11 silver badges32 bronze badges

2 Comments

user2293381 Over a year ago

Thanks that works, also do you mind checking my mysql_query("UPDATE files SET file_views = file_views+1 WHERE file_token = '.$item.'"); it does not seem to work either

user Over a year ago

Add quotes arround the $item again

Community · Accepted Answer · 2017-05-23 11:56:32Z

As others have pointed out your issue has to do with no properly quoting strings when querying the database. That said there is a more fundamental issue at play with SQL Injection in general. Let's take a look at your first two lines:

$item = $_GET['item'];
$sql = 'SELECT * FROM `files` WHERE file_token = '.$item.'';

$item is set to whatever the user feels like providing to you and then directly dropped into your query. If the user is polite and sends along an actual file token everything ends up nice:

// http://example.com/yourapp.php?item=5
SELECT * FROM `files` where file_token = 5;

What if the user sends in some random string of data instead?

// http://example.com/yourapp.php?item=John%20Doe
SELECT * FROM `files` where file_token = John Doe;

The above SQL would be invalid. That string would need to have quotes around it such as:

SELECT * FROM `files` where file_token = "John Doe";

Editing your code to simply add the quotes may seem like enough of a solution, but it isn't. If we look at a solution like:

$sql = 'SELECT * FROM files WHERE file_token = "'.$item.'"';

We will indeed add quotes around whatever is passed in by the user. So in the example of John Doe we would get the proper SQL with John Doe quoted. What if the user decides instead that they wish to submit a GET request with the term 5"; TRUNCATE TABLE files; ";

our SQL would end up looking like:

SELECT * FROM files WHERE file_token = "5"; TRUNCATE TABLE files; "";

The single query now becomes 3:

SELECT * FROM files WHERE file_token = "5";
TRUNCATE TABLE files; 
"";

You could try to go farther by stipping out semi-colons or similar; but there's no need to reinvent the wheel. Check out this great SO answer for specifics on preventing SQL injection in PHP.

Collectives™ on Stack Overflow

SQL Select not retrieving data based on query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related