Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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
  • 3
  • 2
LVL 85

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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

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…
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
Suggested Courses

564 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