Bobby X
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-7 10FD1B1623 6' WHERE product_id = ' to data type int." when running the cfquery below?
<cfquery name="qGetInfo" datasource="SomeDatasoureN ame">
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.tablePrimary KeyName#" />;
SET @tablePrimaryKeyValue = <cfqueryparam cfsqltype="cf_sql_integer" value="#LOCAL.tablePrimary KeyValue#" />;
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.
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-7
<cfquery name="qGetInfo" datasource="SomeDatasoureN
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"
SET @uniqueID = <cfqueryparam cfsqltype="cf_sql_varchar"
SET @tablePrimaryKeyName = <cfqueryparam cfsqltype="cf_sql_varchar"
SET @tablePrimaryKeyValue = <cfqueryparam cfsqltype="cf_sql_integer"
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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.
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).
ASKER
_agx_
No, those values are not user supplied, so they are ok.
Thank you so much for your help again.
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.
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.
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>
ASKER
Ok thanks, _agx_.
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 !!