Re: [FWP] TMTOWTDI: SQL creation

[Michael G Schwern <schwern@pobox.com>]

On Tue, Jun 29, 1999 at 09:02:03AM -0500, Andy Lester wrote:
> I'm writing some code that happens to need to build some SQL on the fly,
> based on some field names and values.  I originally wrote it like:
> 
>         my $sql =
>             "INSERT INTO " . $parms{$table} .
>             "(" . join( ",", @fieldnames ) . ")" .
>             "VALUES ( " . join( ",", @values ) . ")";
> 
> But I think that's so ugly, and so hard to follow, so I rewrote like:
> 
>         my $sql =
>             sprintf( "INSERT INTO %s ( %s ) VALUES ( %s )",
>                 $parms{$table},
>                 join( ",", @fieldnames ),
>                 join( ",", @values );
> 
> Sure, sprintf() may be Old Skool, but in this case, I think it makes
> things far clearer.

I wrote this in some code I just recently wrote to commit changes to
an object to the database:

# save the header changes
my $sql = <<"";
UPDATE 	$TABLES{ForumRef}
SET	${\(join ', ', map {"$_ = ?"} keys %{$self->{Changed}})}
WHERE	Name = ?	AND
	IsActive = 1

unless($self->db_Arena_Admin->do($sql,undef,
			   	 @{$self}{keys %{$self->{Changed}}},
			   	 $self->{Name})) {
	...
}


A few cavets in this whole issue of dynamically generated SQL:

	- Use bind parameters!  

	  This saves you against quoting problems and rogue SQL
	  statements.  For example:
	  
	  $bar = fromTheOutsideWorld;
	  my $sql = "DELETE FROM $TABLE WHERE Foo = $bar";

	  What if $bar is '"foo" OR Foo IS NOT NULL OR Foo IS NULL'.

	  Ooops, your whole table is gone.

	  Bind parameters would save you from that by quoting the contents
	  of $bar:
	
	  my $sql = "DELETE FROM $TABLE WHERE Foo = ?";
	  $dbh->do($sql, undef, $bar);

	- Turn on DBI's taint mode!
	  
	  As of DBI 1.09 (It was introduced earlier, but it had bugs),
	  values fetched via DBI are tainted, and DBI will
	  not accept tainted data IF you specify this as an attribute of
	  the connection.  USE THIS FEATURE!  Reading/writing to a database
	  is just as much a security risk as any other file I/O.

	  $dbh = DBI->connect($data_source, $user, $password, {Taint=>1});

	- Try Ima::DBI

          It automates alot of this garbage for you and encourages use of
	  bind parameters.  It also caches connections and handles.

-- 

Michael G Schwern                                           schwern@pobox.com
                    http://www.pobox.com/~schwern
     /(?:(?:(1)[.-]?)?\(?(\d{3})\)?[.-]?)?(\d{3})[.-]?(\d{4})(x\d+)?/i

==== Want to unsubscribe from Fun With Perl?
==== Well, if you insist... Send mail with body "unsubscribe" to
==== fwp-request@technofile.org