MySQL cfqueryparam single quote problem

[Chris Gottshall]

I have an application with a user login screen. Only users with apostrophes in their names are unable to log in. My database is MySQL 3.23.52 and I'm using the most recent update of CFMX.  If I query for a certain record using phpMyAdmin, I see the escape sequence \' replaces all single quotes. So, I have tried manually replacing all single quotes with slash-quote. And I have tried both with and without cfqueryparam tags.  Here are four scenerios that I have tested out, and the outcomes of each.


1. No escape character with single quote; using cfqueryparam - query does not fail, but no record is returned


2. Using escape character with single quote; and using cfqueryparam - query does not fail, but no record is returned


3. Using escape character with single quote; no cfqueryparam - passing it straight in between single quotes - query fails to execute due to the value passed in for the name


4. No escape character with single quote; no cfqueryparam  (passing it '#strName#') - query executes and the correct record is returned


However, I do not feel comfortable trusting just any user data in the last name field. I want to use cfqueryparam to prevent potential sql injections. Here is part of my query that fails:


SELECT FIRSTNAME, LASTNAME
FROM T_USER
WHERE UPPER(T_USER.LASTNAME) = <cfqueryparam cfsqltype="cf_sql_varchar" value="#UCase(queryLastName)#">


But, this works:
SELECT FIRSTNAME, LASTNAME
FROM T_USER
WHERE UPPER(T_USER.LASTNAME) = '#UCase(queryLastName)#'


Any help is appreciated.  Thanks.


-Chris

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

---