techniques for proper quoting?

Greetings!

I'm using DBI w/ DBD::Pg, and am in the midst of attempting to simplify
some existing code. I happened on the following older perl.com article:
http://www.perl.com/pub/a/2001/03/dbiokay.html which provides what
seems to be a nicely concise way of constructing INSERT statements:

@fields = qw( country firstname lastname );

$fields = join(', ', @fields);

$values = join(', ', map { $dbh->quote($_) } @formdata{@fields});

$sql = "INSERT INTO foo ($fields) VALUES ($values)";


.... which works great - so long as the data types of the columns defined
in the table each have the same quoting requirements.

But what if, for example, there were a couple more columns added to
the above example - say, a couple 'integer', 'numeric' and/or 'boolean'
types? What would be an elegant/simple solution? How would I first
detect/determine the datatype of the field as defined in the schema, and
then properly quote it in the map?


Many thanks!

RE: techniques for proper quoting?

Corey wrote:
> Greetings!
>=20
> I'm using DBI w/ DBD::Pg, and am in the midst of attempting to
> simplify some existing code. I happened on the following older
> perl.com article: http://www.perl.com/pub/a/2001/03/dbiokay.html
> which provides what=20
> seems to be a nicely concise way of constructing INSERT statements:
>=20
> @fields =3D qw( country firstname lastname );
>=20
> $fields =3D join(', ', @fields);
>=20
> $values =3D join(', ', map { $dbh->quote($_) }
> @formdata{@fields});=20

Blech! Quoting values this way is generally a bad idea. Google "DBI
placeholders" for reasons why. One good article is
http://www.stupidfool.org/perl/docs/perltut/dbi/dbiplace.htm l.

> $sql =3D "INSERT INTO foo ($fields) VALUES ($values)";
>=20
>=20
> ... which works great - so long as the data types of the columns
> defined in the table each have the same quoting requirements.
>=20
> But what if, for example, there were a couple more columns added to
> the above example - say, a couple 'integer', 'numeric' and/or
> 'boolean' types? What would be an elegant/simple solution? How would
> I first detect/determine the datatype of the field as defined in the
> schema, and then properly quote it in the map?

You should use placeholders. Here's how:

@fields =3D qw( country firstname lastname );

$sql =3D "INSERT INTO FOO ("
. join(",", @fields)
. ") VALUES ("
. join(",", ("?") x @fields) # a ? for each field
. ")";

$dbh->do($sql, undef, @fields);

Philip

RE: techniques for proper quoting?

Corey [mailto:corey@bitworthy.net] wrote:


> Greetings!
>
> I'm using DBI w/ DBD::Pg, and am in the midst of attempting to simplify
> some existing code. I happened on the following older perl.com article:
> http://www.perl.com/pub/a/2001/03/dbiokay.html which provides what
> seems to be a nicely concise way of constructing INSERT statements:
>
> @fields = qw( country firstname lastname );
>
> $fields = join(', ', @fields);
>
> $values = join(', ', map { $dbh->quote($_) } @formdata{@fields});
>
> $sql = "INSERT INTO foo ($fields) VALUES ($values)";
>
>
> ... which works great - so long as the data types of the columns defined
> in the table each have the same quoting requirements.
>
> But what if, for example, there were a couple more columns added to
> the above example - say, a couple 'integer', 'numeric' and/or 'boolean'
> types? What would be an elegant/simple solution? How would I first
> detect/determine the datatype of the field as defined in the schema, and
> then properly quote it in the map?

You shouldn't need to worry about that. The quoting requirements depend on
the value, rather than on the datatype of the column. quote() will quote
the value so that it can be passed safely to the database, and the database
will convert it to the appropriate datatype if necessary.

For example, if you have a character field, and you want to insert a value
of 7, quote() will simply return "7" (rather than "'7'"). The database will
get the number 7, and will convert it to a character string before storing
it.

If you want to specify the actual datatype for the value, you can use
placeholders and bind_param() instead. See the DBD::Pg docs for examples.

