July 21, 2015 at 4:32 am
Hi all!
I have made this defination for a stored procedure:
PROCEDURE EP_Conterbalances
@Start_Date_For_Totals_Date DATETIME,
@EmpFilterAddDuty VARCHAR(500),
@CounterBalanceType_id INT,
@dateFrom DATETIME,
@DateTo DATETIME
AS
BEGIN
[Much code!] and then
and d.employeeid IN ( @EmpFilterAddDuty)
The value of @EmpFilterAddDuty could be:
'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID '
If i Replace @EmpFilterAddDuty with this in a QUERY, it gives me the expected result, but if i try to execute the stored procedure.:
DECLARE@return_value int
EXEC@return_value = [dbo].[EP_Conterbalances]
@Start_Date_For_Totals_Date = N'20120831',
@EmpFilterAddDuty = 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ',
@CounterBalanceType_id = 4,
@dateFrom = N'20150601',
@DateTo = N'20150630'
SELECT'Return Value' = @return_value
I get this error code:
Conversion failed when converting the varchar value 'SELECT E.EmployeeID FROM dbo.EmployeeGroupMapToEmployee E, dbo.Per_Budget B WHERE E.EmployeeID = B.PER_PERSONAL_ID AND B.PEB_Budget_id = 243 AND E.EmployeeGroupID IN (SELECT H.Id FROM dbo.EmployeeGroup H WHERE H.InstitutionsId = 22) GROUP BY E.EmployeeID ' to data type int.
I really do not understand why SQL 2012 tries to convert the value to an int, and I want to know how to pass the text string.
Best regards
Edvard Korsbæk
July 21, 2015 at 5:02 am
You cannot pass T-SQL to be executed like this.
You will have to either resort to using something like sp_executesql, or change your proc around a bit to avoid this somewhat ungainly construction.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 21, 2015 at 6:59 am
As mentioned, you need to use dynamic SQL, but with your current code, you're wide open for SQL Injection. Here's an example on how could you do it.
CREATE PROCEDURE EP_Conterbalances
(
@Start_Date_For_Totals_Date DATETIME,
@EmpFilterAddDuty NVARCHAR(500), --sp_Executesql requires unicode strings
@CounterBalanceType_id INT,
@dateFrom DATETIME,
@DateTo DATETIME
)
AS
BEGIN
--[Much code!] and then
IF OBJECT_ID( 'tempdb..#EmpIDFilter') IS NOT NULL
DROP TABLE #EmpIDFilter
CREATE TABLE #EmpIDFilter(EmployeeID int)
INSERT INTO #EmpIDFilter
EXEC sp_executesql @EmpFilterAddDuty --Be prepared for SQL Injection, beware of Bobby Tables
--Your query with the problematic clause.
and d.employeeid IN ( SELECT EmployeeID FROM #EmpIDFilter)
--Just to make it nice
DROP TABLE #EmpIDFilter
July 21, 2015 at 7:10 am
Thanks Both!
Always learning....
And I have never ever found out myself.
I do not think, that SQL injection is a problem here.
I am not working on the net - This is a WIN32 program used in a controlled enviroment.
Will make a small sample first, and then put it into 'Real world.
Best and quite humble regards
Edvard Korsbæk
July 21, 2015 at 7:20 am
Just remember that if there's human input, there's a threat. It doesn't matter if it comes from outside or inside of the company. Protect yourself against any way that a user can input a direct string.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply