Re: Escaping large chunk of text for insertion into mysql
Kevin Old wrote:
> I have a large chunk of data that I need to insert into a text or blob
> field in mysql. I've tried all the usually escaping it, but nothing
> seems to work. I'm even using dbh->quote as I thought it might help.
>
> Here's my code:
>
> my $sth = $dbh->prepare("insert into nascar_media values(
> personal_characteristics ) (?)");
> $sth->execute( $dbh->quote($vals[13]) );
>
> Not that it really helps, here's the DBD::mysql error returned:
Actually, it's extremely helpful. It tells you exactly what's wrong.
> 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
Your INSERT syntax is wrong. It has nothing to do with quoting.
http://dev.mysql.com/doc/refman/4.1/en/insert.html
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
You have, for some reason, the word 'VALUES' before the column name,
instead of before the values.
Your SQL should be:
INSERT into nascar_media (personal_characteristics) VALUES (?)
> Is there something I'm not thinking of? This data contains all kinds
> of characters and could even have binary data in it (that's why I
> mentioned blob above). How can I escape them all so it'll go in?
You don't. That's why you use placeholders (the ? in the prepared
SQL). When you call execute() with the proper parameters, those
parameters are used for the actual values. The binding to the place
holder takes care of all escaping for you.
Paul Lalli
.
