Database Reference
In-Depth Information
Recipe 13.12
). This inserts the row if the name doesn't already exist, or updates the image
value if it does.
The
REPLACE
statement that stores the image information into MySQL is relatively
mundane:
$dbh
->
do
(
"REPLACE INTO image (name,type,data) VALUES(?,?,?)"
,
undef
,
$image_name
,
$mime_type
,
$data
);
If you examine that statement looking for some special technique for handling binary
data, you'll be disappointed, because the
$data
variable that contains the image isn't
treated as special in any way. The statement refers to all column values uniformly us‐
ing
?
placeholder characters and the values are passed at the end of the
do()
call. Another
way to accomplish the same result is to perform escape processing on the column values
explicitly, then insert them directly into the statement string:
$image_name
=
$dbh
->
quote
(
$image_name
);
$mime_type
=
$dbh
->
quote
(
$mime_type
);
$data
=
$dbh
->
quote
(
$data
);
$dbh
->
do
(
"REPLACE INTO image (name,type,data)
VALUES($image_name,$mime_type,$data)"
);
Image-handling has a reputation for being a lot more troublesome than it really is. If
you properly handle image data in a statement by using placeholders or by encoding it,
you'll have no problems. If you don't, you'll get errors. It's as simple as that. This is
no
different from how you should handle other kinds of data, even text. After all, if you
insert into a statement a piece of text that contains quotes or other special characters
without escaping them, the statement will blow up in your face. So the need for place‐
holders or encoding is not some special thing that's necessary only for images—it's
necessary for all data. Say it with me: “I will always use placeholders or encode my
column values. Always.” (Actually, if you know enough about a given value—for ex‐
ample, that it's an integer—there are times when you can break this rule. Nevertheless,
it's never wrong to follow the rule.)
To try the script, change location into the
apache/images
directory of the
recipes
distribution. That directory contains the
store_image.pl
script, and some sample images
are in its
flags
subdirectory (they're pictures of national flags for several countries). To
store one of these images, run the script like this under Unix:
%
./store_image.pl flags/iceland.jpg image/jpeg
Or like this under Windows:
C:\>
store_image.pl flags\iceland.jpg image/jpeg
store_image.pl
takes care of image storage, and the next section discusses how to retrieve
images to serve them over the Web. What about deleting images? I'll leave it to you to
write a utility to remove images that you no longer want. If you are storing images in
