executing cfquery in cfc with parameters received via AJAX

Question

I am trying to set up a system that allows me to get data from my ColdFusion database from my JavaScript code. However, I am running into an error and I am not sure what is causing it. I am new to CF and the engine I am running is ColdFusion MX 7.

Here is the CFC file, cfquery.cfc:

<cfcomponent output="no">
    <cffunction name="phonebookQuery" access="remote" returnType="struct">
        <cfargument name="select" type="string" required="yes">
        <cfargument name="from" type="string" required="yes">
        <cfargument name="where" type="string" required="yes">
        <cfargument name="orderBy" type="string" required="yes">

        <cfquery name="query" datasource="phonebook">
            SELECT #select#
            FROM #from#
            WHERE #where#
            ORDER BY #orderBy#
        </cfquery>
        <cfreturn query>
    </cffunction>
</cfcomponent>

I am probably not returning things correctly, but my code doesn't get that far in execution for me to find out.

This is the JS function that performs the AJAX call. I know I shouldn't use async: false, but I just need something that works for now. I plan on implementing either promises or callbacks once I get it working.

function cfQuery(p){
  var result;
  var queryStr= "cfquery.cfc?method=phonebookQuery&" +
    "select="+p.select+"&"+
    "from="+p.from+"&"+
    "where="+p.where.replace("=","%3D")+"&"+
    "orderBy="+p.orderBy;
  $.get( queryStr, function(data){
    result=data;
  });
  return result;
}

The specific example of the function call I am trying to get to work is:

var query_result= cfQuery({
    select: "*",
    from: "shareloantypes",
    where: "share_loan='S'",
    orderBy: "share_loan_type"
});

I get an internal server error when I try to run the code. it complains about the syntax near the where clause in the SQL:

Error Executing Database Query.

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver][SQL Server]

Line 1: Incorrect syntax near 'S'.

The error occurred in C:\Inetpub\wwwroot\xxxxxx\report\cfquery.cfc: line 12
10 :            FROM #from#
11 :            WHERE #where#
12 :            ORDER BY #orderBy#
13 :        </cfquery>
14 :        <cfreturn query>

SQL    SELECT * FROM shareloantypes WHERE share_loan=''S'' ORDER BY share_loan_type
DATASOURCE    phonebook
VENDORERRORCODE   170
SQLSTATE      42000

You can see that my 'S' is being replaced with ''S''. How can I fix this?, If I replace where: "share_loan='S'" with where: "share_loan=S" in the function call then the SQL that gets generated has no quotes at all, rather than 2 on each side and I get "invalid column name".

you tried to replace and/or escape chars? what have you tried so far? — bovino Marcelo Bezerra, May 18 '15 at 16:46
@MarceloBezerra the other thing I tried was manually replacing the " ' " chars in the value of the `where` key parameter with %27, but that yielded the same result — Luke, May 18 '15 at 16:55
Expect you've simplified your example in this code example to post here, but, just in case you aren't - your code is wide open to hackers who could potentially get all the data from any table in your database. — John Whish, May 18 '15 at 17:06
Just remember you are opening up your DB to security issues here (as John has indicated. It would be trivial to get or destroy your data which this approach. — Mark A Kruger, May 18 '15 at 17:15
please see my updated answer for the absolute proper method of using variables in SQL queries — Russell Uhl, May 19 '15 at 12:45

Russell Uhl · Accepted Answer · 2015-05-19T12:44:29.310

Give this a try:

<cfquery name="query" datasource="phonebook">
    SELECT '#select#'
    FROM '#from#'
    WHERE '#where#'
    ORDER BY '#orderBy#'
</cfquery>

As I recall from my research, putting the quotes around the variables tells coldfusion that it should process those values in a sql-like manner. That is, it won't escape the quotes you are feeding it (or, more accurately, it will intelligently escape them).

Additionally, in my personal experience, it also thwarts SQL injection (to which your code is currently wide open). There are actually coldfusion prepared statements that exist, but I think this is a shorthand method of constructing them, that still prevents injection. If anyone conclusively knows otherwise, and can provide an example of injection even using this technique, please do correct me.

Edit: Looking at your code again, this might work, or it might not. With my personal experience, I've been just passing in variable values, not constructing the query itself. If you can, try to do something like

<cfquery name="query" datasource="phonebook">
    SELECT *
    FROM mytable
    WHERE ID > '#minID#'
    ORDER BY ID DESC
</cfquery>

In this instance, you're merely passing the value you're comparing to, rather than actually building the entire WHERE clause.

Edit: I cannot in good conscience leave this code up here when I am not fully confident it is not vulnerable to SQL injection. The proper way to execute the second query I posted (the one where only the WHERE clause has a variable) is as follows:

<cfquery name="query" datasource="phonebook">
    SELECT *
    FROM mytable
    WHERE ID > <cfqueryparam value="#minID#" CFSQLType="CF_SQL_INTEGER">
    ORDER BY ID DESC
</cfquery>

You can read more about cfqueryparam here: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

https://msdn.microsoft.com/en-us/library/ms187331.aspx If any of those CF variables are based on user input, all I have to do is put "WAITFOR DELAY '02:00';" into the order by variable and your SQL Server is hosed. — Adrian J. Moreno, May 18 '15 at 18:25
@AdrianJ.Moreno I'll have to test it. I HAVE attempted SQL injection against my own systems before (some of which uses code like that) and have been unable to actually accomplish anything — Russell Uhl, May 19 '15 at 12:32
This only works if the only user input is going into the where clause, right? If I wanted to let the users input the SELECT, FROM, or ORDER BY clauses, I would have to do additional sanitation, no? — Luke, May 19 '15 at 13:26
You should scrub ALL user input using an AntiSamy filter before attempting to use it. https://www.owasp.org/index.php/Category:OWASP_AntiSamy_Project — Adrian J. Moreno, May 19 '15 at 15:12
@LukeP: right. I was just providing this as an example. Generally, building the entire query is somewhat messy and not nice. If that's what you need your app to do though, then go for it — Russell Uhl, May 19 '15 at 15:39
@AdrianJ.Moreno I don't think that works. Using a format similar to the second query above, the sql that actually gets generated is `SELECT * FROM zz_dropme WHERE id = 'WAITFOR DELAY ''00:01'';'`. the actual code is `SELECT * FROM zz_dropme WHERE id = '#FORM.field1#'` — Russell Uhl, May 19 '15 at 19:38

executing cfquery in cfc with parameters received via AJAX

1 Answers1