[ProdicalProgrammer]

Good Morning All

Basically what I am trying to do is pull is a report from two dates.

Here is my code.

The query seems to run fine, but it produces now results any suggestions?

<cfinclude template="header.cfm">
<cfparam name="Form.Submit" default="">
<cfparam name="name_display" default="0">

<cfif form.submit eq "Submit">
<cfquery name="get_min" datasource="#APPLICATION.DB#">
SELECT * FROM tblemail WHERE TODAYSDATE between #DATE1# and #DATE2#
</cfquery>
<cfset name_display = "1">
</cfif>

<cfoutput>
<table width="100%" align="center" cellpadding="0" cellspacing="0" border="0" bgcolor="##FFFFFF">
	<tr>
    	<td align="center"><h2>Date Reporting System</h2></td>
    </tr>
    <tr>
    	<td align="left">
        	<table width="500px" align="left" cellpadding="5" cellspacing="5" border="0" bgcolor="##FFFFFF">
            	<tr>
                	<td align="left" class="copyblack">Search by date:</td>
                </tr>
                <tr>
                    <cfform action="date_search.cfm" method="post">
                    <td class="copyblack">From:&nbsp;<cfinput type="text" name="DATE1" required="yes" message="Please enter your first date"></td>
                    <td class="copyblack">To:&nbsp;<cfinput type="text" name="DATE2" required="yes" message="Please enter your second date"></td>
                    <td style="padding-top:20px"><cfinput type="submit" name="Submit" value="Submit" class="submitbutton"></td>
                    </cfform>
                </tr>
            </table>
        </td>
    </tr>
    <cfif name_display eq 1>
    <tr>
       <td align="left">
       <br /><br />
    <table width="300px" align="left" cellpadding="0" cellspacing="0" border="0" bgcolor="##FFFFFF">
    <cfloop query="get_min">
    	<tr>
        	<td class="copyblack">#NAME#</td>
            <td class="copyblack">#SURNAME#</td>
            <td>#EMAIL#</td>
        </tr>
    </cfloop>
    </table>
    	</td>
    </tr>
    </cfif>
</table>
</cfoutput>
<cfinclude template="footer.cfm">

[Craig328]

Well, the truly applicable part of your code (or the part I'd concentrate on) is this part here:

<cfquery name="get_min" datasource="#APPLICATION.DB#">
SELECT * FROM tblemail WHERE TODAYSDATE between #DATE1# and #DATE2#
</cfquery>

You said that query seems to run fine but that it produces no results. Obviously, you're expecting results so while the query might not be erroring, it's not running all that fine.

So, that's actually a good place to start: it's not erroring. This means your SQL syntax with your database is okay so it's either that you don't have records that match your criteria (DATE1 and DATE2) or that the criteria themselves aren't compatible with the way the dates are being stored in your database. You didn't mention the database product you're using (MySQL, SQL Server, Oracle, etc) but that's actually not as important as knowing the datatype of the TODAYSDATE column in your tblemail table. As an example, if the value of DATE1 (which I'm assuming is a form variables...and we'll talk about that in a moment as well) is '1/1/12' and the TODAYSDATE is a MySQL timestamp column then the dates stores there will look like this: '2012-01-01'. The 'between' operator in MySQL does have some discretion but having the date formats that far apart is likely too far for it to bridge so it comes back saying "I got nuthin"...which is what you're seeing, right?

The way to fix that is to format the variable(s) you're passing into your SQL so that you're not giving the database an excuse to return a blank dataset. If the database is looking for a format like 'yyyy-mm-dd' then format your variable using DateFormat() and pass it in that way. In a perfect world, that would likely solve your problem.

However, the world isn't perfect and your example contains a couple of other potential issues you probably haven't encountered yet but can and some of them could be quite serious.

For one: how do you know you'll have values for DATE1 and DATE2. Your code seems to assume you'll have form variables, right? That said, in any programming language it's best not to assume anything and CF is no different. Because you don't check for the existence of DATE1 or DATE2 before passing them into your query, you could easily get a "variables doesn't exist" error.

Secondly: what kind of variable IS DATE1 and DATE2? Session? Form? Client? URL? Because you don't explicitly type the variable you're passing in to your SQL (and by association, to your database) you are relying on the CF engine to determine what scope variable goes into your query. It is possible there can be a form.date1 a session.date1 a cookie.date1 an application.date1 and a URL.date1 simultaneously. Do you know the assessment order CF uses to determine which to use? Most don't which is why it's exceptionally important to scope or type your variables when you refer to them. If you mean form.date1 then say form.date1.

Third: why is it important to scope your variables? Well, suppose I was a malicious dickhead trying to hack your site. Literally the very first place hackers go is to your forms. Each form has an action attribute (which is the page the form submits to for processing) either implied or explicitly stated. Since you're submitting to "date_search.cfm" for instance, there is nothing that prevents me from setting up a parallel page, creating a form and submitting to date_search.cfm?date1=blabla and NOT submitting a form field called date1. Because your code doesn't care what variable scope it send to your database, I could easily put the ASCII equivalent of "2011-01-01;DESCRIBE tblemail;" in there and because url.date1 exists and form.date1 does not, your query WILL use url.date1. If you're using a MySQL database that will tell me all about the tblemail table. Naturally I'd need to know the table names you have...but I could also run queries against INFORMATION_SCHEMA and with enough patience I could find just about everything about your database. That is what's known as a SQL injection attack because just as easily as your SQL faithfully passes along my DESCRIBE and INFORMATION_SCHEMA commands, it'll also pass along a GRANT command, a CREATE USER command, a DROP command and so on. Basically, I could take over or even destroy your database.

Luckily for CF devs we have a tag known as CFQUERYPARAM that helpfully shuts down SQL injection attacks. It creates what is known as a bind parameter and if you're going to run queries that use variables that come from users, it's absolutely essential that you use them. They prevent SQL injection attacks and if you use the optional CFSQLType attribute can even impart a slight performance boost to your queries (by telling the database the type of data is has incoming).

So, I know that was more than what you asked for but building secure forms is a critical learning step in CF development. Hope that gets you on your way. Post back here if you have more questions.

Good luck!

This post has been edited by Craig328: 14 May 2012 - 06:28 AM

[ProdicalProgrammer]

Hi Craig

Thank you for the response.
It was very useful and insightful.

<cfquery name="get_min" datasource="#APPLICATION.DB#">
SELECT * FROM tblemail WHERE TODAYSDATE between <cfqueryparam cfsqltype="cf_sql_date" value="#DATE1#"> and <cfqueryparam cfsqltype="cf_sql_date" value="#DATE2#">
</cfquery>

Is that a better way to write a query can my page still be hacked.

This post has been edited by Craig328: 15 May 2012 - 06:00 AM

[Craig328]

Not so much hacked as messed with now. The CFQUERYPARAM tags create bind parameters on your query data which will prevent most SQL injection attacks so your database is relatively safe.

That said, you can still experience some unusual behavior because you're not scoping the DATE1 or DATE2 variables. Since anything is going through the bind parameters now the potential for a database hack is pretty well eliminated (at least via this route) but you still have the potential confusion of sending a url.DATE1 or session.DATE1 or cookie.DATE1 along (amongst others) instead of your intended form.DATE1.

It wouldn't cause anything too extreme but it's always a good idea to explicitly identify your variable scopes so as to eliminate any confusion.