Re: DBI quoting when I don't want it to

I don't know which version of SQL you are using, but I see two problems...

1) you are using an INSERT statement with a WHERE clause. Are you sure you don't want UPDATE?

UPDATE table SET thisdate='2002-12-25' WHERE id=1;

2) Single quotes can cause problems since they can be used to quote literals. Try this:

if ( ($DATE =~ /month/) || ($DATE =~ /day/) ) {
  $statement = "DATE(NOW()+ INTERVAL $DATE)";
   } else {
   $statement = "$DATE";  #assumes YYYY-MM-DD
}
[download]

Then using your DBI method,

INSERT into table (thisdate) VALUES ("$statement") WHERE id=1
[download]

Comment on Re: DBI quoting when I don't want it to Select or Download Code

Replies are listed 'Best First'.
Re: Re: DBI quoting when I don't want it to by Tardis (Pilgrim) on Aug 22, 2002 at 01:56 UTC
Yep sorry my bad. That was a mistake. The code actually does the right thing (INSERT for new objects, UPDATE for existing). Since the SQL is automatically generated (and since DBI doesn't let you see the actual SQL that goes through to the DB (unless you want an awful lot of debugging information as well), I just typed them in off the top of my head. The top of my head needs a bit of work :-) Anyway, the SQL statements generated are syntacticly correct, except when a quoted date comes into the arena :-) I can't use the suggestion you offer unfortunately, because all SQL is generated on the fly, based on configuration data. Without looking at each component of each INSERT/UPDATE, I don't know which ones I have to treat 'specially', and which ones I can use placeholders for.	[reply]

Replies are listed 'Best First'.

Re: Re: DBI quoting when I don't want it to
by Tardis (Pilgrim) on Aug 22, 2002 at 01:56 UTC

The code actually does the right thing (INSERT for new objects, UPDATE for existing). Since the SQL is automatically generated (and since DBI doesn't let you see the actual SQL that goes through to the DB (unless you want an awful lot of debugging information as well), I just typed them in off the top of my head.

The top of my head needs a bit of work :-)

Anyway, the SQL statements generated are syntacticly correct, except when a quoted date comes into the arena :-)

I can't use the suggestion you offer unfortunately, because all SQL is generated on the fly, based on configuration data.

Without looking at each component of each INSERT/UPDATE, I don't know which ones I have to treat 'specially', and which ones I can use placeholders for.

[reply]