[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


Upload images into MySQL and show them on webpage

Posted on 2006-11-25
Medium Priority
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:

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


Question by:nickoarg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 84

Assisted Solution

ozo earned 320 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);
LVL 25

Accepted Solution

clockwatcher earned 680 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.



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;

Author Comment

ID: 18019918
Thanks a lot! I will try this today.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

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.
LVL 25

Expert Comment

ID: 18022667

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

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

Author Comment

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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

On Microsoft Windows, if  when you click or type the name of a .pl file, you get an error "is not recognized as an internal or external command, operable program or batch file", then this means you do not have the .pl file extension associated with …
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
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…
Six Sigma Control Plans

650 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