cfstoredproc with list or cfquery with guid

Report · May 14, 2008

Hey,

I'm trying to get thru a syntax problem (probably), but can't find the right solution...
Here's the thing, I have the following stored procedure in my SQL 2005 database:

CREATE PROCEDURE [dbo].[spPlanningCheck_s]
(
@PID nvarchar(2000) = NULL,
@pEmployeeId uniqueidentifier,
@pDatestart datetime,
@pDateend datetime,
@pProjectId int = NULL,
@pPlanningIds varchar(1000) = NULL
)
AS
BEGIN
DECLARE @iError int
SET NOCOUNT ON
SELECT * FROM tblPLANNING
WHERE fk_employee_id = @pEmployeeId
AND datediff(day, tblPLANNING.datestart, @pDateend) >= 0
AND datediff(day, tblPLANNING.dateend, @pDatestart) <= 0
AND (@pProjectID IS NULL OR (@pProjectId IS NOT NULL AND fk_project_id = @pProjectId))
AND (@pPlanningIds IS NULL OR (@pPlanningIds IS NOT NULL AND id NOT in (@pPlanningIds)))

ORDER BY datestart

SELECT @iError = @@error
IF @iError <> 0
BEGIN
GOTO error_section
END
GOTO end_section
error_section:
end_section:
RETURN @iError
END

I've been trying to call this procedure with a cfstoredproc, but cfstoredproc doesn't understand the concept of 'list'.
So I used my work around : cfquery. Unfortunatly, cfquery doesn't seem to accept my uniqueidentifier?
So I'm at a complete loss...

Here are the 2 options I tried:

<cffunction
name="checkPlanning"
access="public"
returntype="query"
output="false"
hint="Check planning">

<cfargument name="iPlanningId" type="numeric" required="false" default="-1">
<cfargument name="iEmployeeId" type="guid" required="true">
<cfargument name="dStartdate" type="date" required="true">
<cfargument name="dEnddate" type="date" required="true">
<cfargument name="iProjectId" type="numeric" required="false" default="-1">
<cfargument name="sPlanningIds" type="string" required="false" default="-1">

<cfstoredproc datasource="#application.stApplicationData.sDataSource#"
procedure = "spPlanningCheck_s" returncode="true" debug="#session.stuserdata.bdebugmode#"
>
<cfif arguments.iPlanningId EQ -1>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@pId" null="true">
<cfelse>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@pId" value="#arguments.iPlanningId#">
</cfif>
<cfprocparam type="In" cfsqltype="CF_SQL_UNIQUEIDENTIFIER" dbvarname="@pEmployeeId" value="#arguments.iEmployeeId#">
<cfprocparam type="In" cfsqltype="CF_SQL_TIMESTAMP" dbvarname="@pDatestart" value="#arguments.dStartdate#">
<cfprocparam type="In" cfsqltype="CF_SQL_TIMESTAMP" dbvarname="@pDateend" value="#arguments.dEnddate#">
<cfif arguments.iProjectId EQ -1>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@pProjectId" null="true">
<cfelse>
<cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@pProjectId" value="#arguments.iProjectId#">
</cfif>
<cfif arguments.sPlanningIds EQ -1>
<cfprocparam type="In" cfsqltype="CF_SQL_STRING" dbvarname="@pPlanningIds" null="true">
<cfelse>
<cfprocparam type="In" cfsqltype="CF_SQL_STRING" dbvarname="@pPlanningIds" value="#arguments.sPlanningIds#">
</cfif>

<cfprocresult name="qResult">
</cfstoredproc>

<cfif cfstoredproc.statusCode NEQ 0>
<cfmodule template="#application.stApplicationData.sCFMapping#customtags/logging.cfm" sModule="PlanningData.getPlanning" sMessage="Stored Procedure Failed: #cfstoredproc.statusCode#">
</cfif>

<cfreturn qResult>
</cffunction>

Option 2:
<cffunction
name="checkPlanning"
access="public"
returntype="query"
output="false"
hint="Check planning">

