Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Re: DBI, SQL and Dates

by physgreg (Scribe)
on Aug 07, 2002 at 08:29 UTC ( #188257=note: print w/replies, xml ) Need Help??


in reply to DBI, SQL and Dates

If you want the single quotes, then surround the string with double quotes:
$dbhstmt->execute( "'$SuStCo_strID'", "'$date'" );
I suggest this, as it's a good idea to have quotes around anything going into SQL, to stop nasty input tricks.

Replies are listed 'Best First'.
Re: Re: DBI, SQL and Dates
by dws (Chancellor) on Aug 07, 2002 at 08:36 UTC
    $dbhstmt->execute( "'$SuStCo_strID'", "'$date'" ); I suggest this, as it's a good idea to have quotes around anything going into SQL, to stop nasty input tricks.

    Bad advice, unless you want to end up with extra quote characters around fields in the database. I've only seen people do that by accident.

    Better to pass strings to execute() either unquoted, or surrounded by double quotes. The latter is needed if you're trying to "pass" on object that overides stringify, and you want to store the stringified representation.

Re: Re: DBI, SQL and Dates
by aufrank (Pilgrim) on Aug 07, 2002 at 15:27 UTC

    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://188257]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2022-08-13 09:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?