Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Help with searching within range of days

by htmanning (Pilgrim)
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 the fire pops...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2018-05-27 18:37 GMT
Find Nodes?
    Voting Booth?