I am trying to dynamically build an SQL query based on what my user selects on a web form. I'm using the following approach:
<CFSET strSQL = "Select attribute_a, attribute_b, from table_a where">
<CFIF user wants student_id info>
<CFSET strSQL = strSQL & "student_id = '#student_ID#'">
<CFIF user wants something else -- (here's where my problem is)>
<CFSET strSQL = strSQL & "student_major = '" & #user's_selection_from_form# & "'">
I've seen this done plenty of times in CF and ASP, but for some reason I'm getting an SQL syntax error where my #user's_selection_from_form# is referenced. The CF error page that is returned displays the output of my query string as sent to SQL (MS-SQL 2000, btw) with the student major correctly displayed, but it appears to be inside two sets of single quotes. I know SQL wants those items to be enclosed in single quotes, so as you can see I entered them and closed the string before referencing the form variable, but it's not working. Am I missing a required trick for escaping the quotes?