Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

DBI quoting when I don't want it to

by Tardis (Pilgrim)
on Aug 21, 2002 at 13:10 UTC ( #191719=perlquestion: print w/ replies, xml ) Need Help??
Tardis has asked for the wisdom of the Perl Monks concerning the following question:

OK, I suspect I may be out of luck with this one.

I'm converting a web-based app to SQL (The backend is currently a DBM file. A big one).

I have arranged (for minimum pain) for my SQL tables to be automatically created based on a simple config file, which represents the current data structures. So far so good.

I had this bright idea that instead of users just being able to type in '25/12/2002' for dates, they could also type in '+1 month' or '-3 weeks'. What a great idea (I thought).

I duly wrote the logic which would convert +1 month to DATE(NOW() + INTERVAL '+1 month').

It doesn't work, and I bet you're all laughing at me right now.

(Pause to let laughter subside)

So if a user types '25/12/2002', then DBI does something like:

INSERT INTO table (thisdate) VALUES('2002-12-25') WHERE id = 1;

(My function has converted it to YYYY-MM-DD format).

In the interval style one, DBI does:

INSERT INTO table (thisdate) VALUES ('DATE(NOW() + INTERVAL \'+1 month\')') WHERE id =1

Bzzzzzt!

Unfortunately, since all this SQL code is automatically generated on the fly, it's difficult to 'spot' the date fields, and do them via variable interpolation, rather than via placeholder.

You can see why I think I'm probably out of luck :-)

Anyone see an elegant way around this? Like 'DBI, don't autoquote if it looks like this, otherwise do'. :-|

Otherwise, I have to do some sort of lookup for each element as I build the SQL statement.

<sigh>

Comment on DBI quoting when I don't want it to
Re: DBI quoting when I don't want it to
by JupiterCrash (Monk) on Aug 21, 2002 at 13:36 UTC
    How about just always binding that value as a parameter? So your SQL would look like:

    INSERT INTO table (thisdate) VALUES (?) WHERE id =1

    Then store either a yyyy-mm-dd date string or the format which calls the function in a variable, say $theDate.

    Then, always just:

    $sth->bind_param(1, $theDate);

    ... and you don't have to worry about whether to quote or not.

      Does this actually work? I'd be mildly surprised if it did, since you're asking the database to pull commands out of what is being bound as a string (or precisely, SQL_VARCHAR). This sort of thing is better done at the application level, since it alone knows what type of value is being inserted.

      Chris
      M-x auto-bs-mode

Re: DBI quoting when I don't want it to
by Mr. Muskrat (Abbot) on Aug 21, 2002 at 13:49 UTC
    Good call JupiterCrash. And I'd try using (NOW() + INTERVAL 1 month) instead of DATE(NOW() + INTERVAL '+1 month').
      Not sure about PostGres or Oracle, but using MySQL you can leave out the '+' sign and replace with a comma:
      (NOW(), INTERVAL 1 MONTH)
      
      I'd also look at the DATE_FORMAT which is a handy little tool:
      DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-%d')
      
        
        Postgres won't allow
        
         ... interval 1 month ...
        
        Instead, it must be
        
         ... interval '1 month' ...
        
        -- 
        Jeff Boes                            vox 616.226.9550 ext 24
        Database Engineer                           fax 616.349.9076
        Nexcerpt, Inc.                       http://www.nexcerpt.com
                ...Nexcerpt...Connecting People With Expertise
        
Re: DBI quoting when I don't want it to
by perrin (Chancellor) on Aug 21, 2002 at 15:23 UTC
    Placeholders are for literal values, not just randomly inserting whatever you want. In many databases the substitution is done on the server side, and is limited to specific data types. You can't insert a chunk of SQL code like you're trying to.

    Just build the SQL dynamically and ditch placeholders if you need to do this.

Re: DBI quoting when I don't want it to
by waswas-fng (Curate) on Aug 21, 2002 at 15:27 UTC
    Cant you use Date::Manip to do it cgi side and not even worry about the SQL? I may be missing the point here =/

    -Waswas
Re: DBI quoting when I don't want it to
by seattlejohn (Deacon) on Aug 21, 2002 at 15:30 UTC
    Maybe using Date::Manip would be an option. You could calculate the desired date in your Perl code, convert it to a simple DD/MM/YYYY date, and use that date in your SQL statement.
Re: DBI quoting when I don't want it to
by bart (Canon) on Aug 21, 2002 at 15:41 UTC
    So if a user types '25/12/2002', then DBI does something like:

    INSERT INTO table (thisdate) VALUES('2002-12-25') WHERE id = 1;

    (My function has converted it to YYYY-MM-DD format).

    In the interval style one, DBI does:

    INSERT INTO table (thisdate) VALUES ('DATE(NOW() + INTERVAL \'+1 month\')') WHERE id =1

    You don't show any code. I don't see how and what you're doing.

    But anyway: you could try to make DBI not to quote the date field, ever, and produce your own quotes when you need them. $dbh->quote($string) can do that for you.

    Personally, I'd take another approach, and convert the user input to a valid date in plain Perl, instead of letting the DB do it. One of the Date::* modules surely must be able to do it for you. Otherwise, it's not too hard, or too big, to write it yourself. See, for a working code example, the calculation for cookie expiration dates in the sub 'expires_calc' in CGI::Util (which comes with CGI.pm). And since you're reformatting the date anyway, I think it must be pretty easy to fit that little bit of extra functionality in.

Re: DBI quoting when I don't want it to
by shambright (Beadle) on Aug 21, 2002 at 17:21 UTC
    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 }
    Then using your DBI method,
    INSERT into table (thisdate) VALUES ("$statement") WHERE id=1

      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.

