1

I've been trying to set a simple cfstoredproc with cfc component which passes two parameters to mysql stored proc and order by query.

here is the test code:

test.cfm

<cfset sqlN = createObject ("component","cfc.testNews")>
<cfset loadNews = sqlN.getNews(displayFrom=0, sortNewsBy="Date")>

<cfoutput query="loadNews">
#newsID#<br />
</cfoutput>

testNews.cfc

<cffunction name="getNews" access="public" output="false">


    <cfargument name="displayFrom" type="numeric" required="true">
    <cfargument name="sortNewsBy" type="string" required="true">

        <cfstoredproc procedure="spGetNews" datasource="mydatabase">
        <cfprocparam value="#displayFrom#" cfsqltype="cf_sql_integer">
        <cfprocparam value="#sortNewsBy#" cfsqltype="cf_sql_varchar">
        <cfprocresult name="qNews">
        </cfstoredproc>
        <cfreturn qNews>

</cffunction>

in mysql storedproc i have:

 select newsID
 from news    
 order by sortNewsBy desc limit displayFrom,25;

although cf does not throw any error msg, as i can see cfprocparam, more specifically cfsqltype is causing the issue as it passes nothing!? i've also tried to do the same with cfquery and cfqueryparam tag on .cfm page (instead of cfstoredproc and cfc) and the problem still persists.

cfdump on cfm page returns ... order by ? desc limit ?,25;

I know how to make it work with cfquery and cfqueryparam on cfm page but have no idea what to do with cfprocparam, so is there a workaround for this issue. I would really appreciate your help on this one.

EDIT here is the entire mysql stored proc

CREATE DEFINER=`root`@`localhost` PROCEDURE `spGetNews`(in displayFrom int, sortNewsBy varchar (15))
BEGIN
select newsID
from news
order by sortNewsBy desc limit displayFrom,25;

/*In MSSQL Server IFNULL is not supported, use ISNULL instead*/
END

EDIT1:

<cffunction name="GetNews" access="public" output="false">


    <cfargument name="DBcolumn" type="string" required="true">
    <cfargument name="sort" type="string" required="true">

        <cfset whiteList=("newsDate,viewCount")>
        <cfif listfindnocase(whiteList,arguments.DBcolumn)>
        <cfset DBcolumn=arguments.DBcolumn>
        <cfelse>
        <cfset DBcolumn="newsDate">
        </cfif>

        <cfstoredproc procedure="spGetNews" datasource="mydatabasse">
        <cfprocparam value="#DBcolumn#" cfsqltype="cf_sql_varchar">
        <cfprocparam value="#arguments.sort#" cfsqltype="cf_sql_varchar">
        <cfprocresult name="qNews">
        </cfstoredproc>
        <cfreturn qPosts>

</cffunction>

mysql stored proc

DELIMITER //
CREATE PROCEDURE spGetNews(in DBcolumn varchar(50),in sort varchar(5))
BEGIN
SET @DBcolumn := DBcolumn;
SET @sort := sort;
SET @query := CONCAT('SELECT newsID from news ORDER BY ',  @DBcolumn,'',@sort);
PREPARE statement FROM @query;
EXECUTE statement;
DEALLOCATE PREPARE statement;
END //
DELIMITER ;
infused
  • 24,000
  • 13
  • 68
  • 78
user2595617
  • 219
  • 3
  • 13
  • Did you try scoping your arguments in the `getNews` function? `` and `` – Miguel-F Sep 10 '13 at 19:01
  • yes I did, and unfortunately it does not work :( – user2595617 Sep 10 '13 at 19:05
  • Aside from the scoping your CF code looks okay to me. Can you post your entire storedproc code? Or at least the declaration of the parameters that you are attempting to pass and how you are referencing them in your SQL code. – Miguel-F Sep 10 '13 at 19:13
  • this is basically everything, given it's just a test example. sure I will have joins and more sophisticated cfml code though i can not move forward because even this simple one does not work. – user2595617 Sep 10 '13 at 19:17
  • The presence of `?` in debug output usually just represents the presence of a bind parameter (ie that you used cfquery/cfprocparam). It does not mean the value is not being passed to the database. Having said that, IIRC MySQL does not support the usage of bind variables in an `order by` clause. So ultimately it may not work anyway. – Leigh Sep 10 '13 at 19:17
  • so basically I am stuck with this!? – user2595617 Sep 10 '13 at 19:24
  • @Leigh - could the OP set a local variable using the bind variable and then use that local variable in the `order by` clause? See this other question for example - http://stackoverflow.com/questions/15876263/order-by-stored-procedure-parameter – Miguel-F Sep 10 '13 at 19:24
  • I don't use mysql so I could be wrong, but I see no attempt to use any paramters in the stored procedure. I see something in the order by clause, but there is nothing that tells me they are input parameters. – Dan Bracuk Sep 10 '13 at 19:37
  • @Miguel-F - Yes, but that is using dynamic sql, not bind variables, so there is still a sql injection risk. user2595617 - What I am saying is the issue has nothing to do with CF. Bind variables can only be used for *simple* values, not object names (ie table or column names). So if you ran the procedure directly in MySQL (which you should always do first), it should not work correctly their either. While I am more familiar with SQL Server, AFAIK the only way to achieve it in either DBMS is with dynamic sql (as mentioned in Miguel-F's link). Keep in mind that poses a sql injection risk. – Leigh Sep 10 '13 at 20:38
  • here is what i came up with (check out EDIT1), it works with and without "whitelist". hopefully it's not vulnerable to mysql injection. so what do you think, am I going in the right direction? – user2595617 Sep 11 '13 at 10:55
  • also i will remove dynamic adding of sort(asc,desc) and add limit! – user2595617 Sep 11 '13 at 10:59
  • *am I going in the right direction* Yes. Validation is the only way to secure dynamic sql. You can either validate in CF or within the stored procedure itself using the db metadata views ie information_schema.columns. – Leigh Sep 11 '13 at 13:47

0 Answers0