AW: how to allow variable number of argumets in mysql insert quer y

> -----Ursprüngliche Nachricht-----
> Von: fungazid [mailto:fungazid@yahoo.com]
> Gesendet: Dienstag, 18. Dezember 2007 16:57
> Iâ€=99m using DBD::mysql, where Insert query looks like:
>=20
> ____________________________________________________________ __=20
> my $str=3D â€=9C?,?,?,,,â€=9D;=20
> $q=3D$dbh->prepare("INSERT INTO $table VALUES($str)")=20
> or die "Couldn't prepare statement: " . $dbh->errstr;=20
>=20
> $q->execute($args1[0],$args1[1],$args1[2],â€=A6)=20
> or die "Couldn't execute statement: " . $q->errstr;=20
> ____________________________________________________________ ____=20
>=20
> THE PROBLEM:=20
> If I want to change the number of arguments sent to=20
> execute(), I must change
> the source code.=20
> I would like to be able to change it on run-time with something like: =

____________________________________________________________ _=20
> $q->execute(f(@args1))=20
> or die "Couldn't execute statement: " . $q->errstr;=20
> ____________________________________________________________ _=20
>=20
> where f() formats the input to the function, and @args1 is=20
> allowed to have a
> variable size.=20
> This may add invaluable flexibility to my program, but how to=20
> do it ????.
The number of arguments in the execute() call must match the number of
placeholders (question marks) in the prepare().
So, either you leave the prepare() as it is (assuming you can =
predetermine
the maximum number of placeholders you will ever need). You then =
collect the
known arguments into @args1 and add as many undefs at the end as you =
need in
order to match the number of placeholders.=20
Or, you drop the whole prepare() step and you build up a complete SQL
statement (like=20
"insert into $table values(" . join(',', @args1) . ')'
and do() that. Depending on the content of your @args1, you may need to
quote the arguments.
Of course, the latter approach might open some serious security =
problems
with SQL injection, depending on the nature of your application, so =
you'd
better make quite sure that your @args1 contains nothing poisonous.

\Gisbert

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: how to allow variable number of argumets in mysql insert quer y

On Dec 18, 2007 1:06 PM, Selke, Gisbert W. w=
rote:
> > -----Ursprüngliche Nachricht-----
> > Von: fungazid [mailto:fungazid@yahoo.com]
> > Gesendet: Dienstag, 18. Dezember 2007 16:57
> > I'm using DBD::mysql, where Insert query looks like:
> >
> > ____________________________________________________________ __
> > my $str=3D "?,?,?,,,";
> > $q=3D$dbh->prepare("INSERT INTO $table VALUES($str)")
> > or die "Couldn't prepare statement: " . $dbh->errstr;
> >
> > $q->execute($args1[0],$args1[1],$args1[2],=85)
> > or die "Couldn't execute statement: " . $q->errstr;
> > ____________________________________________________________ ____
> >
> > THE PROBLEM:
> > If I want to change the number of arguments sent to
> > execute(), I must change
> > the source code.
> > I would like to be able to change it on run-time with something like:
> ____________________________________________________________ _
> > $q->execute(f(@args1))
> > or die "Couldn't execute statement: " . $q->errstr;
> > ____________________________________________________________ _
> >
> > where f() formats the input to the function, and @args1 is
> > allowed to have a
> > variable size.
> > This may add invaluable flexibility to my program, but how to
> > do it ????.
> The number of arguments in the execute() call must match the number of
> placeholders (question marks) in the prepare().
> So, either you leave the prepare() as it is (assuming you can predetermin=
e
> the maximum number of placeholders you will ever need). You then collect =
the
> known arguments into @args1 and add as many undefs at the end as you need=
in
> order to match the number of placeholders.
> Or, you drop the whole prepare() step and you build up a complete SQL
> statement (like
> "insert into $table values(" . join(',', @args1) . ')'
> and do() that. Depending on the content of your @args1, you may need to
> quote the arguments.
> Of course, the latter approach might open some serious security problems
> with SQL injection, depending on the nature of your application, so you'd
> better make quite sure that your @args1 contains nothing poisonous.

You could also use this idiom:

$dbh->do($sql, \%attrs, @args)

For example,

$dbh->do("select foo from bar where bar in(" . join(',', map { '?' }
@args) . ")",
{}, @args);

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org