Store procedure parameter is not working

Question

1.00/5 (2 votes)

See more:

SQL

ALTER PROCEDURE [dbo].[sp_get_farmerregistration_by_origin_approveheadid] 
@originname varchar(100),    
@approveheadid varchar(100),    
@mastertype varchar(100),
@farmerctscode varchar(100)    
AS    
BEGIN    
 SET NOCOUNT ON;    
    
 DECLARE @sqlQuery VARCHAR(MAX)    
 DECLARE @masterYear VARCHAR(100)  
      
    SELECT @masterYear =  farmermasteryear FROM tbl_originmanagement WHERE LOWER(REPLACE(@originname,' ','')) = @originname  



IF @mastertype = 'farmerdata'    
 BEGIN    
  SET @sqlQuery = 'SELECT A.*,A.firstname + '' '' + A.lastname + '' '' + A.surname as farmername,B.villagename,    
C.subdistrictname,D.departmentname as districtname,G.zonename,H.name as zonalheadname,M.sectionname,    
I.statusname as zonalheadapprovestatus,J.statusname as regionalheadapprovestatus,    
K.name as zonalheadapprovename,L.name as regionalheadapprovename,    
E.regionname,F.name as regionalheadname    
FROM dbo.tbl_farmerregistration_' + @originname + ' A    
INNER JOIN dbo.tbl_villagemaster_' + @originname + ' B ON B.villageid = A.village    
INNER JOIN dbo.tbl_subdistrictmaster_' + @originname + ' C ON C.subdistrictid = B.subdistrictid    
INNER JOIN dbo.tbl_districtmaster_' + @originname + ' D ON D.districtid = C.districtid    
INNER JOIN dbo.tbl_regionmaster_' + @originname + ' E ON E.regionid = D.regionid    
INNER JOIN dbo.tbl_registration F ON F.regid = E.regionalhead    
INNER JOIN dbo.tbl_sectionmaster_' + @originname + ' M ON M.fieldstaffid = a.createdby    
INNER JOIN dbo.tbl_zonemaster_' + @originname + ' G ON G.zoneid = M.zoneid    
INNER JOIN dbo.tbl_registration H ON H.regid = G.zoneheadid    
INNER JOIN dbo.tbl_approvestatusmaster I ON I.approvestatusid = A.firstlevelapprovalstatus    
INNER JOIN dbo.tbl_approvestatusmaster J ON J.approvestatusid = A.secondlevelapprovalstatus    
LEFT JOIN dbo.tbl_registration K ON K.regid = A.firstlevelapprovedby    
LEFT JOIN dbo.tbl_registration L ON L.regid = A.secondlevelapprovedby   
WHERE H.regid = ''' + @approveheadid + ''' AND A.farmerseason = ''' + @masterYear + '''  order by farmerctscode'    
 END 

 ELSE IF(( @mastertype = 'farmerdata' ) and    ( @farmerctscode = '' ))   
 BEGIN    
  SET @sqlQuery = 'SELECT A.*,A.firstname + '' '' + A.lastname + '' '' + A.surname as farmername,B.villagename,    
C.subdistrictname,D.departmentname as districtname,G.zonename,H.name as zonalheadname,M.sectionname,    
I.statusname as zonalheadapprovestatus,J.statusname as regionalheadapprovestatus,    
K.name as zonalheadapprovename,L.name as regionalheadapprovename,    
E.regionname,F.name as regionalheadname    
FROM dbo.tbl_farmerregistration_' + @originname + ' A    
INNER JOIN dbo.tbl_villagemaster_' + @originname + ' B ON B.villageid = A.village    
INNER JOIN dbo.tbl_subdistrictmaster_' + @originname + ' C ON C.subdistrictid = B.subdistrictid    
INNER JOIN dbo.tbl_districtmaster_' + @originname + ' D ON D.districtid = C.districtid    
INNER JOIN dbo.tbl_regionmaster_' + @originname + ' E ON E.regionid = D.regionid    
INNER JOIN dbo.tbl_registration F ON F.regid = E.regionalhead    
INNER JOIN dbo.tbl_sectionmaster_' + @originname + ' M ON M.fieldstaffid = a.createdby    
INNER JOIN dbo.tbl_zonemaster_' + @originname + ' G ON G.zoneid = M.zoneid    
INNER JOIN dbo.tbl_registration H ON H.regid = G.zoneheadid    
INNER JOIN dbo.tbl_approvestatusmaster I ON I.approvestatusid = A.firstlevelapprovalstatus    
INNER JOIN dbo.tbl_approvestatusmaster J ON J.approvestatusid = A.secondlevelapprovalstatus    
LEFT JOIN dbo.tbl_registration K ON K.regid = A.firstlevelapprovedby    
LEFT JOIN dbo.tbl_registration L ON L.regid = A.secondlevelapprovedby   
WHERE H.regid = ''' + @approveheadid + ''' AND A.farmerseason = ''' + @masterYear + '''  order by farmerctscode'    
 END     

  print(@sqlQuery)
  EXEC(@sqlQuery)    
