Solved

Upload images into MySQL and show them on webpage

Posted on 2006-11-25
6
297 Views
Last Modified: 2008-02-01
Hi experts and thanks for your time. I used to work with perl some years ago, but I'm hovaing problems with this. I need users to upload files (images) to a MySQL table and then have a different page where I can show those files.
What i have today is a script that will upload the files but only only to the filesystem.

The code I have is this:

#!c:/Perl/bin/perl.exe
use CGI qw(:standard);
$debug = "yes";
$parentURL = "http://$ENV{'HTTP_HOST'}";
$parentDirectory = "$ENV{'DOCUMENT_ROOT'}";
$URLToThisFile = "http://$ENV{'SERVER_NAME'}$ENV{'SCRIPT_NAME'}";
$pathToThisFile = "$ENV{'SCRIPT_FILENAME'}";

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$month = ($mon + 1);
$year += 1900;
$year %= 100;
$date = sprintf("%02d-%02d-%02d",$year,$month,$mday);

$showPhotoPostOption = "no";                  
$usePostingPassword = "no";                  
$maximumFileSize = "512000";                  
@allowedFileExtensions = ('jpg','jpeg','gif','bmp');
$useMimeTypeDetection = "yes";                  
$parentDirectory =~ s/\\\\/\\/g;
$parentDirectory =~ s/\\$//g;
$parentDirectory =~ s/\\/\\\\/g;
$parentDirectory = $parentDirectory . "\\PhotoAlbums";
$parentURL = $parentURL . "/PhotoAlbums";

&main();    # run script
exit 0;     # end program

sub main    
{
print "Content-type: text/html\n\n";

my $whatToDo = "postForm";        
if (param()) {$whatToDo = param('doWhat'); }                            

if ($whatToDo eq "postForm"){&postForm(param); }
if ($whatToDo eq "postPics"){&postPics(param); }

if ($debug eq "yes") { print "\n<!-- End sub main -->\n"; }
}



sub postForm
{

print "$header";
my $thisPage = "postForm";
$header = "
<!-- Begin HTML Header -->

<html>
<head>
      <title>Subir fotos</title>
</head>
<BODY BGCOLOR=black TEXT=white LINK=aa0000 VLINK=aa0000>

<center>
<table width=500 border=0 cellpadding=0 cellspacing=0>
<tr valign=top><td><center>
<font face=\"Times New Roman\" size=4>
<b><i>NAH! Fotos</b></i></font><br>
</center></td></tr>
<tr valign=top><td><center>
<br>

<!-- End HTML Header -->
";

$footer = "
<!-- Begin HTML Footer -->

<p>
</td>
</tr>

<tr valign=middle><td colspan=2>
<br>
</td></tr></table></td></tr></table>

<table cellpadding=4 border=1>
<tr><td colspan=2>
<center><font size=-1>
Photo Actions
</td></tr>
<tr valign=top>
<td>
<center><font size=-1>
Post:
<font color=red>
post
</td><td>
<center><font size=-1>
Delete:
<font color=red>
delete
</td></tr>
<tr><td colspan=2>
<center><font size=-1>
Album Actions
</td></tr>
<tr valign=top>
<td>
<center><font size=-1>
Create:
<font color=red>
create
</td><td>
<center><font size=-1>
Delete:
<font color=red>
delete
</td></tr>
<tr><td colspan=2>
<center>
<b>Albums are reset every 12 hours</b>
</td></tr>
</table>

</body>
</html>

<!-- End HTML Footer -->
";

print "\n<!-- Begin action: $thisPage -->\n";

print "<hr width=400>";

      print start_form(-enctype=>'multipart/form-data'),
      "<input type=hidden name=doWhat value=postPics>",
      "Foto a enviar:<br>\n";
      my $localSize = $maximumFileSize;      
    $localSize = $localSize/1000;
      print filefield(-name=>'fileName',
      -size=>25, -maxlength=>150),
      br,
      "(hasta $localSize K)",
      br,
      br;

      print "<br><br>Descripci&oacute;n:",
            br,
            textfield(-name=>'fileDescription', -default=>'',
                  -size=>25, -maxlength=>100), br, br;
      print submit(-value=>'Subir mi foto'),
      end_form;
print "<center><hr width=400>";
}


