Table for MySQL

Need a little help with Table for MySQL

[AlexB77]

Hi to all,

Need a little help with table for MySQL.

I need to create a table that will have originale file name - that is lokated on my server in folder called "files" and need also to have a random name of the same file.

So that it looks like this one bellow

/files/excel-book.xls(real Name of the file) - excelbook
/files/macro-book.xls(real Name of the file) - macrobook
/files/codec-book.xls(real Name of the file) - codecbook

the reason I need it is to have a "download now" links on the
other pages of the site that will pint to the random name so once user will click on it than the /download.php should idealy triger the download of the file with this particular randome name (ex. <a href="http://www._____.com/download.php?urlid=excelbook">Download Now</a> than it should open /files/excel-book.xls this file)

Thanks to all for your help and thanks to this forum as well

[dreamcatcher]

Hi AlexB77,

I`m not really sure what you are asking. Can`t you just use a varchar field to store this entry? Is it something database specific you aren`t sure about?

dc

[arlo]

If I understand this correctly, all you need it a table with probably 3 fields max.

Table: FILES

Fields:
file_id (auto-increment)
file_name
file_display_name

With those 3 fields you can keep track of the files in your directory and you keep track of a "random" (variable) name for each one (for your links). Let me know if this is what you needed.

[MartinJ]

I'm kinda new at database creation, so I hope this isn't a stupid suggestion...

But, out of curiosity, since it looks like the random name (alias) needs to be unique, wouldn't it be better to make that the primary key instead of an ID? This assumes, of course, that the alias exists at the time the row is added to the table.

It seems like he would need some sort of constraint on this field, since it's used to find the real filename, so he wouldn't want duplicates.

Marty Fried

[arlo]

Yup, you are absolutely right. I just figured he wanted to keep the urls people friendly with words instead of id's.

Also, when you put id's in the url you are inviting for people to start playing with your id's and start downloading all your files..Of course you can take additional measures to make sure this doesn't happen, but you can stick with words and skip "additional measures"

[AlexB77]

HI to All,

This is exactly what I was trying to do,

Imagine if you have a file let’s say cash.xls in the folder called "files" so the normal URL will look like this [yoursite.com...] by clicking on this url you will obviously get a pop up file download window.

What I was trying to do is I did not want to show the real location of the file and therefore I have had to use "download.php", but also had to modify it to read My SQL, were I have a table that contains a random name of the file and the path to the real destination of the file on my server associated with it. This is why I needed to use download.php?urlid=cash instead of the one above, because only by doing this you can keep the real destination path unrevealed to users.

But I still have some problems with it at the moment and trying to make it all work.

Will post again once I am done with it.

Or if anyone have already figured this out than you help will be much appreciated.

[arlo]

I believe you can do all you need to do with this table.

Table: FILES

Fields:
file_id (auto-increment)(PK)
file_name
file_display_name

Query: SELECT * FROM `FILES` WHERE `file_name`='$URL_VARIABLE'

Most of your work will be done on your programming language. If you are using PHP you can easily strip the filename and get a URL friendly name to use as it's "alias".

Once the info is in the DB. You select the file with the above query and get it's filename and force a download.

[arlo]

Correction for the above query:

Query: SELECT * FROM `FILES` WHERE `file_display_name`='$URL_VARIABLE'

[AlexB77]

HI,

the DB is rejecting the field "file_display_name" by sing incorrect field name.

Any idea why?

[AlexB77]

Never mind I have done it

[AlexB77]

will this php script cause any security problems?

[AlexB77]

I am a little stack with download.php file. do not know how to connect it to my sql to read data from the table once user clicks on the "donload now" link

[arlo]

PHP CODE:

<?
# Local Variables
$host = 'localhost';
$db_name = 'YOUR_DB_NAME';
$db_user = 'YOUR_DB_USER';
$db_pass = 'YOUR_DB_USER_PASSWORD';
$path_to_file_directory = 'files/';
# Database Connection 
mysql_connect( $host, $db_user, $db_pass);
mysql_select_db( $db_name );
# Get Link Variable
# Later, you will filter out possible injections (hacks)
$file_code = $_GET['urlid']; 
# Query for file info
$res = mysql_query("SELECT * FROM `FILES` WHERE `file_display_name`='".$file_code."'");
# I will skip the part where you make sure you get a result back.
$info = mysql_fetch_assoc($res);
# File path is below
$file_path = $path_to_file_directory.$info['file_name'];
# Now push the download through the browser
# There is more than 1 way to do this.
if (file_exists($file_path)) {
  header('Content-Description: File Transfer');
  header('Content-Type: application/octet-stream');
  header('Content-Disposition: attachment; filename='.basename($file_path));
  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($file_path));
  ob_clean();
  flush();
  readfile($file_path);
  exit;
}
?>

Hope this helps.

[arlo]

Forgot to mention that the above code IS the download.php script. You can probably copy paste, just change the top "local variables". Hope this helps.

[AlexB77]

Thank you will try it today. Do you think that this is safe to do it this way? Will I not get hacked?

[arlo]

Whenever you read a "external" variable such as URL, session, cookie and form inputs you run the risk of getting hacked.

In your case you only need to worry about the line:
$file_code = $_GET['urlid'];

You will need to research a bit about filtering different types of input. PHP has a function that does an attempt to filter out different inputs.

Here is the URL for php's filtering function:
[us.php.net...]

If you choose to follow the above link's implementation, the line of code in question would look like this:
$file_code = filter_input( INPUT_GET, 'urlid' );

This should make you very very safe against being hacked.

[AlexB77]

I wanted to implement the part of the code (that connects to DB and reads the file names) in to another version of download.php file. Check the code below you will understand what I mean. This code seems to be more secure and also it does give a chance to have a downloads.log file as well. For some reason I cannot make it work it is always giving me line 81 error. Do you think I am doing something wrong?

What I want to do is to use the code that you have sent to me above (this is by the way works perfect) to work with the code that is posted in this reply, but I cannot see this happening just yes still working on it. If you have any idea how please let me know.

<?php

###############################################################
# File Download 1.3
###############################################################
# Visit http://www.example.com/ for updates
###############################################################
# Sample call:
# download.php?f=phptutorial.zip
#
# Sample call (browser will try to save with new file name):
# download.php?f=phptutorial.zip&fc=php123tutorial.zip
###############################################################

// Allow direct file download (hotlinking)?
// Empty - allow hotlinking
// If set to nonempty value (Example: example.com) will only allow downloads when referrer contains this text
define('ALLOWED_REFERRER', '');

// Download folder, i.e. folder where you keep all files for download.
// MUST end with slash (i.e. "/" )
define('BASE_DIR','files/');

// log downloads? true/false
define('LOG_DOWNLOADS',true);

// log file name
define('LOG_FILE','downloads.log');

// Allowed extensions list in format 'extension' => 'mime type'
// If myme type is set to empty string then script will try to detect mime type
// itself, which would only work if you have Mimetype or Fileinfo extensions
// installed on server.
$allowed_ext = array (

// archives
'zip' => 'application/zip',

// documents
'pdf' => 'application/pdf',
'doc' => 'application/msword',
'xls' => 'application/vnd.ms-excel',
'ppt' => 'application/vnd.ms-powerpoint',

// executables
'exe' => 'application/octet-stream',

// images
'gif' => 'image/gif',
'png' => 'image/png',
'jpg' => 'image/jpeg',
'jpeg' => 'image/jpeg',

// audio
'mp3' => 'audio/mpeg',
'wav' => 'audio/x-wav',

// video
'mpeg' => 'video/mpeg',
'mpg' => 'video/mpeg',
'mpe' => 'video/mpeg',
'mov' => 'video/quicktime',
'avi' => 'video/x-msvideo'
);

####################################################################
### DO NOT CHANGE BELOW
####################################################################

// If hotlinking not allowed then make hackers think there are some server problems
if (ALLOWED_REFERRER !== ''
&& (!isset($_SERVER['HTTP_REFERER']) ¦¦ strpos(strtoupper($_SERVER['HTTP_REFERER']),strtoupper(ALLOWED_REFERRER)) === false)
) {
die("Internal server error. Please contact system administrator.");
}

// Make sure program execution doesn't time out
// Set maximum script execution time in seconds (0 means no limit)
set_time_limit(360);

if (!isset($_GET['f']) ¦¦ empty($_GET['f'])) {
die("Please specify file name for download.");
}

// Get real file name.
// Remove any path info to avoid hacking by adding relative path, etc.
$fname = basename($_GET['f']);

// Check if the file exists
// Check in subfolders too
function find_file ($dirname, $fname, &$file_path) {

$dir = opendir($dirname);

while ($file = readdir($dir)) {
if (empty($file_path) && $file != '.' && $file != '..') {
if (is_dir($dirname.'/'.$file)) {
find_file($dirname.'/'.$file, $fname, $file_path);
}
else {
if (file_exists($dirname.'/'.$fname)) {
$file_path = $dirname.'/'.$fname;
return;
}
}
}
}

} // find_file

// get full file path (including subfolders)
$file_path = '';
find_file(BASE_DIR, $fname, $file_path);

if (!is_file($file_path)) {
die("File does not exist. Make sure you specified correct file name.");
}

// file size in bytes
$fsize = filesize($file_path);

// file extension
$fext = strtolower(substr(strrchr($fname,"."),1));

// check if allowed extension
if (!array_key_exists($fext, $allowed_ext)) {
die("Not allowed file type.");
}

// get mime type
if ($allowed_ext[$fext] == '') {
$mtype = '';
// mime type is not set, get from server settings
if (function_exists('mime_content_type')) {
$mtype = mime_content_type($file_path);
}
else if (function_exists('finfo_file')) {
$finfo = finfo_open(FILEINFO_MIME); // return mime type
$mtype = finfo_file($finfo, $file_path);
finfo_close($finfo);
}
if ($mtype == '') {
$mtype = "application/force-download";
}
}
else {
// get mime type defined by admin
$mtype = $allowed_ext[$fext];
}

// Browser will try to save file with this filename, regardless original filename.
// You can override it if needed.

if (!isset($_GET['fc']) ¦¦ empty($_GET['fc'])) {
$asfname = $fname;
}
else {
// remove some bad chars
$asfname = str_replace(array('"',"'",'\\','/'), '', $_GET['fc']);
if ($asfname === '') $asfname = 'NoName';
}

// set headers
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: public");
header("Content-Description: File Transfer");
header("Content-Type: $mtype");
header("Content-Disposition: attachment; filename=\"$asfname\"");
header("Content-Transfer-Encoding: binary");
header("Content-Length: " . $fsize);

// download
// @readfile($file_path);
$file = @fopen($file_path,"rb");
if ($file) {
while(!feof($file)) {
print(fread($file, 1024*8));
flush();
if (connection_status()!=0) {
@fclose($file);
die();
}
}
@fclose($file);
}

// log downloads
if (!LOG_DOWNLOADS) die();

$f = @fopen(LOG_FILE, 'a+');
if ($f) {
@fputs($f, date("m.d.Y g:ia")." ".$_SERVER['REMOTE_ADDR']." ".$fname."\n");
@fclose($f);
}

?>

[edited by: tedster at 9:10 pm (utc) on Sep. 3, 2009]
[edit reason] switch to example.com [/edit]

[arlo]

The code you pasted works a little differently than what you wanted to do.

The above code gets the ACTUALY filename from the URL. If you want to use the above code, your scenario would look something like this: download.php?urlid=excel-book.xls

Many people (including myself) would tell you never to pass file names through the URL. Obviously you will get many hacking attempts with such a setup. That is why the above code seems to be more secure, because they NEED to be. Also, when you pass the ACTUAL filename through the URL you have no need to query a Database.

I agree with your approach. You want to have an "alias" or "random name" for each file in your files directory, then pass that alias through the URL. Thus you have setup a table in your database to hold such information.

In conclusion, your approach is different from the sample code you pasted above. You will have to decide which technique you will use for "download.php".

[arlo]

Here is an update of the code I gave you. This makes it much more secure.

<?
# Local Variables
$host = 'localhost';
$db_name = 'YOUR_DB_NAME';
$db_user = 'YOUR_DB_USER';
$db_pass = 'YOUR_DB_USER_PASSWORD';
$path_to_file_directory = 'files/';
# Database Connection
mysql_connect( $host, $db_user, $db_pass);
mysql_select_db( $db_name );
# Get Link Variable
# Later, you will filter out possible injections (hacks)
$file_code = filter_input( INPUT_GET, 'urlid' ); 
# Query for file info
$res = mysql_query("SELECT * FROM `FILES` WHERE `file_display_name`='".$file_code."'");
# If query is empty, there is a bad code name
# This catches possible hacking attempt.
if( mysql_num_rows($res) == 0 )
{
  echo 'There will be no hacking on this website! =)';
  exit();
}
# Save file info into an array called "$info"
$info = mysql_fetch_assoc($res);
# File path is below
$file_path = $path_to_file_directory.$info['file_name'];
# Now push the download through the browser
# There is more than 1 way to do this.
if (file_exists($file_path)) {
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename='.basename($file_path));
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($file_path));
ob_clean();
flush();
readfile($file_path);
exit;
}
?> 

You can use the above code for "download.php", i added extra lines to ensure security. Now you can play with the URL variable and see that it will be much harder to hack.

If you need help with the code to display all the files and their links, let me know.

[AlexB77]

Thanks for this.

I am getting this mistake all the time

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/fhlinux199/s/YourSiteName.com/user/htdocs/download.php on line 20
(There will be no hacking on this website! =)

[AlexB77]

I have used a different approach

# Detect missing filename
if(!$file_code) die("I'm sorry, you must specify a file name to download.");

# Make sure we can't download files above the current directory location.
if(eregi("\.\.", $file_code)) die("I'm sorry, you may not download that file.");
$file = str_replace("..", "", $file_code);

# Make sure we can't download .ht control files.
if(eregi("\.ht.+", $file_code)) die("I'm sorry, you may not download that file.");

do you think this works the same way?

[AlexB77]

Hi Arlo,

do you think that I could do the same for all of my affiliate links? Any siggestions?

thanks

[arlo]

Alex,

on line 16 add "or die (mysql_error())" before the ;

It will look something like this:
mysql_query("SELECT * FROM `FILES` WHERE `file_display_name`='".$file_code."'") or die ( mysql_error() );

It looks like you might have an error in your query. That is the only reason the $res variable would not be a valid MySQL result resource.

About the extra lines of code. That seems to be awesome. There is no harm in including those lines, but again looks like that would only be useful when you pass the ACTUAL filename through the url.

In your case, you have the filenames in your database, thus the users cannot alter what is safely secured in your database. The only thing in your url is the "alias codename" for a file, not the actual filename.

BUT if you decide to use no database and pass the ACTUAL filename through the url, then you will definitely need extra security like the example you pasted above.

[AlexB77]

Thank it has all worked out in the end.

I am thinking about hiding all my affiliate links in the same way, do you think that this is possible to do it by using the same code or do I need to add something else.

Thanks Again.

[arlo]

What do you mean by affiliate links?

[AlexB77]

If I have a link that looks like this below
http://www.example.com/cmd.php?Clk=55664477

can I make it look like this?

http://mysite.tld/go.php?=dashbords

do you think that the script similar to the one you helped me with can handle this too?

[edited by: tedster at 9:12 pm (utc) on Sep. 3, 2009]
[edit reason] make the urls anonymous [/edit]

[arlo]

yup..it should do the trick.

[AlexB77]

Hi Arlo,

I have another question to you.
Do you think that this script cam be modified to do another trick like fo example once user has clicked on Download Now link, instead of only an actual file download that starts with this script I can also have another page that can also have "Click here if download did not start after 5 seconds" or similar?

Thanks

[arlo]

sure you just need to create a link with:

<a href="http://PATH_TO_THE_FILE">Click here if download did not start after 5 seconds</a>

[AlexB77]

Thanks, but this is not what need.

What I need is to have is some sort of the middle page that can be opened when user clicks on the "Download Now" together along with the prompt to open, save or cancel file download box. The page itself should have the link to the site so that if for some reason the download did not start automatically than user can click on the link provided on the page. More to it the page itself should retrieve the link automatically so that I do not have to create a separate page for each downloadable file.

Thanks