Re: DBI quoting when I don't want it to
by Mur (Pilgrim) on Aug 21, 2002 at 18:54 UTC
    Here's some code for a Postgres DB that may help:
    my $db; # local connection used to retrieve time $db = ... however you choose to connect a handle =item timestamp($timestring [, $future_flag]) =cut sub timestamp { my($time,$future) = @_; local($db->{RaiseError}) = 0; local($db->{PrintError}) = 0; # Timestamp could be absolute. my $val = scalar $db->selectrow_array(q!SELECT EXTRACT('EPOCH' FROM TIMEST +AMP ?)!, undef, $time); if (defined $val) { $db->commit; return $val; } # Well, it's not, so let's try it again as a delta from today. $db->rollback; $val = scalar $db->selectrow_array(q! SELECT EXTRACT('EPOCH' FROM (TIMESTAMP 'now' + INTERVAL ?))!, { }, $time); return $val unless !defined($val) or (($val >= time) and !$future) +; # Hmm, that gave us nothing, or a time in the future, and the call +er # didn't want that. Try one more time, using a subtraction on the # delta. $db->rollback; $val = scalar $db->selectrow_array(q! SELECT EXTRACT('EPOCH' FROM (TIMESTAMP 'now' - INTERVAL ?))!, { }, $time); return $val; }
    The only disadvantage of this approach that I've seen, is that error messages are logged in the postmaster log file whenever you pass through one of the try-again cases.
    -- 
    Jeff Boes                            vox 269.226.9550 ext 24
    Database Engineer                           fax 269.349.9076
    Nexcerpt, Inc.                       http://www.nexcerpt.com
            ...Nexcerpt...Connecting People With Expertise
    

    Edit kudra, 2002-08-22 s/pre/code/

Re: DBI quoting when I don't want it to
by Tardis (Pilgrim) on Aug 22, 2002 at 02:11 UTC
    Thanks for everybodys comments.

    To give you some background, which may make things a bit clearer, here's what's going on.

    The system as it currently stands has a DBM 'backend'. The user interface to each 'object' is basically a CGI object.

    You call the param method on the object to set/get data, and a 'save' method saves the data away for this object into the DBM file.

    It's actually rather elegant, has scaled beyong all belief (around 120000 objects, in a 17Gb DBM file!), it's quick, well indexed (searches are mostly VERY quick) and it 'just works'.

    The current system means that is you want an object to have some new fields, just call param, and they get saved away. It's very free-form in that respect.

    However it's stopped scaling because of locking. The frontend to this is a web-based application. Since each write needs exclusive access to the single DBM file, you can imagine this causes grief.

    So enter SQL. I have the task of making a bunch of pretty-much freeform CGI objects fit into an SQL model.

    The only thing I know is that each object will (should!) have a 'Module' attribute. This tells me what sort of object it is.

    Basically Module equates to the table name, but I made it smarter than that. For instance, there are other common fields, so I share them in a single table (will make searches much more sane).

    Actually I'm quite proud of the configuration scheme I've defined to map the CGI module/attributes to SQL table/column. So far it's working.

    (BTW, there is a whole world of pain with making the SQL also cope with the fact that some of the CGI attributes might have list values - this feature is used in the current system extensively).

    So hope this makes more sense now. Basically, when I 'save' a CGI object:

    • Decide if it's a new or existing object
    • For each attrib/value pair, map to an SQL table/column
    • For each table/colum we need to update/insert, do a single update/insert
    It's all fun :-)

    So I think at the end of the day, I will have to bite the bullet and make perl do the date manipulation, if I want to include this 'date' function.

    I didn't want to because:

    • SQL can do it, it's already there and it knows how to do date calculations very well
    • I'm trying to speed this system up, so I'm leary of adding code I don't strictly speaking need.
    Another road I've considered since reading all these posts, is to (at conversion time) do a simple SELECT, just to convert the date.

    That's not a bad option, some SQL overhead, plus I need to pass my $dbh handle around a bit more. But doable.