Ronald

RE: techniques for proper quoting?

Garrett, Philip (MAN-Corporate) wrote:
> Corey wrote:
>> Greetings!
>>=20
>> I'm using DBI w/ DBD::Pg, and am in the midst of attempting to
>> simplify some existing code. I happened on the following older
>> perl.com article: http://www.perl.com/pub/a/2001/03/dbiokay.html
>> which provides what seems to be a nicely concise way of constructing
>> INSERT statements:=20
>>=20
>> @fields =3D qw( country firstname lastname );
>>=20
>> $fields =3D join(', ', @fields);
>>=20
>> $values =3D join(', ', map { $dbh->quote($_) }
>> @formdata{@fields});
>=20
> You should use placeholders. Here's how:
>=20
> @fields =3D qw( country firstname lastname );
>=20
> $sql =3D "INSERT INTO FOO ("
> . join(",", @fields)
> . ") VALUES ("
> . join(",", ("?") x @fields) # a ? for each field
> . ")";
>=20
> $dbh->do($sql, undef, @fields);

Doh! That should be:

$dbh->do($sql, undef, @formdata{@fields});

Regards,
Philip

Re: techniques for proper quoting?

On Tuesday 08 August 2006 09:08, Ronald J Kimball wrote:
> Corey [mailto:corey@bitworthy.net] wrote:

> > But what if, for example, there were a couple more columns added to
> > the above example - say, a couple 'integer', 'numeric' and/or 'boolean'
> > types?

> You shouldn't need to worry about that. The quoting requirements depend on
> the value, rather than on the datatype of the column.
>

Aha...

assuming:

CREATE TABLE foo (
id serial NOT NULL,
firsname character varying,
lastname character varying,
age integer
);

@fields = qw( firstname lastname age );

If 'age' in %formdata happened to be blank/missing, I saw that I was actually
using an empty string ( '' ) rather than undef, which would cause the map to
construct the following statement:
INSERT INTO foo ( firstname, lastname, age ) VALUES ( 'john', 'doe', '' )

which resulted in the following error:
DBD::Pg::st execute failed: ERROR: invalid input syntax for type numeric: ""

.... which is what led me to the false assumption that I wasn't quoting correctly.
( I actually had myself believing that integers couldn't be inserted with
surrounding quotes... heh ).

So thank you for your response, which caused me to look again at what was
actually happening.

The following "quick fix" solved the issue:

$values = join( ', ', map { $self->{'_vcc_dbh'}->quote( $_ || undef ) } @$data{ @fields } );

....which, in the case of missing 'age' values, will do:
INSERT INTO foo ( firstname, lastname, age ) VALUES ( 'john', 'doe', NULL )

bingo.


However:

On Tuesday 08 August 2006 09:07, Garrett, Philip (MAN-Corporate) wrote:
> Blech! Quoting values this way is generally a bad idea.
> You should use placeholders.
>

I will also take Philip's advice, and use placeholders - thanks for the heads up!


Many thanks to both of you!

Corey

Re: techniques for proper quoting?

On Tuesday 08 August 2006 10:02, Corey wrote:

> The following "quick fix" solved the issue:
>
> $values = join( ', ', map { $self->{'_vcc_dbh'}->quote( $_ || undef ) } @$data{ @fields } );
>


Whoops, should have been:

$values = join( ', ', map { $dbh->quote( $_ || undef ) } @$data{ @fields } );

Re: techniques for proper quoting?

On Tuesday 08 August 2006 09:07, Garrett, Philip (MAN-Corporate) wrote:
> You should use placeholders. Here's how:
>
> @fields = qw( country firstname lastname );
>
> $sql = "INSERT INTO FOO ("
> . join(",", @fields)
> . ") VALUES ("
> . join(",", ("?") x @fields) # a ? for each field
> . ")";
>
> $dbh->do($sql, undef, @formdata{@fields});
>

