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/


Reply via email to