Re: DBI quoting when I don't want it to
by mattr (Curate) on Aug 22, 2002 at 05:49 UTC
    Here is some code from a very old version of a CGI/Mysql timesheet system I wrote. I used both Date::Manip and SQL date calculation commands. Maybe it will help.

    The program basically lets you record how many hours a day you spend on any of your clients, in a view which covers all clients for a 1 week period. Due to user requests, a function was later added which let them log in to past weeks (to make up for unrecorded hours) by selecting a pulldown menu at login time which had items such as "2 weeks ago". So I think your idea was completely valid.

    I can tell you that Date::Manip is great, but is also one of the two or three biggest modules on CPAN, if memory matters. As for quoting, maybe DATE_ADD will solve it for you?

    Some Date::Manip (mainly used for day of the week type stuff): $companystartdate = &ParseDate("today"); # returns 2000082415:40:44 +format $companystartdate =~ s/^(....)(..).+/$1-$2-01/; # build 2000-08-01 Some SQL: my $statement = "select * from charges where (UserId='$userhash{'UserId'}' AND (Date BETWEEN '$start' AND DATE_ADD('$start', interval 6 day) ) ) order by ClientGroupId,ClientId,Date"; and my $monthstart = substr($sqlstartdate,0,8) . "01"; # 2000-08-01 my $monthend = "DATE_SUB( ( DATE_ADD('$monthstart',interval 1 month) ), Interval 1 day)"; # 1mo less 1dy. too many parens? my $statement="select sum(Hours) from charges where (UserId='$userhash{'UserId'}' and ClientId='$client' and FeeType='$fee' and Date between '$monthstart' and $monthend ) group by UserId";

    By the way regarding list values, why the "world of pain"? Are you creating columns all over the place? You probably know it, but CGI.pm can freeze into a string joined by nulls. Not that DBI might like it any.

    Hope this helps.

      I can tell you that Date::Manip is great, but is also one of the two or three biggest modules on CPAN, if memory matters. As for quoting, maybe DATE_ADD will solve it for you?
      It's the outer quotes that hurt, not so much the inner ones. I simply can't do this unless I stop using placeholders. So I need DBI to do:

      INSERT INTO table (thisdate) VALUES (DATE(NOW() - INTERVAL '+1 month'));

      Rather than:

      INSERT INTO table (thisdate) VALUES ('DATE(NOW() - INTERVAL '+1 month')');

      At least I think that's what you are hinting at being the problem that DATE_ADD might fix.

      Not to mention that (AFAIK) DATE_ADD is a MySQL-specific function (I use Pg).

      By the way regarding list values, why the "world of pain"?
      Because before they came into the picture, the world was a simpler place. I assign arbitary attributes to arbitary table columns. The unique id for each row was the object id.

      Suddenly this doesn't work for list values.

      My approach has been to break any list values into a seperate table of it's own, with a non-unique key.

      When you consider that in a CGI object, anything can have a list value, you can see my pain. In my new backend world I'll be putting my foot down (hard) and deciding which ones can be lists and which ones can't.

      Migration is going to be ..... fun

        Ah. Tres harsh. I can see your pain.

        Override dbh->quote() mentioned in DBI::DBD.pm pod? Stop using placeholders? (eek)

Re: DBI quoting when I don't want it to
by Aristotle (Chancellor) on Aug 22, 2002 at 12:53 UTC
    I don't quite see the problem. The code that converts +1 month into SQL knows that it is producing SQL at that point, so you can set it up to either produce SQL or produce a quoted date string. What am I missing? You need to post some actual code for your real problem to become obvious.

    Makeshifts last the longest.

      I don't quite see the problem. The code that converts +1 month into SQL knows that it is producing SQL at that point, so you can set it up to either produce SQL or produce a quoted date string. What am I missing? You need to post some actual code for your real problem to become obvious.
      Hard to post code that will explain the problem, as the SQL is all generated dynamically with a bunch of crufty addons to a slice of nasty legacy code :-)

      The key is that I'm using placeholders, and when I pass something like 'DATE(NOW() + INTERVAL '1 month')' via a placeholder, it gets quoted as a string, and it doesn't work.

      I beleive that's the bit you're missing :-)

      As others have mentioned, I can either not use placeholders (bad, IMHO) or do the date maniplulations in perl rather than in SQL. A third option is to do an extra 'SELECT' of my own to deduce the actual modified date.

      I've decided to hold off on it for a while. It wasn't a key feature, and I mainly wanted it because (I thought) implementation would be easy and the details could be left to SQL. If the user typed in a b0rked date interval, it would just raise an SQL exception.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://191719]
Approved by Mr. Muskrat
Front-paged by kudra
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (5)
As of 2014-08-02 04:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Who would be the most fun to work for?















    Results (54 votes), past polls