Bram Kuijper am Dienstag, 2. Januar 2007 18:07:
> Hi all,
Hello Bram Kuijper
> I try to execute the following SQL query in PostGreSQL 8.1. However, it
> gives errors since DBI::execute() automatically quotes when it inserts
> its arguments into the SQL query. Is there any way to change the quoting
> behavior of DBI::execute():
>
> my $qry = "ALTER TABLE mytable ALTER COLUMN ? TYPE double precision;
> my $qry_sth = $dbh->prepare($qry);
> $qry_sth->execute($col_name);
>
> which results in the following query being executed:
> ALTER TABLE mytable ALTER COLUMN 'column_name' TYPE double precision
>
> The inserted variable, which is an identifier, will receive single
> quotes by DBI::execute(). PostGreSQL gives an error, since it only
> accepts unquoted identifiers. How can I change DBI::prepare() or
> DBI::execute default behavior, so that added values go unquoted?
>
> I have the idea that this should be done by adding some statement
> handling attribute to the $qry_sth object, but the DBI documentation is
> not very explicit about it.
It states:
"Placeholders and Bind Values
With most drivers, placeholders can’t be used for any element of a statement
that would prevent the database server from validating the statement and
creating a query execution plan for it. For example:
"SELECT name, age FROM ?" # wrong (will probably fail)
"SELECT name, ? FROM people" # wrong (but may not ’fail’)
"
I think since the alter table operation is not likely to be executed many
times, and there's no execution plan to calculate, it's ok to simply:
# sanitize $column_name here to prevent sql injection
$dbh->do(
"ALTER TABLE mytable ALTER COLUMN $column_name TYPE double precision"
) or die $dbh->errstr;
# see the do() method in perldoc DBI
Dani
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/