insertでのエラーについて

質問

お世話になります。
アップロードしたバイナリデータを変数に入れて、それをinsertしているのですが、DBに入りません。
テーブルでは、data mediumblob not nullと設定しています。Perlスクリプト内で、次のように書いています。
$sql="insert into data_tbl(data)";
$sql.="values('" . $datafile . "')";
上記の$datafileにバイナリデータが入っています。

このようにして実行しているのですが、You have an error in your SQL syntax. とエラーになりDBに格納できません。ご教授よろしくお願いします。

回答No.11

root139

> DBI,DBDのバージョンは調べ方がわからなく、申し訳ありませんが直ぐお答えできません。調べてみます。すみません。

Active Perlでしたら、PPMを使ってモジュールをインストールしていると思いますが、queryコマンドでインストールされているバージョンが分かります。

C:\>ppm
PPM> query DBI
DBI [x.xx] Database independent interface for Perl
PPM> query DBD-mysql
DBD-mysql [x.xx] A MySQL driver for the Perl5 Database Interface (DBI)
PPM> quit

また、コマンドプロンプトで下記の様なコマンドを発行しても確認できると思います。

C:\>perl -e "use DBI; print $DBI::VERSION"
C:\>perl -e "use DBD::mysql; print $DBD::mysql::VERSION"

> 今回MySQLにバイナリデータの格納を考えておりましたが、そうするのではなく
> そのバイナリファイルのファイル名をMySQLに格納するというものです。
> ローカルサーバにアップロードしたバイナリファイルを、selectで取得したファイル名と
> 関連付けて取り出したりするというのは、どうでしょうか。実装するうえであまりにも
> 前者に劣る点などあるのでしょうか。root139様ならお勧めなさらない方法でしょうか。

ケースバイケースだと思います。
Webシステムで、PDFなどのバイナリーデータをDBに格納する場合は、下記の様な特徴が考えられます。

・取り扱うデータがDBで一元的に管理できる。
例えば、データを削除するときもDBのレコードを消去するだけで良く、
また、複数のWebサーバを使って負荷分散させる場合なども、DBだけを共有すれば良い。

・データを扱うときに、CPUの負荷・メモリの使用量が大きくなる。
例えば、PDFや画像の場合、表示させる度に、DBからデータの取出しやメモリ(変数)への格納、出力処理などが必要になります。

ファイルで保存するある場合は、これと反対のメリット・デメリットがあると考えて下さい。
編集・削除の際には、DBのレコードとバイナリーファイルの整合性を保つように処理しなければなりません。
また、複数のWebサーバを使って負荷分散させる場合などは、DB以外にファイルの共有も考えなければなりません。
ただし、PDFなどを表示させる場合は、対象のファイルへのリンクを表示するだけでよい場合も多いです。

バイナリーデータは、えてして大容量になる場合も多いので、負荷的にファイルに格納せざるを得ないケースも多いと思います。

お礼

root139様、ご回答どうもありがとうございます。

>、queryコマンドでインストールされているバージョンが分かります

　お陰様でわかりました。ありがとうございます。
DBD-mysql [2.9003] MySQL driver for the Perl5 Database Interface (DBI)
DBI [1.46] Database independent interface for Perl
でした。

>ケースバイケースだと思います・・・・・
　どうもありがとうございます。わかりやすく説明してくださり、とても参考になりました。メリット・デメリットは裏返しなのですね。どちらにしようかちょっと迷っておりまして、もう少し考えてみようと思います。

回答No.10

root139

こちらの環境(下記)では、特殊文字をエスケープしても、プレースホルダを利用しても、バイナリーデータの格納は出来るようです。
OS: WindowsXP
Perl: ver. 5.6.1
MySQL: ver. 3.23.54
DBI: ver. 1.37
DBD-mysql: ver. 2.9002

他のバイナリーデータをアップロードしてもエラーになりますでしょうか？
また、Perl, DBI, DBD::mysql, mysqlのバージョンは、どのようになっていますでしょうか？

base64エンコードしての格納など、他の方法を検討された方が良いかも知れません。

お礼

root139様、お忙しいところどうもありがとうございます。わざわざroot139様の環境で試していただきまして本当にありがとうございます。
OS: WindowsXP
ActivePerl:v5.8.4
MySQL:version 4.0.16-nt
DBI,DBDのバージョンは調べ方がわからなく、申し訳ありませんが直ぐお答えできません。調べてみます。すみません。

