Filtering Help please.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Filtering Help please....

Author

Topic

gpiva
Starting Member

47 Posts

Posted - 2004-09-08 : 04:01:07

Is it possible to pass a paramenter to a storeproc like "where xxx = 0 " like I try to do with this code ?? I want to have a generic sqlfilter paramenter (optional) to pass only if necessary...

Any help will be appreciate!

This is my code:

CREATE PROCEDURE SelectCustomers
@FilterSQL nvarchar(255) = ""
AS
if(@FilterSQL <> "")
begin
select ICustomersId, TLastName, TFirstName, TCity, TState, TPcode, TPhone, TEmail from Customers
where deleted is null and @FilterSQL
end;
else
begin
select ICustomersId, TLastName, TFirstName, TCity, TState, TPcode, TPhone, TEmail from Customers
where deleted is null
end;

GO

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-08 : 04:15:59

CREATE PROCEDURE SelectCustomers
@FilterSQL nvarchar(255) = ""
AS
if(@FilterSQL <> "")
begin
exec('select ICustomersId, TLastName, TFirstName, TCity, TState, TPcode, TPhone, TEmail from Customers where deleted is null and ' + @FilterSQL )
end
else
begin
select ICustomersId, TLastName, TFirstName, TCity, TState, TPcode, TPhone, TEmail from Customers where deleted is null
end

Hemanth Gorijala
BI Architect / DBA

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-08 : 07:28:51

It's bad practice to allow clients to submit arbitrary sql like that do the database.
In this example the column TLastName can be NULL in the database..


CREATE PROCEDURE SelectCustomers
	@ICustomersId int = null,
	@TLastName varchar() = null,
	.
	.
AS
	SELECT
		ICustomersId,
		TLastName,
		TFirstName,
		TCity,
		TState,
		TPcode,
		TPhone,
		TEmail
	FROM
		Customers
	WHERE
		deleted IS NULL AND
		( COALESCE( @ICustomersId, ICustomersId ) = ICustomersId ) AND
		( COALESCE( @TLastName, TLastName ) = TLastName OR COALESCE( @TLastName, TLastName ) IS NULL ) AND
		.
		.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

mr_mist
Grunnio

1870 Posts

Posted - 2004-09-08 : 07:36:06

quote:
Originally posted by rockmoose

It's bad practice to allow clients to submit arbitrary sql like that do the database.

Yep. Consider this being submitted

"1=0;select * from sysobjects"

Then look at the output grids, you get the sysobjects (or whatever) table returned as well as the (no) rows from the prior table. Depending on your security model it leaves your databases wide open.

-------
Moo. :)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-08 : 07:39:08

Look up SQL Injection, here or on google

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

gpiva
Starting Member

47 Posts

Posted - 2004-09-08 : 20:25:18

Thanks guys for the infos!

Carlo

quote:
Originally posted by rockmoose

Look up SQL Injection, here or on google

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources