Link to home
Start Free TrialLog in
Avatar of Bobby X
Bobby XFlag for United States of America

asked on

Need help with EXECUTE sp_executesql in ColdFusion cfquery

Hi,

Can someone please tell me why I am getting the error "Conversion failed when converting the varchar value 'UPDATE products SET productUniqueID = '197A258F-D061-46A1-BTD2-710FD1B16236' WHERE product_id = ' to data type int." when running the cfquery below?

<cfquery name="qGetInfo" datasource="SomeDatasoureName">
      DECLARE       @tableName                         VARCHAR(100);
      DECLARE      @uniqueID                         VARCHAR(100);
      DECLARE      @tablePrimaryKeyName      VARCHAR(75);
      DECLARE      @tablePrimaryKeyValue      INT;
      DECLARE      @parameterDefinition      VARCHAR(100);
      DECLARE      @SQLQuery                        VARCHAR(1000);
      SET @tableName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#LOCAL.tableName#" />;
      SET @uniqueID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#LOCAL.uniqueID)#" />;
      SET @tablePrimaryKeyName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#LOCAL.tablePrimaryKeyName#" />;
      SET @tablePrimaryKeyValue = <cfqueryparam cfsqltype="cf_sql_integer" value="#LOCAL.tablePrimaryKeyValue#" />;
      SET @SQLQuery = 'UPDATE ' + @tableName + ' SET productUniqueID = ' + @uniqueID + ' WHERE ' + @tablePrimaryKeyName + ' = ' + @tablePrimaryKeyValue;
      SET @parameterDefinition = '@uniqueID VARCHAR(100), @tablePrimaryKeyValue INT';
      EXECUTE sp_executesql @SQLQuery, @parameterDefinition,  @uniqueID, @tablePrimaryKeyValue;
</cfquery>      

Many thanks in advance.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

You cannot convert a VARCHAR value to an Int, Bold below is possible.

SELECT CAST(NEWID() AS INT) --- <<Not possible since the NewId is of type varchar since it has alpha numeric values...>>

SELECT CAST(NEWID() AS VARCHAR(100))

Hope it helps !!
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> SELECT CAST(NEWID() AS VARCHAR(100))

Pawan  - Please slow down and read the questions more carefully.  Notice the code is using  sp_executesql.  Casting indiscriminately could construct the wrong SQL statement and end up exposing their db to SQL injection.
@WebAppDeveloper - Speaking of SQL injection, the @tableName and @tablePrimaryKeyName value cannot be protected by either cfqueryparam OR sp_executesql.  If those values are user supplied, the current query is still vulnerable. You'll have to validate those values yourself (before using them in sp_executesql).
Avatar of Bobby X

ASKER

_agx_

No,  those values are not user supplied, so they are ok.

Thank you so much for your help again.
@WebAppDeveloper - Ok, good. Just wanted to be sure you, or anyone reading this down the road, knew that using cfqueryparam on those 2 values won't do anything to protect the SQL.
BTW, any reason you have to use sp_executesql? Looks like a plain cfquery + cfqueryparam could work just as well.
Avatar of Bobby X

ASKER

_agx_,

The reason is that there's a requirement for me to dynamically pass in the tableName, tablePrimaryKeyName, tablePrimaryKeyValue and the uniqueID to dynamically update the productUniqueID column (actual column name not provided) with the dynamic @uniqueID and bla, bla bla. We have several tables that contain the same exact productUniqueID column (again actual column name not provided), and all these dynamic info (e.g, tableName, tablePrimaryKeyName, tablePrimaryKeyValue, etc) are being passed into a CFC method that contains this cfquery that does its thing.
Well technically, there's no reason it couldn't be done with a regular cfquery like below.  Note, it has the same sql injection risks as sp_executesql.  Since you never know how functions/cfc's might get used down the road, I'd still validate the table and column names before using them in a query.  (It can also be dynamic using db metadata tables).  That way you know the function can never pose a risk. Even if it ends up being used differently than intended later on.

         
          <!---- 
                 validate table and column names first .....
          --->
           
        <cfquery ...>
            UPDATE #LOCAL.tableName#
            SET          #LOCAL.numericColumnToUpdate#  =  <cfqueryparam cfsqltype="cf_sql_varchar" value="#LOCAL.uniqueID#" />
            WHERE   #LOCAL.tablePrimaryKeyName#  = <cfqueryparam cfsqltype="cf_sql_integer" value="#LOCAL.tablePrimaryKeyValue#" />
        </cfquery>

Open in new window

Avatar of Bobby X

ASKER

Ok thanks, _agx_.