＞他の方法を検討された方が良いかも知れません。
　ありがとうございます。
　このことについてなのですが、方向が少しずれてし　まうのですが、アドバイス頂けませんでしょうか。
　今回MySQLにバイナリデータの格納を考えておりましたが、そうするのではなくそのバイナリファイルのファイル名をMySQLに格納するというものです。ローカルサーバにアップロードしたバイナリファイを、selectで取得したファイル名と関連付けて取り出したりするというのは、どうでしょうか。実装するうえであまりにも前者に劣る点などあるのでしょうか。root139様ならお勧めなさらない方法でしょうか。よろしくお願いいたします。

回答No.9

root139

おそらく、プレースホルダと値をバインドする際に、バイナリー以外のものとして処理されているのだと思います。
バインドする際にバイナリーデータである事を明示してやらなければいけないのかも知れません。

下記の様に修正したらいかがでしょう。

use DBI;
　↓
use DBI qw(:sql_types);

$sth->execute($img);
　↓
$sth->bind_param(1, $img, SQL_LONGVARBINARY);
$sth->execute();

参考URL：

http://homepage3.nifty.com/hippo2000/perltips/dbimemo.htm

お礼

root139様、このような時間までありがとうございます。
-----------------------------------------------
use DBI qw(:sql_types);
　：
　：
$sql = "insert into pdf2_tbl(pdf) values(?)";

$sth=$db->prepare($sql);
$sth->bind_param(1, $img, SQL_LONGVARBINARY);
$sth->execute();

