Help with searching within range of days

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
[download]

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:

/search.pl?searchtextterms=&andor=AND&search=yes&state=(TO_DAYS(datead
+ded)-TO_DAYS(Now())%3E=-30)&currentpage=2
[download]

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.

Comment on Help with searching within range of days Select or Download Code

Replies are listed 'Best First'.
Re: Help with searching within range of days by davido (Cardinal) on Aug 08, 2012 at 01:29 UTC
...so 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". Dave	[reply] [d/l]
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.	[reply]
Re: Help with searching within range of days by locked_user 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 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.	[reply]