1

Trying to match received data from the form with one in the db. So if I dump form variable and the table I can see there is a match but coldfusion gives me this...

Column 'Kirill' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'Kirill' is not a column in the target table.

The query:

SELECT FIRST_NAME, PASSWORD 
from APP.USERS_TASK
where FIRST_NAME = "#form.username#"
  and PASSWORD = "#form.password#"

Also the same thing works just fine with id which been passed via url. With the different table though.

jarlh
  • 42,561
  • 8
  • 45
  • 63
Kirill Pakhomov
  • 87
  • 1
  • 6
  • 2
    Perhaps you need single quotes instead of double quotes, e.g. `... FIRST_NAME = '#form.username#' ...`? (SQL injection possible?) – jarlh Aug 02 '16 at 13:44
  • 2
    Nothing to do with the question, but [passwords should be hashed](http://stackoverflow.com/questions/1054022/best-way-to-store-password-in-database/1054033#1054033). – Leigh Aug 02 '16 at 17:01
  • It's actually illegal to store passwords in plaintext depending on what you're doing. Leigh is right. – TRose Aug 02 '16 at 18:14
  • lol, i'm just learning so no commercial projects so far. thx a lot btw. i'll do that. – Kirill Pakhomov Aug 02 '16 at 18:22

1 Answers1

6

SQL requires strings to be in single quotes, not double.

SELECT FIRST_NAME, PASSWORD 
FROM APP.USERS_TASK
WHERE FIRST_NAME = '#form.username#'
AND PASSWORD = '#form.password#'

But really, you should be using cfqueryparam to sanitize your user inputs and prevent SQL injection. This would also take care of any data typing and required quotes for you.

SELECT FIRST_NAME, PASSWORD 
FROM APP.USERS_TASK
WHERE FIRST_NAME = <cfqueryparam value="#form.username#" cfsqltype="cf_sql_varchar">
AND PASSWORD = <cfqueryparam value="#form.password#" cfsqltype="cf_sql_varchar">
Joe C
  • 3,506
  • 2
  • 21
  • 32
  • Oh, didnt know quotes matters. When using cfparam is it necessary to provide type info? – Kirill Pakhomov Aug 02 '16 at 13:57
  • 2
    the `cfsqltype` attribute is not required, but it is recommended as it protects your database and makes sure that values are converted to string or numbers properly for you. – Joe C Aug 02 '16 at 14:12
  • It's also great for converting dates/times as necessary. Those are always a pain to get right. – Joe C Aug 02 '16 at 14:28
  • 1
    *cfsqltype .. is not required, but it is recommended* Ditto. Always use the appropriate cfsqltype for the db column. Otherwise, the db has to guess ... and often guesses wrong, leading to inefficient queries - or worse - wrong query results. – Leigh Aug 02 '16 at 17:07