6 Replies Latest reply: Nov 19, 2012 1:44 AM by stratmo RSS

    login procedure

    973713
      Hi,

      I am trying to create one procedure for login.

      Table name----Login
      ---------------------------
      Username varchar2(40) primary key,
      Password varchar2(40)


      i need to create a procedure for login so that every time when we are inserting any username and password it will check whether that username is present or not.
      if not present then insert into that table.
      else show one message that username already exist.
      How can i check whether that username is exist.


      Thanks,
      Prasad
        • 1. Re: login procedure
          Kim Berg Hansen
          You already have solved that by making the username column a primary key. A primary key is implicitly also a unique constraint.

          So when you insert an existing username, you will get an exception raised. Your client application just needs to catch that exception and show the message you desire.

          (It is not the job of the database to show a message generally, it is the client application. Showing a message is different in a C# application and a Java application and a Forms application.)
          • 2. Re: login procedure
            sb92075
            970710 wrote:
            Hi,

            I am trying to create one procedure for login.

            Table name----Login
            ---------------------------
            Username varchar2(40) primary key,
            Password varchar2(40)


            i need to create a procedure for login so that every time when we are inserting any username and password it will check whether that username is present or not.
            if not present then insert into that table.
            else show one message that username already exist.
            How can i check whether that username is exist.
            by issuing a SELECT USERNAME FROM LOGIN WHERE USERNAME = :NAME;
            • 3. Re: login procedure
              Billy~Verreynne
              970710 wrote:

              How can i check whether that username is exist.
              As Kim said - insert it. Let the database unique constraint deal with that check as its sole reason for existence is to enforce that check.

              If the insert raises an unique column exception, you know the username is duplicate and already exists.

              That simple.

              The wrong approach is to manually check if that user exists, by running a SQL SELECT on the table first, and if no data found, then to proceed with the insert.

              Two basic reasons why this is wrong.

              Performance. You are now using 2 SQL statements (a select followed by an <i>insert</i>) with 2 round trips to the database's SQL engine, to perform the row insert. A single trip (using the insert statement) is all that is needed.

              Concurrency. The millisec after your select statement to test for that username in the table, another session commits its insert transaction that creates that username.

              So while your select statement was true at the time it was executed and there was no row with that username, this is no longer true for the insert statement. As by the time it executes, the row with that username exists.
              • 4. Re: login procedure
                973713
                Hi,
                thanks for your reply.
                so can i do this using cursor.



                thanks,
                Prasad
                • 5. Re: login procedure
                  Billy~Verreynne
                  All SQLs are parsed and executed as cursors. Every single one from every single client and language.

                  So you need to explain what you mean by "could you use a cursor".
                  • 6. Re: login procedure
                    stratmo
                    Hi,

                    please take care of "SQL-Injection". Your scenario may implement a security leak here.

                    see

                    [url http://tkyte.blogspot.de/2012/02/all-about-security-sql-injection.html] Tom Kyte Blogspot: SQL-injection.

                    Regards

                    stratmo