sub postPics
{
my $thisPage = "postPics";
print "\n<!-- Begin action: $thisPage -->\n";
my $myMaximumPost = $maximumFileSize + 1000;
if ($ENV{'CONTENT_LENGTH'} > $myMaximumPost ) {
      &error('Ese archivo es demasiado grande!');
}
my $fileDecription = param('fileDescription');
if ($fileDecription eq '') {
      &error('Falta poner descricpci&oacute;n');
}
my $file = param('fileName');
my @fileNameParts = split(/\//,$file);
$file = pop(@fileNameParts);

@fileNameParts = split(/\\/,$file);
$file = pop(@fileNameParts);

my $name = $file;
my $tempFileExtension = $file;
my @fileNameInfo = split(/\./,$tempFileExtension);
my $fileExtension = pop (@fileNameInfo);
my $goodFileType = "no";

      $file = param('fileName');
      my $info = uploadInfo ($file);
      my $realMimeType = $info -> {'Content-Type'};
      my $newExtension;

my $extension;
foreach $extension(@allowedFileExtensions) {
      if ($extension eq $fileExtension) {
            $goodFileType = "yes";
            last;
      }
}
      if ($realMimeType =~ m/^image/i) {
            if ($realMimeType =~ m/gif$/i)  { $newExtension = "gif";  }
            if ($realMimeType =~ m/jpg$/i)  { $newExtension = "jpg";  }
            if ($realMimeType =~ m/jpeg$/i) { $newExtension = "jpg"; }
            if ($realMimeType =~ m/bmp$/i)  { $newExtension = "bmp";  }
            if ($realMimeType =~ m/png$/i)  { $newExtension = "png";  }
            if ( $fileExtension ne $newExtension ) {
                  print "No es $fileExtension, parece $newExtension";
            }
            if (!$newExtension) {
                  $fileExtension eq $newExtension;
            }
      }
      else {
            print "\n<br>Mime Type: $realMimeType<br>",
            "no me parece un archivo de im&aacute;gen<br>\n";
            $goodFileType = "no";
      }

if ($goodFileType eq "no") {
      &error('No se permiten archivos con esa extensi&oacute;n');
} else {

      my $newFileName = $fileDecription;
      $newFileName =~ s/^\s+//g; ### remove leading spaces
      $newFileName =~ s/\s+$//g; ### remove trailing spaces
      $newFileName =~ s/..\//_/g;  
      $newFileName =~ s/ /_/g;  
      $newFileName =~ s/\//_/g;    
      $newFileName =~ s/\"//g;
      $newFileName =~ s/'//g;
      $newFileName =~ s/\./_/g;
      $newFileName =~ s/,/_/g;
      $newFileName =~ s/:/_/g;
      $newFileName =~ s/\;/_/g;
      $newFileName =~ s/!/_/g;
      $newFileName =~ s/\@/_/g;
      $newFileName =~ s/\-/_/g;
      $newFileName =~ s/\+/_/g;
      $newFileName =~ s/\[/_/g;
      $newFileName =~ s/\]/_/g;
      $newFileName =~ s/\{/_/g;
      $newFileName =~ s/\}/_/g;
      $newFileName =~ s/\|/_/g;
      $newFileName =~ s/\?/_/g;
      $newFileName =~ s/</_/g;
      $newFileName =~ s/>/_/g;

            my $i;
            for ($i=0, $i<10, $i++) {
                  $newFileName =~ s/__/_/g; }
      $newFileName =~ s/^_//g;
      $newFileName =~ s/_$//g;
      my $localFileName = join (".", $date, $newFileName, $fileExtension);
      if ($newFileName ne ""){
            my $fileTotalPath;
            my $fileTotalNewPath;

            if ($servertype eq"unix") {
                  $fileTotalPath = "$parentDirectory/$fileDirName/$name";
                  $fileTotalNewPath = "$parentDirectory/$fileDirName/$localFileName";
            }
            else {
                  $fileTotalPath = "$parentDirectory\\$fileDirName\\$name";
                  $fileTotalNewPath = "$parentDirectory\\$fileDirName\\$localFileName";
            }
            open(LOCAL, ">$fileTotalPath") or &error('No se puede crear un nuevo archivo');
            if ($servertype ne "unix") { binmode LOCAL; }
            while(<$file>) {
               print LOCAL $_;      
            }
            close LOCAL or &error('No se puede cerrar el archivo nuevo');
            rename("$fileTotalPath","$fileTotalNewPath");
      }
      else {
            &error('Esa descripci&oacute;n no est&aacute; buena!');
      }

      my $displayPhoto = $newFileName;
      $displayPhoto =~ s/_/ /g;
      my $displayAlbum = $fileDirName;
      $displayAlbum =~ s/_/ /g;
      print "<hr width=400>\n",
      "<b>Env&iacute;o exitoso!</b><br><br>\n";
      print "\n$displayPhoto<br>\n",
      "Ser&aacute; revisada por nuestro jurado<br>\n",
      print "<hr width=400>\n";
      &endPage('$thisPage');
      }

}

sub endPage
{
if ($debug eq "yes") { print "\n<!-- Begin sub endPage -->"; }

my ($thisPage) = @_;
print "\n<!-- End the display for action: $thisPage -->\n";
print "<center>\n<table width=400 border=0><tr valign=top>",
"<td width=130 align=right>\n";
my $tempnumber = "0";
if ($thisPage ne "showForm") {
      print start_form,
      "<input type=hidden name=doWhat value=postForm>\n",
      submit(-value=>'Subir otra'),
      end_form;
      $tempnumber++;
}
print "</td>\n<td width=140 align=center>";

      if ($thisPage ne "postForm") {
      my $numberOfAlbums;
      if ($numberOfAlbums ne "0"){
                  print "<td>\n";
                  print start_form,
                  "<input type=hidden name=doWhat value=postForm>\n",
                  submit(-value=>'Posting Page'),
                  end_form;
                  print "</td>\n";
      }
      }
      $tempnumber++;


print "</td></tr>\n</table>";
if ($tempnumber ne "0") {
      print "<hr width=400>\n";
      }

print "<center><i>\n ";
print "$generatedTag";
print "$footer";
if ($debug eq "yes") { print "\n<!-- End sub endPage -->"; }
}

sub error
{
my $thisPage = "Error";
print "<h3>Pas&oacute; algo raro:", br;
print @_;
print "</h3>";
print "<center><hr width=400>\n";
&endPage($thisPage);
exit 0;
}


Please, can you show me an example of a cgi that will upload a picture to a mysql table and one that will show that? I can manage from there.
If you are feeling helpfull today can you help me modify this so that the upload is done to a MySQL table? :D

Thanks!!

Nico
0
Comment
Question by:nickoarg
  • 3
  • 2
6 Comments
 
LVL 84

Assisted Solution

by:ozo
ozo earned 80 total points
ID: 18017065
           use DBI;
           $dbh = DBI->connect("DBI:mysql:database=test", $user, $password, {'RaiseError' => 1}) or &error($!);
           $dbh->do("CREATE TABLE images (name VARCHAR(50), image LONGBLOB)");
           $dbh->do("INSERT INTO images VALUES (?, ?)", undef, $fileTotalNewPath, join'',<$file>) or &error('No se puede cerrar el archivo nuevo');



           $image  = $dbh->selectrow_array( "select image from images where name = ?", undef, $fileTotalNewPath);
0
 
LVL 25

Accepted Solution

by:
clockwatcher earned 170 total points
ID: 18017275
Here's a CGI example.   It takes a JPEG upload, stores it in the database, and then retrieves it.  It assumes a table filedata defined as:

CREATE TABLE `filedata` (
  id int(11) NOT NULL auto_increment,
  filename varchar(100),
  contenttype varchar(100),
  data longblob NOT NULL,
  PRIMARY KEY  (`id`)
);


And requires the script name be Q_22072401.pl.

Q_22072401.pl
-----------------

#!/usr/bin/perl

use CGI;
use DBI;
use DBD::mysql;

my $cgi = new CGI;
my $index;
my $dsn = "DBI:mysql:database=somedb;host=localhost";
my $uid = "uid";
my $pwd = "pwd";
my $error = "";

if (my $filename = $cgi->param("upload"))  {
      
      if ($filename =~ /\.jpg$/i) {      
            binmode $filename;
            local $/;
            my $filedata = <$filename>;
            my $sql = "insert into filedata (contenttype, filename, data) values (?, ?, ?)";
            my $dbh = DBI->connect($dsn, $uid, $pwd);
            my $sth = $dbh->prepare($sql);
            $sth->execute("image/jpeg", $filename, $filedata);
            $index = $dbh->selectrow_array("select LAST_INSERT_ID()");
            $dbh->disconnect();
      }
      else {
            $error = "<p>JPEG uploads only.</p>"
      }
            
}
unless ($cgi->param("id")) {
      my $lastimage;
      if ($index) {
            $lastimage = <<;
            <p>Added image id: $index</p>
            <img src="Q_22072401.pl?id=$index">

      }

      print $cgi->header;
      print <<;
      <HTML>
      <body>
      <form method="post" ENCTYPE="multipart/form-data">
      <input name="upload" type="file">
      <input type="submit">
      </form>
      $lastimage
      $error
      </html>

}
else {
      my $index = $cgi->param("id");
      my $dbh = DBI->connect($dsn, $uid, $pwd);
      my $sql = "SELECT OCTET_LENGTH(data) FROM filedata WHERE id = ?";
      my $sth = $dbh->prepare($sql);
      $sth->execute($index);
      ($dbh->{LongReadLen}) = $sth->fetchrow_array();
      $sth = $dbh->prepare("select contenttype, data from filedata where id = ?");
      $sth->execute($index);
      

      ($contenttype, $data) = $sth->fetchrow_array();

      print "Content-type: $contenttype\n\n";
      print $data;
      
      $sth->finish();
      $dbh->disconnect();
}
0
 
LVL 3

Author Comment

by:nickoarg
ID: 18019918
Thanks a lot! I will try this today.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 3

Author Comment

by:nickoarg
ID: 18021844
Hi guys, thanks again. I'm having problems now tryng to display the image on the page. How do I do that? do I have to write the file to disk and insert the filename on the html code, and later delete it? or can I send the output to the html page? On the ozo answer I have the image now on the variable called $image. If I do print $image, I get the dialog to download the file. The same happens with the answer from clockwatcher. Please help me again with this.
Thanks!
0
 
LVL 25

Expert Comment

by:clockwatcher
ID: 18022667

You need it to be the source of an image tag.

  <img src="yourcgi.pl?id=someid">
0
 
LVL 3

Author Comment

by:nickoarg
ID: 18022692
yep, I figured out after some trial and errors.
Thanks again!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I've just discovered very important differences between Windows an Unix formats in Perl,at least 5.xx.. MOST IMPORTANT: Use Unix file format while saving Your script. otherwise it will have ^M s or smth likely weird in the EOL, Then DO NOT use m…
Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now