ColdFusion/mySQL - quotation marks and apostrophes

Question

I'm having an issue when inserting data into a mySQL database using a ColdFusion application. I've tried REPLACE and PRESERVESINGLEQUOTES and a few other things, but I'm lost at this point.

It's basically wherever I have a textbox or textfield in a form - users aren't allowed to use quotation marks in the fields (an error gets sent back - You have an error in your SQL syntax;) and when an apostrophe is used it gets doubled (a word like Mark's gets turned into Mark''s)

Any help would be appreciated. My head is about to burst.

---

SOLUTION: cfqueryparam

Answer 1

You should be using cfqueryparam Something like this will work

<cfset userEnteredData = "I'm using apostrophes">

<cfquery>
INSERT INTO data (userText)
VALUES (<cfqueryparam cfsqltype="cf_sql_varchar" value="#userEnteredData#">)
</cfquery>

EDIT

Do this for three reasons:

1. This creates a "bind variable", which (among other things) protects against SQL Injection attacks.
2. CFQUERYPARAM automatically escapes quotes and apostrophes.
3. If you're passing a list of variables, it will correctly escape the list based on the cfsqltype if you use the list attribute.