SQL Server 2000 - Store Procedure - Add new user - check before adding - return value if taken

Below is the basic stored procedure that I will use but I need to know how to return a value if the username/password is already taken.

Just use a return statement, as in
RETURN -1 -- Insert failed
RETURN 0 -- Item inserted

However note that your sproc may have a problem in that a username could be inserted into the table between the IF statement and the INSERT statement. While the IF statement is OK, I would also check the @@ERROR from the INSERT, assuming that you have a UNIQUE constraint on field1 (which isn't a really good column name BTW). You also are missing an @-sign on password in your INSERT statement.

I'm one of these people that rely heavily on Dreamweaver but am trying to get away from that by using Stored Procedures. I still interface using Dreamweaver but I now use the SP functionality.

With that in mind, where does the "RETURN" bit go and how would I pull it out? In other words, how would I have it return something that would trigger a message saying that "username" take. Is "RETURN" a variable that I can use? In other words, can I do an IF statement if do something

Also, I've read about the @@ERROR checking but have not done any error checking with Stored Procedures so it's a bit new to me. What sort of error would it be checking for? Someone else inserting in between the time that the current user is inserting?

As far as the naming of the tables (field1...) and such, I was using generic stuff that I will change when I actually put it into action.

What is your goal with this?

Is it to prevent people form signing up with the same username and password only?

If that's the case then you are allowing people to have the same username?

Ideally, you would want all of your users to have distinct username, the passwords they choose, whether they match someone else's password or not, should not matter.

If you are planning on returning a message to the user informing them that the username they wanted to register is already taken, that's a bad idea.

Why is this a bad idea? Because if I am a hacker and I come to your site and attempt to register an account and you tell me username "BSmith" is already taken. Then you have just provided half of the credentials I need to hijack "BSmith's" account. Since I have his/her username, now all I have to do it perform a brute-force/dictionary attack against your login process and then it's only a matter of time before I have successfully hijacked the account.

So let's assume you have a lockout on your login process. Let's assume if a user has 5 failed login attempts you lock the account. Then in his case, I, as the hacker, can continue to attack your "registration" process to gather a list of valid usernames...then I can perform a scripting attack against your login process and lock out all the users for which I have valid usernames....

See where I'm going here?? Bad idea to provide an specific type of feedback to the user, as you can't be 100% sure that the user is friendly...

Again, the problem with your process is that the 3 SQL statements in your procedure are not atomic -- meaning that that process could be interrupted and a the process run on another connection such that your INSERT will fail. Assuming that EMail is the primary key on your atblCust table (as it should be), the sproc can become:



The only reason for the Select statement is to preserve the error code if you need to do additional processing. In general, a sproc is considered to work if the return code is 0, otherwise a non-zero indicates an error -- in this case, a primary key violation.

Thanks for that, now I haven't even figured out how to do it wrong and I'm doing it wrong. Typical...

My goal is to check to see if a username is taken before adding it. If it is taken, they get bumped back if not, it adds the new user.

Your description of the problem makes sense but how do I inform the person, that the username they chose can't be used without, essentially telling them that that user is already taken?

I'm getting an error on the sp below: "ERror 128: the name ''var..." is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted"

You can do it "safely" in two different ways...

1) Just display a generic "Registration Failed" error. It's not very descriptive and might confuse users.

or

2) Provide a "laundry list" of reasons why the process might have failed, such as...

Instead of giving them the exact reason why it failed, you have provided enough feedback to allow the user to deduce the reason it may have failed without disclosing too much information.

Sorry, my Oracle is showing.
Values (@varUser, @varPass)
if I remember my T-SQL correctly.

Thanks, that makes sense but won't most most hackers know that this is what I am doing?

OK, I've been on the Dreamweaver app forum and it was recommended that I get some error checking into it using Transact SQL. I've tried different examples off the web and applied them to my own example but I can't seem to get rid of the bugs.

Below is something that doesn't error out but what bothers me is that the Begin Tran isn't ended. Is this right? I've tried to "Commit Tran" but it errors out when I do this. I basically don't want it to insert anything if any of it had an error.

In this case, you don't need a transaction since you are only making a single modification to the database. You would use a Transaction if you need to do multiple updates/inserts to the database, and want to rollback all changes should any of the updates/inserts fail.

Also, you @ins_error makes no sense here since it should be the error code from the INSERT table. By having it in the ELSE clause, you're getting the error code from the SELECT statement.

And yes, it BEGIN TRANSACTION, ROLLBACK TRANSACTION and COMMIT TRANSACTION. You didn't specify where the COMMIT was coded, nor what the error was.