Upload images into MySQL and show them on webpage

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:

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 -->

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

<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>
<tr valign=top><td><center>

<!-- End HTML Header -->

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


<tr valign=middle><td colspan=2>

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


<!-- 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),
      "(hasta $localSize K)",

      print "<br><br>Descripci&oacute;n:",
            textfield(-name=>'fileDescription', -default=>'',
                  -size=>25, -maxlength=>100), br, br;
      print submit(-value=>'Subir mi foto'),
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";
      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');
      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";


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'),
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'),
                  print "</td>\n";

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";
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


Who is Participating?

Improve company productivity with a Business Account.Sign Up

clockwatcherConnect With a Mentor Commented:
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.



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()");
      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 <<;
      <form method="post" ENCTYPE="multipart/form-data">
      <input name="upload" type="file">
      <input type="submit">

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);
      ($dbh->{LongReadLen}) = $sth->fetchrow_array();
      $sth = $dbh->prepare("select contenttype, data from filedata where id = ?");

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

      print "Content-type: $contenttype\n\n";
      print $data;
ozoConnect With a Mentor Commented:
           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);
nickoargAuthor Commented:
Thanks a lot! I will try this today.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

nickoargAuthor Commented:
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.

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

  <img src="yourcgi.pl?id=someid">
nickoargAuthor Commented:
yep, I figured out after some trial and errors.
Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.