END

when i run the above store procedure output i get as follows

SQL

exec sp_get_farmerregistration_by_origin_approveheadid 'ivorycoast','','farmerdata',''

1875 2019 2010 Ramesh Babu - MALE 31-05-1985 Chennai Suresh
1975 2019 2010 Suresh Babu - MALE 01-05-1995 Chennai Sathish
1775 2019 2010 Sashi Sashi - MALE 11-08-1995 Chennai Sathish
1675 2019 2010 Srir Sashi - MALE 01-12-1999 Chennai Sathish
1795 2019 2010 Arjun Sashi - MALE 01-07-1989 Chennai Sathish

Bu when i pass the farmerctscode in the procedure i get the same output as follows

SQL

exec sp_get_farmerregistration_by_origin_approveheadid 'ivorycoast','','farmerdata','1875'

1875 2019 2010 Ramesh Babu - MALE 31-05-1985 Chennai Suresh
1975 2019 2010 Suresh Babu - MALE 01-05-1995 Chennai Sathish
1775 2019 2010 Sashi Sashi - MALE 11-08-1995 Chennai Sathish
1675 2019 2010 Srir Sashi - MALE 01-12-1999 Chennai Sathish
1795 2019 2010 Arjun Sashi - MALE 01-07-1989 Chennai Sathish

when i pass farmerctscode in the stored procedure i want the below output

SQL

exec sp_get_farmerregistration_by_origin_approveheadid 'ivorycoast','','farmerdata','1875'

1875 2019 2010 Ramesh Babu - MALE 31-05-1985 Chennai Suresh

from my above store procedure what is the mistake.

What I have tried:

refer to describe section.

Posted 15-Jul-18 5:22am

nar86

Updated 15-Jul-18 8:31am

Bryian Tan

v2

Add a Solution

Comments

Richard Deeming 17-Jul-18 13:00pm

'... WHERE H.regid = ''' + @approveheadid + ''' AND A.farmerseason = ''' + @masterYear + '''  order by farmerctscode'

Don't do it like that. You've just introduced a SQL Injection vulnerability into your stored procedure.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Parameters must always be passed to sp_executesql[^] as parameters.

'... WHERE H.regid = @approveheadid AND A.farmerseason = @masterYear order by farmerctscode'
...
PRINT(@sqlQuery);

EXEC sp_executesql @sqlQuery, 
    N'@approveheadid varchar(100), @masterYear varchar(100)',
    @approveheadid = @approveheadid,
    @masterYear = @masterYear;

For the parts which can't be passed as parameters, you need to validate them to death. Never trust anything that could be influenced by the user!

DECLARE @tbl_farmerregistration sysname;
SELECT @tbl_farmerregistration = QUOTENAME(name) FROM sys.tables WHERE name = N'tbl_farmerregistration_' + @originname;
-- Repeat for all other table names

...


'... FROM dbo.' + @tbl_farmerregistration + ' A ...'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 7870345 · Answer 1 · 2018-07-15T08:32:00

In the code of your stored procedure you construct in @sqlQuery the select stament depending on the value of @mastertype and @farmerctscode.
The structure is

SQL

...
IF @mastertype='farmerdata'
   BEGIN
   /*first condition*/
   SET @sqlQuery=bla_bla_bla1
   END
ELSE
   IF ((@master='farmerdata' and  (@farmerctscode='') )
      BEGIN
      /*second condition*/
      SET @sqlQuery=bla_bla_bla2
      END
...

so, if @mastertype is 'farmerdata' the first condition is satisisfaced and then the second condition never is evaluated (whatever will be the value of farmerctscode).

If you wish that @sqlQuery could be adquiere two differents values please consider in change the structure to something like:

SQL

...
IF ( (@mastertype='farmerdata') and (@farmerctscode<>'') )
   BEGIN
   /*first condition*/
   SET @sqlQuery=bla_bla_bla1
   END
ELSE
   IF ((@mastertype='farmerdata' and  (@farmerctscode='') )
      BEGIN
      /*second condition*/
      SET @sqlQuery=bla_bla_bla2
      END
...

PD: I cant found the difference between bla_bla_1 and bla_bla_2