<cfargument name="iPlanningId" type="string" required="false" default="NULL">
<cfargument name="iEmployeeId" type="guid" required="true">
<cfargument name="dStartdate" type="date" required="true">
<cfargument name="dEnddate" type="date" required="true">
<cfargument name="iProjectId" type="numeric" required="false" default="NULL">
<cfargument name="sPlanningIds" type="string" required="false" default="NULL">

<cfquery name="qResult" datasource="#application.stApplicationData.sDataSource#">
DECLARE @RC int
DECLARE @PID nvarchar(2000)
DECLARE @pEmployeeId uniqueidentifier
DECLARE @pDatestart datetime
DECLARE @pDateend datetime
DECLARE @pProjectId int
DECLARE @pPlanningIds varchar(1000)

EXEC @RC = [PRS].[dbo].[spPlanningCheck_s] #arguments.iPlanningId#, @pEmployeeId="#tostring(arguments.iEmployeeId)#", #arguments.dStartdate#, #arguments.dEnddate#, #arguments.iProjectId#, #tostring(arguments.sPlanningIds)#
</cfquery>
<cfreturn qResult>
</cffunction>

Summary:
- Why doesn't cfstoredproc work with lists?
- Why doens't cfquery work with uniqueidentifiers?

Report · May 14, 2008

Ok...
So I finally found the solution to my problem...
I added '{ and }' to iEmployeeId manually, so that worked.
After that I added single quotes to the date fields, which also helped!

Now I'm still struggeling with the fact that my PlanningIds aren't seen as multiple items, but as 1 string...
grmbl... thought that worked this way... grmbl

Report · May 14, 2008

Feels like I'm talking to myself, but hey... Perhaps someone is thinking with me on this...

It turns out to be an SQL problem...
After finding something using google, I tried to implement it, but alas, didn't help...

If anyone is able to help on MSSQL matters, here's the problem:

I call the procedure like this:

DECLARE @RC int
DECLARE @pId nvarchar(2000)
DECLARE @pEmployeeId varchar(1000)
DECLARE @pDatestart varchar(1000)
DECLARE @pDateend varchar(1000)
DECLARE @pProjectId int
DECLARE @pPlanningIds varchar(1000)

-- TODO: Set parameter values here.

EXECUTE @RC = [PRS].[dbo].[spPlanningCheck_s]
@pId
,'326D759B-F304-42DF-8B73-A2E37DCAA10F'
,'2008-06-11'
,'2008-07-04'
,48
,'47085,47086,47087'

Which gives me the following error:
Msg 102, Level 15, State 1, Line 7
The syntax near , is invalid.

And my procedure looks like this:

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[spPlanningCheck_s]
(
@pId nvarchar(2000) = NULL,
@pEmployeeId uniqueidentifier,
@pDatestart varchar(1000),
@pDateend varchar(1000),
@pProjectId varchar(1000) = NULL,
@pPlanningIds varchar(1000) = NULL
)
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT "YMD"
DECLARE @iError int
DECLARE @SQL1 varchar(8000)

SET @SQL1 = '
SELECT * FROM vwWEEKPLANNING
WHERE fk_employee_id = ''' + UPPER(CAST(@pEmployeeId AS VARCHAR (50))) + '''
AND datediff(day, vwWEEKPLANNING.datestart, ' + @pDateend + ') >= 0
AND datediff(day, vwWEEKPLANNING.dateend, ' + @pDatestart + ') <= 0
AND (' + @pProjectID + ' IS NULL OR (' + @pProjectId + ' IS NOT NULL AND fk_project_id = ' + @pProjectId + '))
AND (' + @pPlanningIds + ' IS NULL OR (' + @pPlanningIds + ' IS NOT NULL AND idchar NOT in (' + @pPlanningIds + ')))

ORDER BY datestart'

EXEC(@SQL1)

SELECT @iError = @@error
IF @iError <> 0
BEGIN
GOTO error_section
END
GOTO end_section
error_section:
end_section:
RETURN @iError
END

Report · May 14, 2008

Ok still talking to myself, but still getting closer to my answer...
Except for this weird SQL behaviour...

This query gives back results just fine:

SET DATEFORMAT YMD;

SELECT * FROM vwWEEKPLANNING
WHERE fk_employee_id_char = UPPER(CAST('326D759B-F304-42DF-8B73-A2E37DCAA10F' AS VARCHAR (50)))
AND (vwWEEKPLANNING.datestart BETWEEN '2008-06-11' and '2008-07-04'
OR vwWEEKPLANNING.dateend BETWEEN '2008-06-11' and '2008-07-04')
AND (NULL IS NULL OR (NULL IS NOT NULL AND fk_project_id = NULL))
AND ('Y' IS NULL OR ('Y' IS NOT NULL AND id NOT in (47085,47086)))

ORDER BY datestart

But I have to use a stored procedure, so here it is:

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[spPlanningCheck_s]
(
@pEmployeeId uniqueidentifier,
@pDatestart varchar(1000),
@pDateend varchar(1000),
@pProjectId varchar(1000) = NULL,
@pPresent varchar(10) = NULL,
@pPlanningIds varchar(1000) = NULL
)
AS
BEGIN
SET NOCOUNT ON
SET DATEFORMAT YMD;
DECLARE @iError int
DECLARE @SQL1 varchar(8000)

SET @SQL1 = '
SELECT * FROM vwWEEKPLANNING
WHERE fk_employee_id_char = ''' + UPPER(CAST(@pEmployeeId AS VARCHAR (50))) + '''
AND (vwWEEKPLANNING.datestart BETWEEN ' + @pDateend + ' AND ' + @pDatestart + '
OR vwWEEKPLANNING.dateend BETWEEN ' + @pDateend + ' AND ' + @pDatestart + ')
AND (' + @pProjectID + ' IS NULL OR (' + @pProjectId + ' IS NOT NULL AND fk_project_id = ' + @pProjectId + '))
AND (' + @pPresent + ' IS NULL OR (' + @pPresent + ' IS NOT NULL AND id NOT in (' + @pPlanningIds + ')))

ORDER BY datestart'

EXEC(@SQL1)

SELECT @iError = @@error
IF @iError <> 0
BEGIN
GOTO error_section
END
GOTO end_section
error_section:
end_section:
RETURN @iError
END

Calling this stored procedure like this:

DECLARE @RC int
DECLARE @pEmployeeId uniqueidentifier
DECLARE @pDatestart varchar(1000)
DECLARE @pDateend varchar(1000)
DECLARE @pProjectId varchar(1000)
DECLARE @pPresent varchar(10)
DECLARE @pPlanningIds varchar(1000)

-- TODO: Set parameter values here.

SET DATEFORMAT YMD;

EXECUTE @RC = [PRS].[dbo].[spPlanningCheck_s]
@pEmployeeId='326D759B-F304-42DF-8B73-A2E37DCAA10F'
,@pDatestart='2008-06-11'
,@pDateend='2008-07-04'
,@pProjectId=NULL
,@pPresent='Y'
,@pPlanningIds='47085,47086'

Gives no results....
Really starting to get agressive feelings towards my screen...

Report · May 14, 2008

This is the only part I understand:
SELECT * FROM vwWEEKPLANNING
WHERE fk_employee_id_char = UPPER(CAST('326D759B-F304-42DF-8B73-A2E37DCAA10F' AS VARCHAR (50)))
AND (vwWEEKPLANNING.datestart BETWEEN '2008-06-11' and '2008-07-04'
OR vwWEEKPLANNING.dateend BETWEEN '2008-06-11' and '2008-07-04')
AND (NULL IS NULL OR (NULL IS NOT NULL AND fk_project_id = NULL))
AND ('Y' IS NULL OR ('Y' IS NOT NULL AND id NOT in (47085,47086)))

The following things look strange:
1. casting a string as a varchar.
2. looking for null values in fields that look like foreign keys (fk_project_id = NULL)
3. looking for string literals being equal to null

Report · May 14, 2008

