PERL Beginners - Escaping large chunk of text for insertion into mysql

Author

Escaping large chunk of text for insertion into mysql

Kevin Old

2006-02-01, 7:55 am

Hello everyone,

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 =3D $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:

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 '(''Married: Arlene. Children: Amy
(10/14/72), Twins Rachel and Heather (10/31/7' at line 1 at ./nm4.pl
line 181.

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?

Thanks for any help,
Kevin
--
Kevin Old
kevinold@gmail.com

John Doe

2006-02-01, 7:55 am

Kevin Old am Mittwoch, 1. Februar 2006 13.44:
> Hello everyone,
>
> 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:
>
> 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 '(''Married: Arlene. Children: Amy
> (10/14/72), Twins Rachel and Heather (10/31/7' at line 1 at ./nm4.pl
> line 181.

The first single quote in the error msg is from the msg itself. The error
occurs at the '('. So, your mysql syntax is wrong, independently from the
inserted value. Just thest the statement at the mysal cmdline.

Then, when using placeholders, quote() is not necessary; it's done
automagically.

I did not test it, but try the following (also note the missing field name
'personal_characteristics') - it is assumed that the table only consists of
one field.

$dbh->prepare( 'insert into nascar_media values (?)' );
$sth->execute( $vals[13] );

Check
man DBI
http://dev.mysql.com/

hth,
joe

Paul Lalli

2006-02-01, 7:55 am

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

Sponsored Links