ColdFusion + SQL help requested

[Saint Nick]

I'm looking for some help using ColdFusion. I am working on a form where a user can input a month and year through a drop-down box. I then want that to be passed into a query (<cfquery>) that will then be presented on the page.

I am very new to ColdFusion and our company uses it almost exclusively. The form method is POST. Here is what I have so far.

				<!---MONTH--->
				  <select name="beginYear" size="1">
				  	<option value=""></option>
					<option value="2000">2000</option>
					<option value="2001">2001</option>
					<option value="2002">2002</option>
					<option value="2003">2003</option>
					<option value="2004">2004</option>
					<option value="2005">2005</option>
					<option value="2006">2006</option>
					<option value="2007">2007</option>
					<option value="2008">2008</option>
					<option value="2009">2009</option>
					<option value="2010">2010</option>
				  </select> 
				  to 
				<!---MONTH--->
				  <select name="endMonth" size="1">
				  	<cfif #form.endMonth# EQ ''><option selected="selected" value=""></option></cfif>
					<cfif #form.endMonth# EQ '1'><option selected="selected" value="1">1</option></cfif>
					<cfif #form.endMonth# EQ '2'><option selected="selected" value="2">2</option></cfif>
					<cfif #form.endMonth# EQ '3'><option selected="selected" value="3">3</option></cfif>
					<cfif #form.endMonth# EQ '4'><option selected="selected" value="4">4</option></cfif>
					<cfif #form.endMonth# EQ '5'><option selected="selected" value="5">5</option></cfif>
					<cfif #form.endMonth# EQ '6'><option selected="selected" value="6">6</option></cfif>
					<cfif #form.endMonth# EQ '7'><option selected="selected" value="7">7</option></cfif>
					<cfif #form.endMonth# EQ '8'><option selected="selected" value="8">8</option></cfif>
					<cfif #form.endMonth# EQ '9'><option selected="selected" value="9">9</option></cfif>
					<cfif #form.endMonth# EQ '10'><option selected="selected" value="10">10</option></cfif>
					<cfif #form.endMonth# EQ '11'><option selected="selected" value="11">11</option></cfif>
					<cfif #form.endMonth# EQ '12'><option selected="selected" value="12">12</option></cfif>
				  </select>
				<!---MONTH--->
				  <select name="endYear" size="1">
				  	<option value=""></option>
					<option value="2000">2000</option>
					<option value="2001">2001</option>
					<option value="2002">2002</option>
					<option value="2003">2003</option>
					<option value="2004">2004</option>
					<option value="2005">2005</option>
					<option value="2006">2006</option>
					<option value="2007">2007</option>
					<option value="2008">2008</option>
					<option value="2009">2009</option>
					<option value="2010">2010</option>
				  </select> 
				 </td>
				</td>

Then, this is what I have inside of a <cfquery> tag:

			<cfif isDefined("form.beginMonth") AND isDefined("form.endMonth") AND isDefined("form.beginYear") AND isDefinied("form.endYear")>
				a.AssignedDate >=
			</cfif>

But, I'm not real sure how to put that into the query. You'll see a.AssignedDate in there, but I just don't know what the syntax is. I've tried looking online to no avail.

Thanks!

 

[Cogman]

Give this a read over
http://www.adobe.com/livedocs/coldfusion/6.1/htmldocs/tags-b20.htm

And be careful. This is EXACTLY the place where SQL injections will take place.

 

[Brian4321]

Here is a snippet from a calendar routine in one of my apps. Hope it helps:

<cfquery datasource="#DSN#" name="GetHolidays">
SELECT *
FROM Holidays
WHERE Holiday_Date = #TheDate#
ORDER BY Holiday_Name ASC
</cfquery>

 

[Cogman]

Here is a snippet from a calendar routine in one of my apps. Hope it helps:

<cfquery datasource="#DSN#" name="GetHolidays">
SELECT *
FROM Holidays
WHERE Holiday_Date = #TheDate#
ORDER BY Holiday_Name ASC
</cfquery>

Here is a good example of a SQL injection vulnerability. What happens if #TheDate# looks like this

0; Delete from Holidays; --

Ideally, if the user is entering a parameter, you should use the cfqueryparam tag rather then just throwing the data in there (even if you do your own checking.)

 

[InverseOfNeo]

http://xkcd.com/327/

 

[Markbnj]

Haha, I love that one. "Little Bobby Tables, we call him."

 

[Cogman]

http://xkcd.com/327/

while you should still sanitize inputs. Most SQL interfaces feature a some sort of binding mechanism. That should be used whenever possible for user inputs as it allows the user to put in something like "'Who is there' asked mary; she was unsure of the assailants identity." without messing things up. Plus, it can be faster.