security hole in stored procedure

[Cliff]

here's my stored procedure:

CREATE PROCEDURE proc
@id varchar(50),@pswd varchar(20),@no_go int OUTPUT
AS
SET NOCOUNT ON
SELECT user_id FROM profile
WHERE user_id=\@id AND pswd=\@pswd
IF \@\@ROWCOUNT = 0
BEGIN
SET \@no_go = 1
END
ELSE
BEGIN
SELECT date,date_mod FROM ans
WHERE user_id=\@id
SET \@no_go = 0
END

Using the PERL odbc_more_results function I can retrieve the data in
the second select statement whether the rowcount is 0 or not. Any
suggestions how to stop this

[Simon Hayes]

"Cliff" <cl***@walkacrossfire.com> wrote in message
news:54**************************@posting.google.c om...

here's my stored procedure:

CREATE PROCEDURE proc
@id varchar(50),@pswd varchar(20),@no_go int OUTPUT
AS
SET NOCOUNT ON
SELECT user_id FROM profile
WHERE user_id=\@id AND pswd=\@pswd
IF \@\@ROWCOUNT = 0
BEGIN
SET \@no_go = 1
END
ELSE
BEGIN
SELECT date,date_mod FROM ans
WHERE user_id=\@id
SET \@no_go = 0
END

Using the PERL odbc_more_results function I can retrieve the data in
the second select statement whether the rowcount is 0 or not. Any
suggestions how to stop this

Looking at one of your previous posts (the one Erland replied to), I notice
that you've simplified the above code from what you previously posted.
Specifically, do you still have these lines in your real code?

SELECT user_id FROM myTable
WHERE user_id=@id AND iyt=@iyt
SET @id_err = @@ERROR
IF @@ROWCOUNT <> 0
BEGIN

The SET @id_err = @@ERROR statement increments @@ROWCOUNT to 1, so the
following IF statement is always false, and execution always passes to the
second SELECT.

Since @@ROWCOUNT is volatile in the same way as @@ERROR, a common approach
is this:

SELECT user_id FROM myTable
WHERE user_id=@id AND iyt=@iyt
select @err = @@ERROR, @rows = @@ROWCOUNT

In this way, both values are captured reliably. If this is not the
explanation, then I can suggest two other things. First, use Profiler to
view the SQL being submitted to the server by your Perl script, to ensure
that it's really doing what you think it is. Second, use the stored proc
debugger to trace execution of the proc and watch the value of @@ROWCOUNT.
If you still have an issue after that, perhaps you could post the results of
those tests.

Simon

[Erland Sommarskog]

Cliff (cl***@walkacrossfire.com) writes:

here's my stored procedure:

CREATE PROCEDURE proc
@id varchar(50),@pswd varchar(20),@no_go int OUTPUT
AS
SET NOCOUNT ON
SELECT user_id FROM profile
WHERE user_id=\@id AND pswd=\@pswd
IF \@\@ROWCOUNT = 0
BEGIN
SET \@no_go = 1
END
ELSE
BEGIN
SELECT date,date_mod FROM ans
WHERE user_id=\@id
SET \@no_go = 0
END

Using the PERL odbc_more_results function I can retrieve the data in
the second select statement whether the rowcount is 0 or not. Any
suggestions how to stop this

The first SELECT seems meaningless. Why do you produce a result set?

Better would be to say:

IF EXISTS(SELECT *
FROM profile
WHERE user_id = @id
AND pswd = @pswd)
BEGIN
SELECT date, date_mot FROM ans WHERE user_id = @id
SET @no_go = 0
ELSE
SET @no_go = 1

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[cliff rubin]

tried that and still pulled the results from the second select. Adding
the word 'RETURN' after the first select in the originl way I wrote it
made all the difference and stopped the procedure from allowing the perl
to pull the second statement if rowcount was 0
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!