$sth->finish;
$db->disconnect;
----------------------
という形で書きました。
create table pdf2_tbl(
ID int unsigned not null auto_increment,
pdf mediumblob not null,
primary key(ID)
);
としています。
これで実行してみましたところ、
mysql> select * from pdf2_tbl;
Empty set (0.02 sec)
となり、バイナリデータを格納することができませんでした。アパッチのlogには、
DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(ホ3Eホ g §鈆+頷ソfハgBセ`Et飫薬覦ネ:┏7¶K(9H5疚カ胼、Eｗe(ィhr at c:\・・\・・・\cgi-bin\UPLOAD~4.PL line 58.
とエラーがでて、58行目は$sth->execute();です。
まだ未熟なためこのように何度も申し訳ないのですが、もう少しご教授頂けますようお願いいたします。

回答No.8

root139

> mediumblobという型は、数値しか格納できないからエラーになるのでしょうか？
～～BLOB はバイナリデータ用の型ですので、数値しか格納できないということはありません。
http://www.mysql.gr.jp/Manual/mysql-3.23.47/manual.ja_Reference.html#BLOB
http://e-words.jp/w/BLOB.html

> 数値部分だけ格納するということはできるのでしょうか。その場合、
> 取り出したときにブラウザに表示させて元の画像やPDFのかたちで見たいのですが、
> やはり数値だけのデータからでは不備になるのでしょうか。

もちろん、mediumblob型の項目に数値を格納することはできますが、今回のケースのエラーが解消するかどうかは分かりません。
また、元のデータの一部分でしかないので、そこから元のデータを復元するのは無理だと思います。

エラーメッセージでは、SQLにバイナリらしき部分があり、そこで、Syntax(文法)に間違いがあると出ていますが、SQL文はどうなっていますでしょうか？

また、下記の様に、挿入するデータをバイナリではなくテキストにした場合はどうでしょうか？
$sth->execute($img);
↓
$sth->execute("123");

お礼

root139様、お忙しいところ回答してくださり、本当にありがとうございます。
質問させていただいた内容は理解できました。ご丁寧にありがとうございます。
>SQL文はどうなっていますでしょうか？
　$sql = "insert into pdf_tbl(pdf) values(?)";
　$sth=$db->prepare($sql);
　if(!$sth->execute($img)){
　print "insert失敗\n";
　exit;
　}
と、実際のソースでは書いています。
テーブルでは、
　create table pdf4_tbl(
　　ID int unsigned not null auto_increment,
pdf mediumblob not null);
としております。
このようなソースで行うと、「insert失敗」となり、if(!$sth->execute($img)){の行でSyntaxエラーになります。データベースにも勿論値は入っておりません。

また、　
$sth->execute($img);
↓
$sth->execute("123"); 　　　　　
にしてみましたところ、「insert失敗」と表示されなく、データベースにも『123』とpdfフィールドに入っておりました。
テーブル作成で型をmediumblobにしていない、ということがあってはいけないと思いまして、新しくテーブルを作り、再度mediumblobと確認しまして行いましたが、結果同じでした。
度々申し訳ありませんが、ご教授下さいますようお願いいたします。

補足

すみません、下のテーブルで
create table pdf4_tbl(
は
create table pdf_tbl(
書き間違いました。

回答No.7

root139

mysqlのドキュメントでは、特殊文字をエスケープするように書いてありましたが、DBIのドキュメントでは、バイナリーデータを扱うときは、プレースホルダという機能を使用するように書いてありました。
http://homepage3.nifty.com/hippo2000/perltips/dbimemo.htm#handling%20blob%20/%20long%20/%20memo%20fields

具体的には、SQLの中に「?」を記述して、execute時に「?」の分の引数を渡してやれば良いです。
http://homepage3.nifty.com/hippo2000/perltips/dbimemo.htm#item_execute

例) --------------------------------------------------------
$sql = "insert into binary_tbl(binary_data) values(?)";
$sth = $db->prepare($sql);
$sth->execute($img);
------------------------------------------------------------

この方法ではどうでしょうか？

お礼

root139様、ご回答本当にありがとうございます。

上記の方法で試してみたのですが、
DBD::mysql::st execute failed: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(ホ3Eホ g §鈆+頷ソfハgBセ`Et飫薬覦ネ:┏7¶K(9H5疚カ胼、Eｗe(ィhr at c:\www\PUBLIC~1\cgi-bin\upload.pl line 87.
というように、$sth->execute($img);の行でエラーが
出てしまいます。
現在、サイズの大きくないPDFをアップロードして試しているのですが、print $img;で表示させると
%PDF-1.4 %&acirc;&atilde;&Iuml;&Oacute; 7 0 obj << /Type/Encoding /Differences[33/exclam/quotedblright/numbersign/dollar/percent/ampersand/quoteright/parenleft/parenright/asterisk/plus/comma/hyphen/period/slash/zero/one/two/three/four/five/six/seven/eight/nine/colon/・・・
という感じで表示されます。何度も申し訳ありませんが、SQLがエラーになる理由として他に何か考えられるのでしょうか。すみませんがよろしくお願いいたします。

補足

mediumblobという型は、数値しか格納できないからエラーになるのでしょうか？
先ほど下記にて、表示させた$imgには/Encoding /Differences[33/exclam/quotedblright/numbersign・・・x&iquest;TWW÷&ocirc;&pound;&ordf;&euml;9 といった文字が含まれています。その間、間に388.9 388.9 500 777.8 277・・・という数値も入っているのですが、数値部分だけ格納するということはできるのでしょうか。その場合、取り出したときにブラウザに表示させて元の画像やPDFのかたちで見たいのですが、やはり数値だけのデータからでは不備になるのでしょうか。

回答No.6

root139

エスケープ処理は、SQL文の生成時に行い、#5でご紹介したページに書いてある通り、DBIパッケージのquoteメソッドを使用するのが良いかと。
http://www.mysql.gr.jp/Manual/mysql-3.23.47/manual.ja_Reference.html#IDX950
http://homepage3.nifty.com/hippo2000/perltips/dbimemo.htm#item_quote

例) -------------------------------------------------
$sql = "insert into binary_tbl(binary_data) values(" . $db->quote($img) . ")";

お礼

root139様、どうもありがとうございます。
下に書かせて頂いた
$sql="insert into binary_tbl(binary_data)";
$sql.=" values('" . $img . "');
を
$sql = "insert into binary_tbl(binary_data) values(" . $db->quote($img) . ")";
と書き換えて実行してみたのですが、「insert失敗」となってしまいます。
エラーは$sth=$db->prepare($sql);の行で起こっています。何かアドバイスいただけると助かります。
よろしくお願い致します。

補足

教えていただいたURLを見ても、やはりSQL文には問題なく思うのですが、どうしてかYou have an error in your SQL syntax. となってしまいます。$imgにちゃんとバイナリデータが入っていないと、このようなSQL syntaxでのエラーとなってしまうのでしょうか？
DBに挿入する以前のスクリプトで問題があるのでしょうか。Perlのカテゴリにて、その部分（DB挿入以前のスクリプト）について質問いたしたいと思います。

回答No.5

root139

直接バイナリデータを入れるためには、null値、\(バックスラッシュ)、'(シングルクウォート)、"(ダブルクウォート)をエスケープする必要が有るようです。
http://www.mysql.gr.jp/Manual/mysql-3.23.47/manual.ja_Reference.html#IDX950

また、対象のデータをbase64エンコードして格納しても良いと思います。
http://www.sea-bird.org/doc/Cygwin/BASE64enc.html
http://www.din.or.jp/~ohzaki/perl.htm#JP_Base64
ただし、取出す際にデコードする手間が増えますが。

お礼

root139様、回答どうもありがとうございます。
ご教授いただいた最初の方法で行いたいのですが、どの段階でエスケープする作業を行えばいいのでしょうか。エスケープするのは置換などを用いて行うのでしょうか。(←的外れなことを言ってるかもしれません）
今作っているのが、アップロードしたバイナリファイルを変数($img)に格納してDBに挿入するというものです。
#!C:\Perl
use CGI;
use DBI;
$PCMOJPATH='C:\パス名';
my ($buffer);
my $query = new CGI;

$uniqid = time . "_" . $$;
$newfile = "upload_$uniqid";

print "Content-type: text/html\n\n";
print "<html><body>\n";

my $fH = $query->upload('filename');
my $mimetype = $query->uploadInfo($fH)->{'Content-Type'};

open (OUT, ">$PCMOJPATH/$newfile");
binmode (OUT);
while(read($fH, $buffer, 1024)){
print OUT $buffer;
$img .= $buffer;
}
close (OUT);
close ($fH) if ($CGI::OS ne 'UNIX');
chmod (0666, "$PCMOJPATH/$newfile");

my $db=DBI->connect("DBI:mysql:DB_name:localhost","user_name","password",{RaiseError => 0,PrintError => 1});

if(!$db){
print "失敗\n";
exit;
}

$sql="insert into binary_tbl(binary_data)";
$sql.=" values('" . $img . "');
$sth=$db->prepare($sql);
if(!$sth->execute){
print "insert失敗\n";
exit;
}
$sth->finish;
$db->disconnect;
----------------------
このプログラムのどこにエスケープする処理を行えばよいのでしょうか。よろしければご教授よろしくお願いたします。

回答No.4

moon_night

改めて考えてみるとバイナリを入れたことがなかった（汗）

通常文字はinsertされるので、バイナリ形式での入れ方（変数への代入の仕方など）の問題でしょう。
どうやらバイナリファイルは " や ' などをエスケープしないといけない様子。
で、あとbinmode(STDIN)とかを駆使しないといけないような・・・
詳しくは分からなかったので以下URL参照してください。
すいません。

http://www.mysql.gr.jp/mysqlml/mysql/msg/2031

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=554630,http://oshiete1.goo.ne.jp/kotaeru.php3?q=1143091

お礼

moon_night様、何度もありがとうございます。
入れ方がまずいのですね。

>バイナリファイルは " や ' などをエスケープしないといけない様子
　これは、DBに入れる前にそのバイナリファイルの中　に含まれる" や 'をエスケープするとういうことで　しょうか。すみません、まだどうやってするのかイ　メージがわきません。
示していただいたURLありがとうございます。参照いたします。

回答No.3

moon_night

残念ながら、＃２に書かれている構文を試した結果、私の環境ではエラーは起きませんでした・・・

考えられる問題として、
・テーブル名が間違っている
・入力するデータ形式が違う
・データが空だ

などなど、たぶんSQLの構文が間違っているような気がします。
SQLの構文が正しいかどうか見直してみてください。
（insertではなく、selectして見るとか）
あと、英語のエラーメッセージを読みましょうね。

参考URL：

http://www.excite.co.jp/world/english/

お礼

moon_night様、お返事が遅くなり申し訳ありません。
その後ずっと色々と新しくテーブルを作り直してみたり等して試みているのですが、解決できていません。
質問で書いた、バイナリデータを格納している変数には、次のようなものが入っています。(←ブラウザに表示させて確認)
%PDF-1.4 %粤マモ 7 0 obj << /Type/FontDescriptor /CapHeight 709 /Ascent 723 /Descent -241 /FontBBox[-170 -331 1024 903] /FontName/Ryumin-Light /ItalicAngle 0 /StemV 69 /Flags 6 /Style<< /Panose(\001\005\002\002\003\000\000\000\000\000\000\000) >> >> endobj 8 0 obj << /Type/Font /Subtype/CIDFontType0 /BaseFont/Ryumin-Light /FontDescriptor 7 0 R /CIDSystemInfo<< /Registry(Adobe) /Ordering(Japan1) /Supplement 2 >> /DW 1000 >> endobj 9 0 obj (省略)
これは、mediumblobというデータ形式に合わないのでしょうか？SQLの挿入では、この変数の値を入れるようにしています。よろしくお願いいたします。
　参考URL大変役立っております。ありがとうございました。

回答No.2

moon_night

UPLOAD~.PL の line 57 らへんでエラーが起きているようです。

お礼

moon_night様、ありがとうございます。
line 57 辺りは次のようになっているのですが、
どの部分でエラーになっているのか分かりません。
何度もすみませんが、見ていただけないでしょうか？
お願いします。
$sql="insert into data_tbl(data)"; #54行目
$sql.=" values('" . $datafile . "')"; #55行目
$sth=$db->prepare($sql); #56行目　　
if(!$sth->execute){ #57行目
print "insert失敗\n"; #58行目
exit; #59行目
}