SitePoint Sponsor

User Tag List

Results 1 to 16 of 16

Thread: T SQL Question

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    T SQL Question

    I have a legacy user table that I want to transfer users over to a new app.

    However, I would like to auto generate new random passwords when I do this.


    I found this little piece of code to generate one random password (below) ... how do write a query that will select current values from the user table and the value reutrned below as the password... ie:

    select name, address from users

    Lets pretend that is all I want form the users table, but I also want a random password

    select name, address, [Password returned from the code below] from users



    I have run into similar hurdles before and never really understood how to get around it - any insight / help would be appreciated.



    PHP Code:
    DECLARE @RandomID varchar(32)
    DECLARE @
    counter smallint
    DECLARE @RandomNumber float
    DECLARE @RandomNumberInt tinyint
    DECLARE @CurrentCharacter varchar(1)
    DECLARE @
    ValidCharacters varchar(255)
    DECLARE @
    ValidCharactersLength int
    DECLARE @Length int

    SET 
    @ValidCharacters 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-=+&$'
    SET @ValidCharactersLength len(@ValidCharacters)
    SET @CurrentCharacter ''
    SET @RandomNumber 0
    SET 
    @RandomNumberInt 0
    SET 
    @RandomID ''
    SET @Length 8

    SET NOCOUNT ON

    SET 
    @counter 1

    WHILE @counter < (@Length 1)

    BEGIN

            SET 
    @RandomNumber Rand()
            
    SET @RandomNumberInt Convert(tinyint, ((@ValidCharactersLength 1) * @RandomNumber 1))

            
    SELECT @CurrentCharacter SUBSTRING(@ValidCharacters, @RandomNumberInt1)

            
    SET @counter = @counter 1

            SET 
    @RandomID = @RandomID + @CurrentCharacter

    END

    SELECT 
    @RandomID AS 'Password' 

  2. #2
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    that's easy, dude.
    Put the code in an after insert trigger, which updates the password field when the record gets created

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Pretty good idea. It does seem like a bit of hack for the problem though and I imagine there is a 'best practices' way to do it - if so I'd like to learn... otherwise - for now I'll go down this route - thanks for the good idea.

  4. #4
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    'best practices' way to do it
    Nope, everything is a hack
    You could of course put it in a user defined niladic function

    Hah, I got to use the word niladic, that made my day

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is that a riddle?

    No - thank you much for your help

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does this seem like what you had in mind?



    CREATE TRIGGER [RandomPassword] ON [dbo].[Users]
    FOR INSERT
    AS

    ///// CODE THAT CREATES RAND PWD (@RandomId) HERE

    UPDATE Users
    SET password = @RandomID
    Where Users.Userid = (Select UserId From Inserted)

  7. #7
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I meant something like this...

    Code:
      USE MYDB
      IF EXISTS (SELECT name FROM sysobjects
      	  WHERE name = 'mytrigger' AND type = 'TR')
         DROP TRIGGER mytrigger
      GO
      
      CREATE TRIGGER mytrigger
      ON mytable
      FOR INSERT
      AS
      
      --generate password
      DECLARE @someval varchar(100)
      select * FROM inserted
      
      IF (@someval <> '') 
      BEGIN
         RAISERROR ('My Error', 16, 1)
         ROLLBACK TRANSACTION
      END
      ELSE
      
      update mytable SET password=@someval
      -- do something
      END
      
      GO

  8. #8
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AFTER is the default kind of trigger

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh damn... it didn't work

    I suppose I should have mentioned that I was using DTS to transfer the files... apparantly DTS doesn't trigger INSERT!?

  10. #10
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So now... all the user records have transfered over - but the password is set to a temporary value... how can I update/change the password using the code from my first post?

  11. #11
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well - I ended up reverting back to what I know best. I couldn't figure it out the sql way so I did it in code/script. I'll learn to be better at sql another day eheh

  12. #12
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    apparantly DTS doesn't trigger INSERT!?
    As far as I was aware, yes it does... But I may be wrong.

  13. #13
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how can I update/change the password using the code from my first post?
    You can use a stored procedure, you can use something like:

    update users set password = functionTempPassword()

    and you store your SQL in a new function you named functionTempPassword()

  14. #14
    SitePoint Guru asterix's Avatar
    Join Date
    Jun 2003
    Posts
    847
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll learn to be better at sql another day eheh
    Thats a shame!
    better luck next time

  15. #15
    SitePoint Guru puco's Avatar
    Join Date
    Feb 2005
    Location
    Slovakia
    Posts
    785
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why don't you create a scalar UDF to generate a password of specified length, call it GeneratePassword (int length).

    Then you can easily select them: select dbo.GeneratePassword (7), or use them in inserts/updates.
    Martin Pernecky

  16. #16
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    'Ville
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I actually started going down the route of making a UDF but got stuck on syntax and gave up and just coded a quick script. I spent the better part of a day trying it in sql and got it all done in c# in 15 minutes. *shrug* Thank goodness for DBAs b/c it ain't my bad of tea.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •