Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Help with searching within range of days

by htmanning (Monk)
on Aug 07, 2012 at 22:06 UTC ( #986091=perlquestion: print w/replies, xml ) Need Help??
htmanning has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I'm querying a database trying to find records added within 30, 60, or 90 days. I have a form where the user picks the timeframe, and the query works:
SELECT * FROM database WHERE (TO_DAYS(dateadded)-TO_DAYS(Now())>=-30) + ORDER BY dateadded DESC, ID DESC
We're showing 20 results at a time, so I embed the query into a "NEXT" link but this code doesn't work in a string:
/ +ded)-TO_DAYS(Now())%3E=-30)&currentpage=2
I'm not sure how to get around this. The less than sign doesn't translate into the string. Any help would be appreciated. UPDATE: Never mind. I figured it out. Duh.

Replies are listed 'Best First'.
Re: Help with searching within range of days
by davido (Archbishop) on Aug 08, 2012 at 01:29 UTC I embed the query into a "NEXT" link...

    Am I understanding correctly that if I were to view-source on your page I would see within the NEXT link the SQL code that will be passed to the database? If that's the case, someone with a little motivation could construct a query that passes any SQL he wants back to the server. ...find out what tables exist, look up user account information, drop tables, and make a big mess.

    POST isn't the answer either. The answer is to treat any data that comes from the client like it's capable of transmitting ebola. And because your URL has a .pl in it, the headline will read "Hackers attack insecure Perl script".


Re: Help with searching within range of days
by sundialsvc4 (Abbot) on Aug 08, 2012 at 03:12 UTC

    Personally, I prefer to tackle problems like this one by availing myself fully of packages such as Date::Calc and Date::Calc::Object, instead of trying to write the necessary calculations in terms of the SQL language variant that I am presently dealing with.   I will perform the date calculations within my program, then provide those computed values as placeholders within the queries that I am to execute.   (Notice that I do not insert them into the SQL text.)

    Furthermore, one extremely important (possible...) consideration that you must bear in mind is:   time zones.   If the server is in Portland, Oregon USA and you are in Richmond, Virginia USA, the time-value that you compute locally is three hours “off.”

Re: Help with searching within range of days
by frozenwithjoy (Priest) on Aug 07, 2012 at 23:49 UTC
    Since you've figured it out, you may want to mention what the solution was in case someone has a similar problem in the future.
Re: Help with searching within range of days
by chacham (Prior) on Aug 07, 2012 at 23:58 UTC

    Allowing the information to be passed from the form will likely enable SQL injection.

    Using an SP would be more secure, especially as it can use a host variable and avoid dynamic SQL.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://986091]
Approved by chacham
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2017-09-21 13:18 GMT
Find Nodes?
    Voting Booth?
    During the recent solar eclipse, I:

    Results (247 votes). Check out past polls.