Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re: Re: DBI, SQL and Dates

by aufrank (Pilgrim)
on Aug 07, 2002 at 15:27 UTC ( #188354=note: print w/replies, xml ) Need Help??


in reply to Re: DBI, SQL and Dates
in thread DBI, SQL and Dates

it is not a great idea to put quotes around everything going into an sql statement, but luckily DBI has methods built in to help you quote intelligently. Check the docs for $dbh->quote() and $dbh->quote_identifier. great tips for using these methods can be found in chromatic's DBI is OK. I find I often use this bit of code from the article ('cause hash slices are so cool):

my $table = 'uregisternew'; my @fields = qw( country firstname lastname userid password address1 c +ity state province zippostal email phone favorites remaddr gender inc +ome dob occupation age ); my $fields = join(', ', @fields); my $values = join(', ', map { $dbh->quote($_) } @formdata{@fields}); $sql = "INSERT into $table ($fields) values ($values)"; $sth = $dbh->prepare($sql); $sth->execute(); $sth->finish();

the one change I sometimes have to make is to the line my $fields = join(', ', @fields);. If you're using a DB that allows spaces in column names, you'll need to quote the column names using code like my $fields = join(', ', map {dbh->quote_identifier($_) }@fields);.

The take home message is, when in doubt use $dbh->quote() on any value you specify in your query, and use $dbh->quote_identifier() on any column or table name. Also, the docs recommend using the quote-like qq{} around sql statements containing variables prepared in this way, so that nothing gets misquoted. rewriting chromatic's example a little, I get a piece of code that looks like this:

my $table_name = $dbh->quote_identifier(uregisternew); my @fields = qw( country firstname lastname userid password address1 c +ity state province zippostal email phone favorites remaddr gender inc +ome dob occupation age ); my $fields = join(', ', map { $dbh->quote_identifier($_) } @fields); my $values = join(', ', map { $dbh->quote($_) } @formdata{@fields}); $sql = qq{INSERT into $table_name ($fields) values ($values)}; $sth = $dbh->prepare($sql); $sth->execute(); $sth->finish();

hth,
--au

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://188354]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (6)
As of 2022-12-02 09:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?