magic_quotes_gpc setting for ColdFusion? ColdBox?

Question

I am new to ColdFusion and want to remove single quotes from the values of my input fields. I tried to search on google and what I found is to use "magic_quotes_gpc" or "mysql_real_escape_string" but those functions do not exist in ColdFusion. Is there any way to handle this kind of mysql query injection in ColdFusion?

Updated:

Thank you for reply but please look at my code

<div class="form-group">
    <label for="jobDesc">Job description</label>
    <textarea name="description" class="form-control" rows="3" id="jobDesc">
        <cfif isdefined('userTime')>#userTime.description#</cfif>        
   </textarea>
</div>

I just want to use single quotes in the text area and my form is submitting to event. The query is:

 sqlstr = "";
          sqlstr = "insert into usertime set
          userid = '#arguments.userTimeParams.userid#',
          projectid = '#arguments.userTimeParams.projectid#',
          timesheetdate = '#arguments.userTimeParams.timesheetdate#',
          estimatedtimespent = '#arguments.userTimeParams.jobhours * 60 + arguments.userTimeParams.jobMins#',
          description = '#arguments.userTimeParams.description#',
          timeentered = #arguments.userTimeParams.timeentered#;";

            queryObj = new query();
            queryObj.setDatasource("timesheet");
            queryObj.setName("adduserTime");
            result = queryObj.execute(sql=sqlstr);
            adduserTime = result.getResult();
            return result.getPrefix().generatedKey;

I have one option that I can add slashes to my string, but then I have to add slashes in all strings. So is there any function or way to do this with less lines of code?

Sorry for asking much with limited knowledge.

Use the [cfqueryparam](https://wikidocs.adobe.com/wiki/display/coldfusionen/cfqueryparam) tag (or script equivalent). Among other things, it will escape the quotes for you. — Miguel-F, Apr 03 '15 at 12:08
FYI, those features you mention were huge design mistakes that the PHP folks already fixed years ago. You need to use prepared statements, no matter the client language. — Álvaro González, Apr 03 '15 at 13:05
@ Alvaro, i have been through a lot links and i know they fixed in php but i din found any solution in cf till yet so i asked..anyway thanx for your PRECIOUS commant — Leo the lion, Apr 03 '15 at 13:32
Regarding, `wanted to remove single quotes from the values of my input fields`, that's probably a bad idea. A single quote has the same ascii value as an apostrophe. These are used in contractions and surnames. You've already been told about query parameters, the path to happiness. — Dan Bracuk, Apr 03 '15 at 16:02

Adam Cameron · Accepted Answer · 2015-04-03T15:24:33.937

4

Um... just don't pass your user input (or any other data ~) values hard-coded in your SQL statements, pass them as parameter values instead.

Example:

coloursViaQueryExecute = queryExecute("
    SELECT  en AS english, mi AS maori
    FROM    colours
    WHERE   id BETWEEN :low AND :high 
    ",
    {low=URL.low, high=URL.high},
    {datasource="scratch_mssql"}
);

Where low and high are your parameters.

See relevant docs @ QueryExecute()

And further reading on the topic:

edited Apr 03 '15 at 15:24

answered Apr 03 '15 at 11:55

Adam Cameron

29,677
4
37
78

1

I realize the above is just an example, but you should definitely add a parameter "type" as well. Otherwise, the values are sent as strings and the database ends up performing implicit conversion, [which does not always produce the desired results](http://stackoverflow.com/questions/27049918/coldfusion-parameterizing-a-querie/27066113#27066113). (Edit) BTW, nice entry on parameter do's and don'ts :) – Leigh Apr 03 '15 at 16:01

Scott Stroz · Answer 2 · 2015-04-04T12:22:20.400

4

Without parameterizing the user data, you are opening yourself to SQL injection. The REReplace() may not catch everything. Here is how you should rewrite that code to use cfqueryparam. You may need to tweak the addParam() method calls to add the correct cfsqltype.

sqlstr = "";
      sqlstr = "insert into usertime set
      userid = :userid,
      projectid = :projectid,
      timesheetdate = :timesheetdate,
      estimatedtimespent = :estimatedtimespent,
      description = :description,
      timeentered = :timeentered";

        queryObj = new query();
        queryObj.setDatasource("timesheet");
        queryObj.setName("adduserTime");
        queryObj.addParam( name="userid", value=arguments.usertimeparams.userid);
        queryObj.addParam( name="projectid", value=arguments.usertimeparams.projectid);
        queryObj.addParam( name="timesheetdate", value=arguments.usertimeparams.timesheetdate, cfsqltype="CF_SQL_TIMESTAMP");
        queryObj.addParam( name="estimatedtimspent", value=arguments.userTimeParams.jobhours * 60 + arguments.userTimeParams.jobMins, cfsqltype="CF_SQL_INTEGER");
        queryObj.addParam( name="description", value=arguments.usertimeparams.description);
        queryObj.addParam( name="timeentered", value=arguments.usertimeparams.timeentered, cfsqltype="CF_SQL_INTEGER");
        result = queryObj.execute(sql=sqlstr);
        adduserTime = result.getResult();
        return result.getPrefix().generatedKey;

edited Apr 04 '15 at 12:22

answered Apr 03 '15 at 13:55

Scott Stroz

7,510
2
21
25

That's a lot more code than you need mate. You don't need to attach the parameter values one at a time. See example here: http://blog.adamcameron.me/2014/01/using-querycfc-doesnt-have-to-be-drama.html – Adam Cameron Apr 03 '15 at 15:18
1

Understood, but thought it might be easier to understand what was being done if I added each separately. – Scott Stroz Apr 03 '15 at 15:20
1

Yep. [Escaping quotes is definitely not bullet-proof](http://stackoverflow.com/a/18797042/104223). As Scott and Adam said, you need to use "parameterized" sql. – Leigh Apr 03 '15 at 18:37
@ scott thanx but this code is not working..i tried and getting error after queryObj.addParam( name="userid", ...) lines as it says Element USERTIMPARAMS.PROJECTID is undefined in ARGUMENTS. but i checked with dump and got all value..may be not the correct way to write addParam..?? – Leo the lion Apr 04 '15 at 06:31
Oops, there was a typo. I mis-named the variable. I just fixed the code. – Scott Stroz Apr 04 '15 at 12:21

magic_quotes_gpc setting for ColdFusion? ColdBox?

2 Answers2