Before I run into a pitfall, I thought I'd ask before trying the above in my
code: will the placeholders work correctly for functions as well?

For instance, what if along with a bunch of integer and character data
stuffed into my %formdata, I also have a function, say, localtimestamp()?

Thanks!

Re: techniques for proper quoting?

On Thursday 10 August 2006 13:13, Corey wrote:
> On Tuesday 08 August 2006 09:07, Garrett, Philip (MAN-Corporate) wrote:
> > You should use placeholders. Here's how:
> >
> > @fields = qw( country firstname lastname );
> >
> > $sql = "INSERT INTO FOO ("
> > . join(",", @fields)
> > . ") VALUES ("
> > . join(",", ("?") x @fields) # a ? for each field
> > . ")";
> >
> > $dbh->do($sql, undef, @formdata{@fields});
> >
>
> Before I run into a pitfall, I thought I'd ask before trying the above in my
> code: will the placeholders work correctly for functions as well?
>
> For instance, what if along with a bunch of integer and character data
> stuffed into my %formdata, I also have a function, say, localtimestamp()?
>


Well, I ditched my False Laziness, and went ahead and tried this - and found
that it doesn't seem to work correctly for functions.

For instance, if localtimestamp is stored in my %formdata, then the string
'localtimestamp' is inserted into the db rather than the actual timestamp
resulting from the function.

The DBI perldoc says:

"Using placeholders and @bind_values with the "do" method can be useful
because it avoids the need to correctly quote any variables in the $statement."

....which appears to not be the case when functions are involved?

Using the other ( "non-bound" ) technique mentioned in this thread, I came up
with this:

sub db_insert {

my $self = shift;
my $table = shift;
my $data = shift;
my $funcs = shift;

my ( @fields, $fields, $values, @funcs, $st, $sth, $id );

push @fields, $_
for keys %{ $data };

$fields = join( ', ', @fields );

$values = join( ', ', map { $self->{'_vcc_dbh'}->quote( $_ ) } @$data{ @fields } );

# deal with functions... lame solution?
if ( $funcs ) {

push @funcs, $_
for keys %{ $funcs };

$fields .= ', ' . join( ', ', @funcs );

# don't quote functions
$values .= ', ' . join( ', ', map { $_ } @$funcs{ @funcs } );

}

$st = qq{ INSERT INTO "$table" ( $fields ) VALUES ( $values ) };

$sth = $self->{'_dbh'}->prepare( $st );

$sth->execute()
or croak $sth->errstr;

$id = $self->{'_dbh'}->last_insert_id(
undef, undef, $table, undef
);

return $id;

}

Then I'd call it with something like:

$self->db_insert( 'Foo', { 'blah' => $blah }, { 'time_stamp' => 'locatimestamp' } );

Which works - but that whole special-case situation for functions seems... inelegant.

RE: techniques for proper quoting?

Corey [mailto:corey@bitworthy.net] wrote:
>
> On Tuesday 08 August 2006 09:07, Garrett, Philip (MAN-Corporate) wrote:
> > You should use placeholders. Here's how:
> >
> > @fields = qw( country firstname lastname );
> >
> > $sql = "INSERT INTO FOO ("
> > . join(",", @fields)
> > . ") VALUES ("
> > . join(",", ("?") x @fields) # a ? for each field
> > . ")";
> >
> > $dbh->do($sql, undef, @formdata{@fields});
> >
>
> Before I run into a pitfall, I thought I'd ask before trying the above in
> my
> code: will the placeholders work correctly for functions as well?
>
> For instance, what if along with a bunch of integer and character data
> stuffed into my %formdata, I also have a function, say, localtimestamp()?

You can only use placeholders for constant values, not for object or
function names.

If you want to use the function as a default, you can do something like this
(this is an Oracle example):

$dbh->do(<<"EndOfSQL", 7, undef);
INSERT INTO foo
(id, date)
VALUES
(?, NVL(?, SysDate))
EndOfSQL

Ronald