1

When doing a cfquery to MySQL, MYSQL gives me the error Unknown column 'Question' in 'field list' on the following code:

<cfquery name="qUpdateTheQuestion" datasource="testmaster">
INSERT INTO ETrueFalseQuestions
VALUES (<cfoutput>#newTypeID#</cfoutput>, `<cfoutput>#FORM.Question#</cfoutput>`, <cfoutput>#FORM.truefalse#</cfoutput>

I have tried everything I could think of to try to solve this issue, as well as some suggestions I found when trying to search for a solution, such as using backticks instead of single quotes.

I also don't understand why it is searching for a column with the name Question, as that is the value of FORM.Question, and changes depending on what is typed into the form. Any suggestions would be greatly appreciated.

Sean Walsh
  • 8,266
  • 3
  • 30
  • 38
Computerman1597
  • 214
  • 3
  • 10
  • 2
    Please wrap your variables in `cfqueryparam`: ``, ``, `` – Sean Walsh Mar 26 '12 at 13:49
  • Re: *why it is searching for a column with the name Question* `FORM.Question` contains a string. Literal strings must be enclosed in single quotes (or wrapped in cfqueryparam), otherwise your database assumes it is an object name ie Column or table name. Also, you never need `cfoutput` tags within a cfquery. – Leigh Mar 26 '12 at 15:52
  • 1
    Used the cfqueryparam, but is there some advantage to this over just using the variables? – Computerman1597 Mar 26 '12 at 16:30
  • `cfqueryparam` gives these advantages: protection against SQL-injection attacks _and_ using bind variables which allows the database to re-use compiled queries and, thus, improve performance. – ale Mar 26 '12 at 16:47
  • The ``s you're using are also unnecessary inside a `cfquery` tag. – ale Mar 26 '12 at 16:47
  • So, is it a security issue to select all of the fields, including the password field, when logging in a user? Is it somehow possible to get this. – Computerman1597 Mar 27 '12 at 02:10

1 Answers1

7

that query makes no sense to me

normally it should look like

INSERT INTO ETrueFalseQuestions (IdColumnName, questionColumnName, questionTrueFalse)     
VALUES ( #newTypeID#, '#form.Question#', #form.truefalse# )

So you need to

  1. list the columns
  2. not use <cfoutput> within a <cfquery>
  3. put single quotes around string values or use <cfqueryparam>

PS: the column names I used are made up you need to fix them to match yours.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Dale Fraser
  • 4,623
  • 7
  • 39
  • 76