Database Reference
In-Depth Information
That is, you either use a placeholder and bind the data value to it, or else encode the
data and put it directly into the statement string.
The script shown in this recipe,
store_image.pl
, runs from the command line and stores
an image file for later use. The script takes no side in the debate over whether to store
images in the database or the filesystem: it implements
both
approaches! Of course, this
requires twice the storage space. To adapt this script for your own use, you'll want to
retain only the parts appropriate for the storage method you want to use. The necessary
modifications are discussed at the end of this section.
The
store_image.pl
script uses an
image
table that includes columns for the image ID,
name, and MIME type, and a column in which to store the image data:
CREATE
TABLE
image
(
id
INT
UNSIGNED
NOT
NULL
AUTO_INCREMENT
,
#
image
ID
number
name
VARCHAR
(
30
)
NOT
NULL
,
#
image
name
type
VARCHAR
(
20
)
NOT
NULL
,
#
image
MIME
type
data
LONGBLOB
NOT
NULL
,
#
image
data
PRIMARY
KEY
(
id
),
#
id
and
name
are
unique
UNIQUE
(
name
)
);
The
name
column indicates the name of the image file in the directory where images are
stored in the filesystem. The
data
column is a
LONGBLOB
, the largest
BLOB
type.
It is possible to use the
name
column to store full pathnames to images in the database,
but if you put them all under the same directory, you can store names that are relative
to that directory, and
name
values will take less space. That's what
store_image.pl
does.
It needs to know the pathname of the image storage directory, which is what its
$im
age_dir
variable is for. You should check this variable's value and modify it as necessary
before running the script. The default value reflects where I like to store images, but
you'll need to change it according to your own preferences. Make sure to create the
directory if it doesn't exist before you run the script, and set its access permissions so
that the web server can read and write files there. You'll also need to check and possibly
change the image directory pathname in the
display_image.pl
script discussed in
Recipe 19.7
.
The image storage directory should be outside the web server docu‐
ment tree. Otherwise, a user who knows or can guess the location may
be able to upload executable code and cause it to run by requesting it
with a web browser.
store_image.pl
looks like this:
#!/usr/bin/perl
# store_image.pl: read an image file, store in the image table and