Hehe, that part was where I filled in the variables!
I manually created the query using the same variables as I was giving the stored procedure.
As for point 1, that is an uniqueidentifier which I have to cast to a varchar before it's usable in the query...

This is the actual query (using the variables at the end of my last post)

SELECT * FROM vwWEEKPLANNING
WHERE fk_employee_id_char = ''' + UPPER(CAST(@pEmployeeId AS VARCHAR (50))) + '''
AND (vwWEEKPLANNING.datestart BETWEEN ' + @pDateend + ' AND ' + @pDatestart + '
OR vwWEEKPLANNING.dateend BETWEEN ' + @pDateend + ' AND ' + @pDatestart + ')
AND (' + @pProjectID + ' IS NULL OR (' + @pProjectId + ' IS NOT NULL AND fk_project_id = ' + @pProjectId + '))
AND (' + @pPresent + ' IS NULL OR (' + @pPresent + ' IS NOT NULL AND id NOT in (' + @pPlanningIds + ')))

ORDER BY datestart

Report · May 15, 2008

Finally fixed it!
Here's correct procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spPlanningCheck_s]
(
@pEmployeeId varchar(50),
@pDatestart varchar(50),
@pDateend varchar(50),
@pProjectId varchar(1000) = NULL,
@pPlanningIds varchar(1000) = NULL
)
AS
BEGIN
SET DATEFORMAT YMD;
DECLARE @iError int
DECLARE @SQL1 varchar(8000)

SET @SQL1 = '
SELECT * FROM vwWEEKPLANNING
WHERE fk_employee_id = ''' + @pEmployeeId + '''
AND datediff("d", ''' + @pDatestart + ''', vwWEEKPLANNING.dateend) >= 0
AND datediff("d", ''' + @pDateend + ''', vwWEEKPLANNING.datestart) <= 0 '
IF Not @pProjectID IS NULL
BEGIN
SET @SQL1 = @SQL1 + 'AND fk_project_id = ' + @pProjectId + ' '
END
IF NOT @pPlanningIds IS NULL
BEGIN
SET @SQL1 = @SQL1 + 'AND id NOT in (' + @pPlanningIds + ') '
END
SET @SQL1 = @SQL1 + 'ORDER BY datestart'

EXEC(@SQL1)

SELECT @iError = @@error
IF @iError <> 0
BEGIN
GOTO error_section
END
GOTO end_section
error_section:
end_section:
RETURN @iError
END

Report · May 16, 2008

Be aware that your code is vulnerable to a SQL injection attack. I recommend you use cfqueryparam to reduce your database's vulnerability. See attached sample.

SQL injection topic from Microsoft SQL Server documentation
http://msdn.microsoft.com/en-us/library/ms161953.aspx

SQL injection and ColdFusion
http://www.adobe.com/devnet/coldfusion/articles/cfqueryparam.html
http://www.adobe.com/devnet/coldfusion/articles/ben_forta_faster.html

cfqueryparam tag
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html#1102474

Report · May 18, 2008

Ok, the reason I used cfquery in stead of cfstoredproc was because I couldn't send a list thru... But now the last link shows that with cfqueryparam there is a list option... I didn't know that :X

But still, some lines of code I have to use will always be vulnerable to sql injection I suspect... But since my program is only used inside the company, executing sql injects only hurts the users themselves ;)
If this was a internetsite in stead of an intranetsite such precautions would be necessary!

But I wonder, from what I've briefly read, isn't checking for an ; in the parameters enough to prevent this?

Checking for an ' will only prevent errors?

Report · May 18, 2008

quote:

since my program is only used inside the company, executing sql injects only hurts the users themselves 😉 If this was a internetsite in stead of an intranetsite such precautions would be necessary!

A single disgruntled employee could cause a lot of damage.

quote:

But I wonder, from what I've briefly read, isn't checking for an ; in the parameters enough to prevent this?

Hexidecimal characters could be used to bypass checking for ";".

Report · May 18, 2008

Ok true... But still this isn't exactly an computer experts company!
But I'll heed your warning and build in extra safety lines when using cfquery instead of cfstoredproc!