Dismiss Notice
You are browsing this site as a guest. It takes 2 minutes to CREATE AN ACCOUNT and less than 1 minute to LOGIN

Help needed: MySQL - Perl database authentication

Discussion in 'Tech, Gadgets & Science Forum' started by Bluray, Apr 9, 2009.

  1. Bluray

    Bluray JF-Expert Member

    #1
    Apr 9, 2009
    Joined: Mar 25, 2008
    Messages: 3,445
    Likes Received: 25
    Trophy Points: 135
    Wandugu,

    Natengeneza an auction application kwa kutumia mySQL na perl, hizi ni project requirements.

    Nahitaji algorithm/code/commands za ku integrate a perl login kutoka mySQL database.

    Nataka kuwa na login moja tu ya mySQL ambayo watu wote kwenye auction application wataitumia ku connect na ku authenticate kwenye mySQL database, halafu kutoka hapo nataka kuwa authenticate users kwa kutumia table moja ya mySQL yenye fields za username na password.

    Process ya kwanza (ku connect mySQL) ni cha mtoto, kasheshe linakuja katika kutaka ku authenticate users kwa kutumia a mySQL table.

    Nitashukuru kama kuna mjanja atanionyesha njia.
     
  2. SnEafer

    SnEafer Senior Member

    #2
    Apr 11, 2009
    Joined: Apr 1, 2009
    Messages: 154
    Likes Received: 0
    Trophy Points: 0
    Hi Bluray,
    I got like 50% of the meaning of your post so i'll reply due to my understanding.

    Let's create a login system using PHP and mySQL. First we need to create the table that holds our user data. We're going to have 3 fields, logged, which will keep track of the last time a person logged in to the page, user and pass which will be the username and password respectively. Using Telnet you can simply copy and paste this into your mySQL monitor.

    CREATE TABLE login
    (
    L_ID INT NOT NULL AUTO_INCREMENT,
    logged DATETIME,
    user VARCHAR(10),
    pass VARCHAR(10),
    PRIMARY KEY (L_ID)
    );

    Next we need a way to insert the new users into this table. We can do that in telnet by utilyzing the following SQL command:

    INSERT into login VALUES( 0, SYSDATE(), 'username', 'password' );

    The zero increments the L_ID which is a way to refer to each row in the data without having to view to compare the other fields for a match. SYSDATE() is a built-in SQL function to insert the current server date and time in the format 2000-07-12 11:24:00. We can turn this SQL query into a PHP script by making the above code into a query and using the php function mysql_query. Here would be the code to do the above in our PHP script.

    <script language="php">
    $query = "INSERT into login VALUES ( ";
    $query .= "0, SYSDATE(), '$username', '$password' )";
    mysql_query($query, $mysql_link);
    </script>

    Now we need a way to actually check and see if a username/password matches a record in the database. The SQL query you can type directly into telnet is as follows:

    SELECT user, pass FROM login WHERE user='$username' AND pass='$password';

    Again, we can easily convert this into a query in PHP as follows:

    <script language="php">
    $query = "SELECT user, pass FROM login ";
    $query .= "WHERE user='$username' AND pass='$password'";
    $result = mysql_query($query, $mysql_link);
    if(mysql_num_rows($result)) {
    // we have at least one result, so let them in
    } else {
    print("Sorry, this login is invalid.");
    exit;
    }
    </script>

    Now let's create the code to put in the top of php enabled page before the <HTML> tag to actually authenticate the user. If the person comes to the page and there is no $username and $password, we'll show them a login box. If the $username and $password variables are assigned then we'll try to validate the user and if there isn't a match we'll pop the invalid login message and exit the script before showing the content. This may seem like a lot of code, but actually it is only a few more lines to the one above. Check it out:

    <script language="php">
    $mysql_link = mysql_connect("localhost", "mysql_username", "mysql_password");
    mysql_select_db($db, $mysql_link);
    if (($username) AND ($password)) {
    // you should inspect these variables before passing off to mySQL
    $query = "SELECT user, pass FROM login ";
    $query .= "WHERE user='$username' AND pass='$password'";
    $result = mysql_query($query, $mysql_link);
    if(mysql_num_rows($result)) {
    // we have at least one result, so update the logged in datetime
    $query = "UPDATE from login SET logged=SYSDATE()";
    $query .= "WHERE user='$username' AND pass='$password' ";
    mysql_query($query,$mysql_link);
    } else {
    print("Sorry, this login is invalid.");
    exit;
    }
    } else {
    print ("<form action=\"$PHP_SELF\" method=\"POST\">");
    print ("User: <input type="text" name="username">")
    print ("Pass: <input type="password" name="password">");
    print (" <input type="submit" value="Submit"></form>");
    }
    </script>
    <HTML>
    <BODY>


    You may notice the use of the $PHP_SELF variable in the generated login which will call the same page. This is useful so you wouldn't have to alter every page you put the code above on. Also if we do have a successful login then we update the logged time in the mySQL table. Now we'll take this a step further and we'll build an admin interface to add and delete users to this page by page password system for validating users.


    For those who don't want to telnet in to create new users ... create a browser-based admin area

    For the sake of simplicity here, I'm going to assume you would put the script we are building in an admin protected directory. I'll leave that part to you to add, but don't put this type of script in an unprotected area or anybody that knows the url can add themselves to your username/password database :)

    The HTML Form that drives the admin script

    The admin script will call itself similarly to the script in above entry so that we don't need to have 2 or 3 separate scripts. Oddly enough the last section of code is really the first step. We first design of the form we are going to use in HTML. This is where I like to use editors because I can open up Front Page or another fancy editor and then quickly create a new page and insert the forms where I want. Then view the source and take out the garbage the Front Page adds (like the web bot calls in the form action area for example). Then I can cut and paste the code to my working admin.phtml script.

    <html>
    <head>
    <title>Login Admin Area</title>
    </head>
    <body>
    <p align="center"><big><big>Admin Area</big></big></p>
    <form method="POST" action="admin.phtml">
    <input type="hidden" name="react" value="add_user">
    <div align="center"><center><p>Username: <input type="text" name="user" size="12"><br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
    Password: <input type="text" name="pass" size="12"><input type="submit" value="Add User"></p>
    </center></div>
    </form>
    <form method="POST" action="admin.phtml">
    <input type="hidden" name="react" value="delete_user">
    <div align="center"><center><p>Username: <select name="user" size="1">

    <script language="php">
    // INSERT a dropdown list with all valid usernames in the database
    </script>


    </select>&nbsp; <input type="submit" value="Delete User"> </p>
    </center></div>
    </form>
    </body>
    </html>

    Creating branches of code to accomplish multiple activites within the same script

    Note the hidden form tags I've added to flag the script of what we are doing. Using hidden form tags you can create branches in your code to do many different things based on the value of the hidden form. This makes it so you can have one self-contained admin area instead of needing multiple scripts. This makes it easier to modify one script instead of multiple ones.

    Creating a dynamic drop down list

    You'll see the green area of code is where we will be adding the dynamic drop down list of usernames in the database. The form itself is pretty standard with a text box to add users and passwords at the top and the box to delete members at the bottom. Let's make the query to the database to get all the users out and then present them as options in the dropdown (select) list.

    <script language="php">
    $query = "SELECT user FROM login ";
    $result = mysql_query($query, $mysql_link);
    if(mysql_num_rows($result)) {
    // we have at least one user, so show all users as options in select form
    while($row = mysql_fetch_row($result))
    {
    print("<option value=\"$row[0]\">$row[0]</option>");
    }
    } else {
    print("<option value=\"\">No users created yet</option>");
    }
    </script>

    Again you see how we used mysql_num_rows to determine if there were any results and then the PHP myql_fetch_row function is used to pull each consecutive row of data, if users exist in the table. We use a while to loop through the reseults until we reach the last row and the function returns false.

    I hope i answered your question.
    Cheers
     
  3. Freelancer

    Freelancer JF-Expert Member

    #3
    Apr 11, 2009
    Joined: Sep 22, 2008
    Messages: 2,068
    Likes Received: 641
    Trophy Points: 280
    vipi password zipo encrypted?
     
  4. Kiranga

    Kiranga JF-Expert Member

    #4
    Apr 11, 2009
    Joined: Jan 29, 2009
    Messages: 34,583
    Likes Received: 5,622
    Trophy Points: 280
    Unaweza ku encrypt password kwa kutumia MD5 option ya mySQL.
     
Loading...