Solved

Upload images into MySQL and show them on webpage

Posted on 2006-11-25
6
298 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need help with bash and/or perl commands on OS X Terminal 9 100
stftime format 4 55
Extract data from span tag 1 91
Regular Expression for URL 10 90
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…
In the distant past (last year) I hacked together a little toy that would allow a couple of Manager types to query, preview, and extract data from a number of MongoDB instances, to their tool of choice: Excel (http://dilbert.com/strips/comic/2007-08…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

947 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

21 Experts available now in Live!

Get 1:1 Help Now